Excel Incorrectly Displays Data

[bfarr112]

In Excel 2003, try typing in 5523.00159 into a cell and set its formatting to
Number with 5 decimal points and no commas. Once you enter this data, it
looks good in the cell but if you go back to the cell and look in the formula
bar, it displays 5523.00158999999. I've tried using other numbers but it
only appears to happen when there are 4 numbers in front of the decimal point
and .00159 after the decimal. Is there anyone who can explain to me why this
would be happening or how to fix it? This is really driving me nuts and
making my data incorrect!

 

[Jerry W. Lewis]

You are seeing the display bug that MS minimizes in
http://support.microsoft.com/kb/161234
Contrary to the discussion in that KB article, there are literally thousands
of decimal fractions that do not display properly in Excel. Some that have
previously been reported in the newsgroups are
http://groups.google.com/group/microsoft.public.excel.misc/msg/f15e3c663545c8ef
http://groups.google.com/group/microsoft.public.excel.misc/msg/f074a072a96998ad
I recall at least one other newsgroup thread that I cannot immediately locate

In all instances that I have seen (I programmed a systematic search a couple
of years ago), each number is correctly represented in binary, and that
binary approximation should display to 15 figures identically to the input
number, but for some unaccountable reason the display is off by 1 in the 15
figure.


Using the functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
you can verify that the binary representation of your number is
1.0101100100110000000001101000001100111100011000000000B12
whose decimal value is 5523.0015899999998509883880615234375 which to Excel's
display limit of 15 figures is 5523.00159000000. If you increment the binary
representation by 1 in the last bit
1.0101100100110000000001101000001100111100011000000001B12
then its decimal value is 5523.0015900000007604830898344516754150390625
which is farther from 5523.00159 than the representation that Excel used.
Hence Excel is representing the number correctly, but for some unknown reason
fails to display that representation properly.


Given that it appears to be a display issue rather than a numeric issue, and
the display is off by so little, it is more of an annoyance than an accuracy
concern, but it is a surprising that MS has done nothing about it in over a
decade.

Jerry

 

[bfarr112]

Thanks for the follow-up! I hadn't ever run across this before but now that
I know its not just my machine freaking out, I can sleep in peace at night!
Thanks!

 

[Jerry W. Lewis]

You're welcome.

Sleep well,
Jerry