How and where can I submit a bug found in excel?

[JohnnyB]

We have found what I would call a "bug" in the excel 2007 application. I need
to know how I can go about submitting this info to microsoft. Thanks!

 

[Gary''s Student]

Post it right here.

If it is a know problem, the user community might help you with a workaround.

 

[JohnnyB]

Ok here is the issue, it's very easy to re-create in fact.

On a new spreadsheet put 1.145 and subtract 1.1 from it. it should come out
to .045 right? Then in a new column take .145 and subtract .1 from it. It
also comes out to .045. Now here is the bug/issue. Change the cell format to
number, 2 decimal places. The first column changes to .04, the 2nd changes to
..05. Why is this? And it's not floating point errors, or set precision as
displayed issues. We are using excel 2007 w/ SP1.

I just want to know why excel changes one answer to .04 when in two decimal
places, and the other to .05, when they are the exact same in 3 decimal
places cell format. Again, please don't direct me to floating point KB
articles, this is not the issue. Just open a new spreadsheet and try what I
said for yourself to see waht I mean. Thanks for any help!

 

[Gary''s Student]

Sadly, the issue IS rounding:

in A1 enter:
1.145
in A2 enter:
..145
in B1 enter:
1.1
In B2 enter:
..1
In C1 enter:
=A1-B1
in C2 enter:
=A2-B2

The format col C with lots of decimal places. We see:

1.145 1.1 0.044999999999999900000000000000
0.145 0.1 0.045000000000000000000000000000

That's why, with limited decimal display, C1 and C2 look different.

 

[JohnnyB]

Thank you so much for explaining it, this has answered my question!

 

[JohnnyB]

One question though, why does the one have all those 9's when you increase
the decimal place, and the other doesnt? There were no 9's in the original
numbers, so that is a little strange. Sorry I am bad at math as well....

 

[Jerry W. Lewis]

Most computer hardware and software does math in binary, not decimal. In
binary, most terminating decimal fractions are non-terminating binary
fractions that can only be approximated in binary. When you have to
approximate the inputs, it should be no surprise if the output is also only
approximate.

Almost all binary computer math (including Excel) follows the IEEE standard
for double precision, which gives decimal accuracy of about 15 significant
figures. As a result, decimal digits beyond the 15th usually not what you
would expect. Excel will not display more than 15 decimal digits (it will
pad the display with meaningless zeros if you ask for more), but you can
often get a peak behind the curtain when you cancel leading significant
figures by subtraction.

The two calculations you described use binary values that have the following
decimal equivalents

1.145000000000000017763568394002504646778106689453125
-1.100000000000000088817841970012523233890533447265625
-----------------------------------------------------
0.0449999999999999289457264239899814128875732421875

0.1449999999999999900079927783735911361873149871826171875
-0.1000000000000000055511151231257827021181583404541015625
---------------------------------------------------------
0.044999999999999984456877655247808434069156646728515625

If you do the math, you will see that it is exactly correct, given the
unavoidable initial approximatisons.
If you note where the 15th significant figure is on each of the two answers,
you will see why they display differently.

Jerry