Excel Math Bug

[fred]

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?

Fred

 

[Chip Pearson]

Fred,

Perhaps you could provide an example of the bug?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Frank Kabel]

Hi
in addition to Chip. Are your sure this isn't just a rounding issue?
Bus as Chip said please provide an example

 

[Nigel]

Chip, Sounds like Fred want to keep it to himself and get paid first! No
doubt he believes it exists but I guess someones got to prove it for real?

Cheers
Nigel

 

[Ron Rosenfeld]

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.

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 Excel's 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?


--ron

 

[Tom Ogilvy]

http://support.microsoft.com/default.aspx?scid=kb;en-us;78113&Product=xlw
XL: Floating-Point Arithmetic May Give Inaccurate Results

http://support.microsoft.com/default.aspx?scid=kb;en-us;214118&Product=xlw
How to correct rounding errors in floating-point arithmetic

if these are the type of problems you are having, then your consultant needs
to provide formulas that do not accumulate rounding error.

 

[Jerry W. Lewis]

To expand on Ron's 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.

Jerry

 

[fred]

Well, I'd 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 Mathematica
calculates the formula differently (the right way).

 

[Myrna Larson]

I highly doubt you'll get any reimbursement. Excel is probably operating
according to it's specs.

 

[Frank Kabel]

Hi
I don't think MS will re-imburse you (I would assume the EULA aggrement
you signed will exclude this kind of payment/reimburesement).

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 anybode 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)

 

[Tom Ogilvy]

Precedence of operations is stated in the help. As far as I know, there is
no universally accepted order of precedence that covers all combinations and
permutations.

Again, sounds like your beef is with your consultant if you were paying for
his/her expertise in Excel.

 

[Myrna Larson]

Again, sounds like your beef is with your consultant if you were paying for

his/her expertise in Excel.

I agree!

 

[Peter T]

Well, I'd really like to get reimbursed for this.

If I post the bug here the chances of that drop to near

zero.

Can't drop below what they already are.

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 Mathematica
calculates the formula differently (the right way).

Google "calculus excel limitations" gives me 6880 hits.

Peter

 

[Ken Wright]

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
Mathematica 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 generalise 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. You can read more about this at
www.cpearson.com/excel/rounding.htm , but in an oversimplified
form, it stores numbers as

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.

This is a fact of life in computers and in the real world, and in
the realm in which the two coincide.

But what about the rest of the decimal places, and how, if at all,
can I achieve more precision?

You can *display* a number to as many decimal places as you want,
but anything past 15 is no man's land. Within Excel there is no
way to achieve additional precision. Errors in rounding can
compound, so that rounding error in one formula is compounded when
the rounded error is used by other formulas, which themselves
round.

Some computer programs use other representations of numbers, but
these programs trade performance and compatibility for precision.
Additional precision comes at the cost of performance and
compatibility with other programs. For example, a program that
stored numbers to 100 digits of precision would use a different
encoding scheme, and its data would not be compatible with the
majority of computer programs. The IEEE Double Precision standard
provides a universal format that is "good enough" for the vast
majority of uses. Not all, but most. For good reason, MS chose
years ago to use IEEE Doubles for Excel.

Can you recommend a non-Excel app that offers higher precision?

Dedicated mathematical programs like Matlab and Mathamatica can
provide much greater precision, but those results aren't compatible
with most other computer programs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)


--------------------------------------------------------------------------------
------------------------

For a calculator that will support more than 15 digits, Jerry W. Lewis has
given the following info and link:-


The decimal data type gives 28 figure if you don't need exponents and
don't mind VBA programing. I think the Windows calculator uses the same
data type.

A free quad precision (64 digit) calculator can be downloaded from
http://www.crbond.com/applications.htm
unless it has been updated, it does not support cut/copy/paste.

I think some extended precision routines using VBA and strings have been
published for Excel - search the Google archives.

In Maple, Mathematica, Matlab, Rexx, etc., you can specify the number of
output figures you want.


Jerry W. Lewis

--------------------------------------------------------------------------------
--------------------------

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Well, I'd 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.

<snip>

 

[Dana DeLouis]

...the wrong sign a math bug,

...And Mathematica calculates the formula differently (the right way).

Just guessing here. The only thing that comes to mind of hand might be the
following:

In Excel:
=-5^2
returns +25.

and in mm:
-5^2
-25

A beginning "-" is a "known" little issue with Excel. I think Excel handles
negation a little differently.

Subtracting from zero changes the answer in Excel.
Excel:
=0-5^2
-25

But stays the same in mm:
0-5^2
-25

Just a wild guess of course. I think we all want to see and learn from the
issue you encountered.
HTH
Dana DeLouis

 

[fred]

Yes Dana, that's it. Good deduction.

It cost me $2000, and a week, to find that out.

it happens directly after the leading equal sign or inside prens.

We found another strange thing with double negatives.
It was something like "=--2" yielded a result of "5" or something
along those lines. But we were in the middle of something
and I didn't write it down. Are you familiar with that one?

 

[Amedee Van Gasse]

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?

Fred

Fred,

From the EULA:

