Function Improvements in Excel 2010

re: Function Improvements in Excel 2010

joeu2004 — Sat, 10 Oct 2009 05:21:25 GMT

[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.

re: Function Improvements in Excel 2010

AdamV — Fri, 09 Oct 2009 10:16:01 GMT

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

http://wp.me/p2I5L-44

re: Function Improvements in Excel 2010

Doug Jenkins — Wed, 30 Sep 2009 07:32:00 GMT

"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.

re: Function Improvements in Excel 2010

Jason — Thu, 17 Sep 2009 20:46:47 GMT

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

re: Function Improvements in Excel 2010

Colin Banfield — Wed, 16 Sep 2009 18:28:55 GMT

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

re: Function Improvements in Excel 2010

Jessica Liu — Wed, 16 Sep 2009 17:42:38 GMT

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.

re: Function Improvements in Excel 2010

Colin Banfield — Mon, 14 Sep 2009 19:28:44 GMT

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

re: Function Improvements in Excel 2010

Jessica Liu — Mon, 14 Sep 2009 18:49:43 GMT

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.

re: Function Improvements in Excel 2010

Mike Staunton — Mon, 14 Sep 2009 11:10:37 GMT

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?

re: Function Improvements in Excel 2010

Simon Murphy — Mon, 14 Sep 2009 10:49:04 GMT

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.