Microsoft Excel

Good to see improvements in stats functions, IRR and MOD finally addressed! LINEST also had some problems, I don't know if these were already fixed. It would also be nice to have more functionality for linear and non-linear estimation/interpolation.

Another function that needs attention is PROB. The function can error on a uniform distibution of just 27 values for example. The checksum fails due to the 15-digit precision tolerance as can be checked since =SUM(ROW(1:27)*0+1/27)=1 returns false (array-entered). This function can be very useful for stats on frequency data, something that's not really feasible without UDFs otherwise. eg for ascending data (x) and frequencies (f), LOOKUP(0.5,PROB(x,f/SUM(f),,x),x) returns the (lower) median.

Since you have added over 50 functions whose name is very close to the existing ones (GAMMA.DIST() instead of GAMMADIST()), that will result in a mess with the user unable to choose wisely.

What I would have seen instead is a flag in Excel options (per workbook, or for the application), off by default, where the user can require better accuracy.

That would avoid both breaking changes and a useless row of new functions.

I'd have rather see the Excel functions team work on adding guts to some of the most powerful functions.

I'm curious what is your say on this.

I'm interested to know how the RAND function has been improved (repetition period, cryptographically secure or not). In Excel 2003, I found it baffling that the old Wichman-Hill algorithm was used - an algorithm so old that it had to be converted from Fortran. The Mersenne twister algorithm, for example, has been freely available in multiple languages for quite some time, and is vastly superior to Wichman-Hill.  

"The Mersenne twister algorithm, for example, has been freely available in multiple languages for quite some time, and is vastly superior to Wichman-Hill."

Not to mention Microsoft's own CryptGenRandom function.

I would love to see the rand command within VBA given a facelift. It's the same old numbers, time after time.

With respect to the inverse probability distribution functions, the main use would be simulation, no? If so, the formulas calling these functions would change upon every recalc. I'm not sure I see a compelling need for backward compatibility in terms of function return values/[in]accuracy.

Nice to see MOD and GAMMALN on the list. Presumably this means MOD is now calling the hardware FPU remainder operation, so can now handle full IEEE 754 double precision. Also, presumably GAMMALN(1) and GAMMALN(2) both now correctly return 0.

The fix to GEOMEAN presumably means it's now implemented as =EXP(AVERAGE(LN(data))) rather than as =PRODUCT(data)^(1/COUNT(data)).

You should take a look at all the stats, matrix, and optimization functions I created for Excel & Access.

Nico

Anon

I prefer the additional function route rather the workbook flag approach you prefer.

I think few people check that sort of setting, so its better to be explicit in the face of the worksheet.

I agree it adds complexity, but that is the trade off for power and flexibility.

Having a worksheet/book flag would lead to lots of head scratching when two apparently identical functions in 2 worksheets/books give inconsistent results.

Good news, but long overdue and, without knowing which improved algorithms have been used, it's impossible to be really happy - for instance, the best application for NormSInv that I know is from Feb 2009

And will the last of the former ATP functions, that for Fourier transforms, finally get a proper built-in function?

anon & Simon: I will be describing the UI changes we have made to help users distinguish and choose between functions that are similarly named in a future blog post. So stay tuned for that!

Colin: We are using the Mersenne Twister for RAND.

Jessica, that's good news. Would there be a similar improvement in the VBA Rnd function?

Colin: We did not change VBA's RND function since that is part of VBA's math library. All of the other worksheet functions we've added are available through VBA.

Well, at least we can use the updated RAND function in VBA through [RAND()]

I think it would be great if you could add a function to do a simple linear interpolation, such as in the above link.  

"We did not change VBA's RND function since that is part of VBA's math library. All of the other worksheet functions we've added are available through VBA."

Having the worksheet functions available to VBA is no substitute for implementing native VBA functions to the same standard.

Well not unless you have also provided a dramatic reduction in the time penalty of using a worksheetfunction call from VBA.

Really please to see these improvements, and the whitepaper does add some great detail.

http://wp.me/p2I5L-44

[Forgive me if this is another duplicate.  It will be my last try for now.]

Has the following Excel 2003 problem been fixed in 2010 (or 2007)?

INT(123456789 - 0.0000004) returns 123456789 instead of 123456788.

This causes a problem in formulas like the following:  if A1 is =123456789-0.0000004, =A1-INT(A1) is negative unexpectedly, about -4.917E-07 when formatted as General.

In contrast, INT(123456789 - 0.0000005) returns 123456788 as expected, and myInt(123456789 - 0.0000004) returns 123456788, where myInt() is the following UDF:

Function myInt(x as Double) as Double

myInt = Int(x)

End Function

Note that 123456789 - 0.0000004 is represented internally as about 123456788.999999,598, whereas 123456789 - 0.0000005 is about 123456788.999999,493 internally.  (The comma demarcates the first 15 significant digits.)

So I suspect that the Excel INT algorithm is effectively, albeit perhaps unintentionally, rounding its argument to 15 significant digits before truncating to an integer.  It shouldn't.

Indeed, the largest expression involving 123456789 that returns an incorrect INT value is 123456789 - 33*2^-26, which is represented internally as about 123456788.999999,508, whereas 123456789 - 34*2^-26 is about 123456789.999999,493 internally.

As you might imagine, the problem is not limited to 123456789 and 0.0000004.  And the problem will not appear with some combinations that you might think are similar, e.g. 100000000 and 0.0000004.

You need to look at the exact conversion of the internal binary representation -- that is, beyond the first 15 significant digits -- to determine whether or not to expect a problem.  I have VBA code that will help with that, if you need it.