18. EXCLUSION OF INCIDENTAL,CONSEQUENTIAL AND CERTAIN OTHER DAMAGES. TO
THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, IN NO EVENT SHALL
MICROSOFT OR ITS SUPPLIERS BE LIABLE FOR ANY SPECIAL, INCIDENTAL,
PUNITIVE, INDIRECT, OR CONSEQUENTIAL BUT NOT LIMITED TO, DAMAGES FOR
LOSS OF PROFITS OR CONFIDENTIAL OR OTHER INFORMATION, FOR BUSINESS
INTERRUPTION, FOR PERSONAL INJURY, FOR LOSS OF PRIVACY, FOR FAILURE TO
MEET ANY DUTY INCLUDING OF GOOD FAITH OR OF REASONABLE CARE, FOR
NEGLIGENCE, AND FOR ANY OTHER PECUNIARY OR OTHER LOSS
WHATSOEVER)ARISING OUT OF OR IN ANY WAY RELATED TO THE USE OF OR
INABILITY TO USE THE SOFTWARE, THE PROVISION OF OR FAILURE TO PROVIDE
SUPPORT OR OTHER SERVICES, INFORMATON, SOFTWARE, AND RELATED CONTENT
THROUGH THE SOFTWARE OR OTHERWISE ARISING OUT OF THE USE OF THE
SOFTWARE, OR OTHERWISE UNDER OR IN CONNECTION WITH ANY PROVISION OF
THIS EULA, EVEN IN THE EVENT OF THE FAULT, TORT (INCLUDING NEGLIGENCE),
MISREPRESENTATION, STRICT LIABILITY, BREACH OF CONTRACT OR BREACH OF
WARRANTY OF MICROSOFT OR ANY SUPPLIER, AND EVEN IF MICROSOFT OR ANY
SUPPLIER HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Don't you think Microsoft has legal consultants? A whole army of them!
Just to make sure they don't have to pay any money to people like you.

Actually you are very, *very* selfish. This way, nobody wins, and
surely not you and your client! Instead of keeping the information to
yourself, you should share it. Make your information free (as in
speech). Perhaps other people might benefit from your information. Make
this a nonzero equation. You should know what I mean, you and your
client have both done the relevant studies.

Anyway, a claimed "bug" isn't a bug at all until it has been confirmed
as a bug. So give some more information please so that we can help you.

What we here in the ng are willing to do, even for free (as in beer),
is to help you find a workaround. And something else. If you had come
here sooner, you would have saved a lot of time and money!!!

 

[Amedee Van Gasse]

(reading order fixed)

Dana DeLouis wrote:

Yes Dana, that's it. Good deduction.

It cost me $2000, and a week, to find that out.

it happens directly after the leading equal sign or inside prens.

We found another strange thing with double negatives.
It was something like "=--2" yielded a result of "5" or something
along those lines. But we were in the middle of something
and I didn't write it down. Are you familiar with that one?

This sounds like a documented feature of Excel. So no money.
The workaround is simple: use brackets.

=-5^2
returns +25

=(-5)^2
returns +25

=-(5^2)
returns +25

A math "bug" indeed, but a small one. It took me 2 seconds to find a
workaround. Implementing it for your client might take longer, bu
surely not a whole week.

In short: "Much ado about nothing."

 

[Amedee Van Gasse]

Precedence of operations is stated in the help. As far as I know,
there is no universally accepted order of precedence that covers all
combinations and permutations.

Again, sounds like your beef is with your consultant if you were
paying for his/her expertise in Excel.

Tom,

You are 100% right!
It sounds like he doesn't want to tell his client he wasted 1 week and
2000$ on something that simply can be found in the help. So the easy
way out is to blame MS for everything.

Welll... this is in my help (in Dutch)

Operator Beschrijving
– Negatief maken (zoals in –1)
% Procent
^ Machtsverheffen
* en / Vermenigvuldigen en delen
+ en – Optellen en aftrekken
& Twee tekenreeksen aan elkaar koppelen
= < > <= >= <> Vergelijken

As you can see, a negative sign has precendence over power.
Unfortunately for Fred, a negative sign and a subtraction are
typographically identical. There is only one good solution: using
brackets.

 

[Jerry W. Lewis]

Amedee Van Gasse wrote:
....

=-(5^2)
returns +25

I think you meant "returns -25"; +25 would be a bug for this one (but
not one that I can reproduce).

That -5^2 returns +25 is covered by Help for "About calculation
operators" subtopic "The order in which Excel performs operations in
formulas". As Tom noted, different programs may use different orders of
operator precedence. Programmers are responsible for learning the
languages that they use. It is a bit more problematic when one person
provides the formulas (or even the code) and another implements it, but
that is what testing is for.

I once got burned providing an algorithm that someone else then
implemented in a flavor of Basic that evaluated strictly left to right
with no algebraic hierarchy (other than parentheses) at all. I was
irritated that someone would write a language that worked that way, but
they documented how it worked, so our ignorance of their documentation
was not their fault. Fred's consultant should have known better than to
imply that any developer could be sued for producing a package that
operated as documented.

Jerry