Excel error: 1-0.99*0.99 <> 3.94039900000001%

[juanroberts]

The formula 1-0.99*0.99*0.99*0.99 in Excel equals 3.94039900000001%, which is
an error. It should instead equal 3.940399%. There has to be a bug somewhere.

This also happens with .98

1-0.98*0.98 = 3.96000000000001%

But not when using .97

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...8f0499b&dg=microsoft.public.excel.crashesgpfs

 

[Gary''s Student]

This is simple round-off error and can easily be avoided.

 

[juanroberts]

No, it is not.

If you go through the trouble to format a cell so it shows 20 decimals, you
find out that:

1-0.98*0.98 = 0.03960000000000010000
1-0.97*0.87 = 0.05910000000000000000

Note the first answer has a "1" in the 5th to last number of the mantisa,
while the second answer does not.

The problem is when I want to check my models because this bug throws the
numbers off and equalities no longer hold.

John

 

[garfield-n-odie [MVP]]

See http://support.microsoft.com/kb/78113 "Floating-point
arithmetic may give inaccurate results in Excel" and
http://support.microsoft.com/kb/269370 "Last digits are changed
to zeroes when you type long numbers in cells of Excel". That's
the way it is... learn to deal with it.

 

[juanroberts]

Garfield,

I concede that bug happens near the end of the mantissa, but then please
explain to me why it is actually outside of it, its the 17th number. Thanks
for the webpage you shared. Sorry, but I also think you missed my point,
which is that there is at least a lack of consistency on behalf of Excel. The
error I am describing is an odd exception, not the rule.

Next time someone responds, I would appreciate it if you could please try
the formulas I presented in Excel and perhaps fiddle with other possible
numbers so that you can get a feel fro what is going on in terms of this blip
in programming?

Here is another example. Plug in these three formulas in Excel. Then tell me
why mathematically equivalents equate for IF statements 1 and 3, but not for
the one in the middle.

=IF(1-0.99*0.99=1-0.9801,"OK","ERROR")
=IF(1-0.98*0.98=1-0.9604,"OK","ERROR")
=IF(1-0.97*0.97=1-0.9409,"OK","ERROR")

Thank you.

John

 

[juanroberts]

Sorry, my mistake, it is not the 17th digit.

 

[Jerry W. Lewis]

The math is correct, but your inputs were unavoidably approximated.

Almost all computer software does math in binary, not decimal. Most
terminating decimal fractions (including 0.99 and 0.98) are non-terminating
binary fractions that can only be approximated (much as 1/3 can only be
approximated as a decimal fraction).

You cannot directly see the approximation because Excel will display no more
than 15 significant digits (see Help for "specifications"), if you ask for
more, it is simply padded with trailing zeros. However, I have posted code
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
that will display more decimal digits. Using the D2D function, you will
find that 0.99 is approximated as
0.9899999999999999911182158029987476766109466552734375
so that 1-0.99*0.99*0.99*0.99 is
0.0394039900000000553603740627295337617397308349609375
Similarly, 0.98 is approximated as
0.979999999999999982236431605997495353221893310546875
so that 1-0.98*0.98 is
0.03960000000000007958078640513122081756591796875

All this is per the IEEE standard for double precision, and is the same
result you will get from almost all software. Your options are to either
stick to integer arithmetic, round results to a suitable level, or structure
your comparisons to allow near equality, such as ABS(a-b)<epsilon instead of
a=b.

Jerry