You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Thanks to Jessica Liu for putting together the next few posts on function improvements.
In Excel 2010, we made many improvements to Excel's function library. Excel 2010 will feature an accurate and consistent function library while remaining compatible with previous versions of Excel. In this first blog post, I will be giving an overview of the work we did in this area as well as talk about the function accuracy improvements. Subsequent posts will go into the details of the consistency improvements as well as the backward compatibility story.
The first area we invested in was to improve the accuracy of functions. Over the years there have been various academic papers detailing issues in Excel's worksheet functions. In Excel 2003, we started the work to address the most serious of the issues reported in these papers and in Excel 2010 we have addressed even more of these issues. Our goal for Excel 2010 was to address the most significant function accuracy issues reported. For any function we modified, we corrected all known bugs relating to that function.
We implemented new algorithms in order to improve the accuracy of our statistical, financial and math functions. We worked very closely with industry experts to determine which algorithms to use as well as to validate these new algorithms. Our hope is that Excel 2010 users will be able to utilize functions in our library with confidence knowing that they have comparable accuracy to those of other statistical packages.
The other area we invested in was making our function library more consistent. This was in response to the other set of concerns voiced in these academic papers as well as by our users. Users have noted that there were consistency issues with Excel's function names and definitions. In Excel 2010, we will offer users a set of consistently and accurately named functions as well as function definitions that are consistent with user expectations. We have introduced over 50 new functions in order to do this.
Finally, the last piece of work we did in this area was to update the functions user interface. We have improved the function auto complete feature, and we have also made changes to support the new function set.
For Excel 2010, we overhauled the function library and implemented completely new algorithms for many of our statistical, financial and math functions. The Excel team partnered with Frontline Systems, the Numerical Algorithms Group, and ScienceOps to select, implement and validate these algorithms.
The algorithms for calculating the follow statistical distribution functions have been modified or redesigned completely for better accuracy:
Chi squared distribution
Negative Binomial distribution
Standard Normal distribution
Student's t distribution
The following financial functions have improved accuracy:
Cumulative interest paid on a loan
Cumulative principal paid on a loan
Interest payment for an investment
Internal rate of return for a series of cash flows
Payment for a loan
Payment on principal for an investment
The accuracy of these additional functions has been improved:
Complementary error function
Natural logarithm of the gamma function
Random number function
Sample standard deviation
As part of the accuracy improvements, we will also accept a larger range of input values and as a result will be returning a wider range of results for certain functions. For example, the ERF and ERFC functions will now take in negative input values, and the MOD function will be able to take larger input values.
In the next post, I will talk about the changes we have made in Excel 2010 to improve the consistency of the function library.
Really please to see these improvements, and the whitepaper does add some great detail.
[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)
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.