Math Bug in Microsoft Excel

[M.N.S]

I paid a consultant for a bunch of formulas and built a spreadsheet with them. The results didn't match those of the consultant. I thought some formulas must have been keyed in wrong, so we went over everything 15 times. Days later, that was ruled out. Next, we suspected the consultants math. After several consultant-hours we nailed it down to a math bug in Excel. After nearly a decade I can't believe it still exists. I tried it in Excel 2002 and 2003 -- both have the bug. It's not an everyday thing, but it's not rare or esoteric either. When consultant found it, he was shocked and said they "should be sued". It's not arguable -- it's wrong, it's a bug. Since it cost me $2000 to find it, I'd like to know if Microsoft has any kind of pay-for-bugs programs. Or maybe magazines that would buy this info. Any ideas for how I can get reimbursed.

[JyotiT]

Perhaps you could provide an example of the bug. In addition to Chip. Are your sure this isn't just a rounding issue. As I said please provide an example.

[-Deven-]

Virtually every time a message with "math bug" in the subject has been posted here, the problem has turned on a lack of understanding of Excels published specifications and limitations, rather than a true bug. Did your consultant know that you were going to be using his formulas in an Excel workbook? And did your contract reflect that.

[BillieJ]

To expand on above comment, basic math, exponential, log, and trig functions are almost surely done by the CPU, rather than Excel reinventing the wheel. It is extremely unlikely that there is a math bug here, though on average there are 1-2 such mistaken claims per week from people who don't understand the implications of finite precision binary math (done by almost all software and hardware, not just Excel). Another common problem is that text digits that look like a number in input cells will either be ignored or cause an error, except for the few cases where Excel's evaluation rules would coerce the text to a number. Probability distribution functions (including ERF() and ERFC()), Bessel functions and GAMMALN() were implemented in Excel using inferior algorithms, but I don't immediately recall any other algorithm issues
that have not been improved in 2003. If the previous paragraph does not cover your formula issues, then rather than a "math bug", it is much more likely that either your formula is numerically unstable or you have made some kind of mistake in using it. We cannot help you diagnose this until you provide details.

[Gania]

Well, I would really like to get reimbursed for this. If I post the bug here the chances of that drop to near zero. I will say this about it though: If you consider calculating the wrong sign a math bug, then this is a math bug. In certain situations, that is the problem. 'Weekly claims of Excel Math bugs' that don't pan out, wow, that's kind of amazing. I think you'll all agree with me on this though, I've had 4 years of college calculus etc, and the consultant has a degree in physics. And Mathematical calculates the formula differently (the right way).

[Shurod]

I don't think MS will re-reimburse you (I would assume the EULA agreement you signed will exclude this kind of payment/reimbursement). To give you an example for this: Excel 2003 introduced a RAND() error (sometimes returning a negative value). This error was admitted by MS but I doubt anybody got money for this (even if their calculation models were affected). So the only 'benefit' you probably could get is post this bug to the public / this NG, and at least got a confirmation that it is REALLY a bug (and not an already know issue or just a simple Excel restriction)

[Erakna]

You've had all this in various posts/links, but it was a nice summary posted by others previously that I have kept. The difference between Excel and Mathematical is also referred to in the text:-

• Excel will round all numbers to 15 significant figures. Anything over and above this will be rounded to 0. If the data needs to be entered as for example a credit card number, you need to precede the entry with an apostrophe or format the cell as text before you enter the data. You can still do calculations against a number entered as text BUT it will only use 15 significant figures in the calculation, so that doesn't buy you anything extra doing it that way. A slightly edited (To generalize the response only), but very comprehensive answer to a similar question was posted by Chip Pearson - Reproduced below in it's entirety. As you have noticed Excel handles only 15 digits of precision.
• The reason is that Excel, like many other computer programs, uses the IEEE (Institute of Electrical and Electronic Engineers) Double Precision Floating Point number format as the most accurate representation of a number.
• N = Integer + X*(1/2) + X*(1/4) + X*(1/8) + X*(1/16) +.....+ X*(1/2^51)
• Where each X is either 1 or 0. In binary format, there are 51 digits to the right of the decimal point. In decimal form, 2^51 is about equal to 10^15, which is why you get approximately 15 digits of precision. Unless a fractional number can be expressed *exactly* as the sum of 1/2 + 1/4 + 1/8 + ... + 1/(2^51) it will be stored as an approximation. This is not unique to computers. Using a finite number of decimal places, you cannot accurately store the number 1/3. You can store it as an approximation, like 0.3 or 0.33 or 0.33333333333333 but at some point you're rounding the true value 1/3, and 0.33333333333...+0.33333333333...+0.33333333333... does NOT equal 1. It equal 0.999999999999...... which is decidedly not 1.