Why if I type in 51570.6213 Excel converts it to 51570.5213999999
chd
unable to get Excel to accept this number.
Pete at Sappi Fine Paper
51570.5212999999, not 51570.5213999999, and in any case, it does not display
that way in the cell, just in the formula bar. Simply format the cell
containing the data to show 4 decimal places and it will display 51570.5213
as you require.
Patricia Shannon
only approximate the value. Maybe it has too many significant digits to
store exactly as a number..
Jerry W. Lewis
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.
http://groups.google.com/group/microsoft.public.excel.misc/msg/f15e3c663545c8ef
gives another. 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 year
or two 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.1001001011100101001111100001101100001000100110100000B15
whose decimal value is 51570.62129999999888241291046142578125 which to
Excel's display limit of 15 figures is 51570.6213000000. If you increment
the binary representation by 1 in the last bit
1.1001001011100101001111100001101100001000100110100001B15
then its decimal value is 5.15706213000000061583705246448516845703125 which
is farther from 51570.6213 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
Dana DeLouis
> http://support.microsoft.com/kb/161234
Just a side note. The .848 bug listed in this article isn't quite
complete.
I believe it applies to all numbers that end with .848 and offset by a
factor of 1/8 (.125) within that same range of 32,768 and 65,535.
For example, .848+.125 = .973.
Numbers like 30000.973 will not display this bug,
but a number like 40000.973 will.
--
HTH. :>)
Dana DeLouis
Windows XP, Office 2003
"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:9535A177-2B67-403D...@microsoft.com...
Jerry W. Lewis
meant that they had not only overlooked the other seven 3-digit decimal
fractions that you mentioned, including the one we discussed at
http://groups.google.com/group/microsoft.public.excel.misc/msg/9613c573c4423a27
also 136 4-digit decimal fractions, such as the one the OP noticed,
1,392 5-digit decimal fractions, such as the one at
http://groups.google.com/group/microsoft.public.excel.misc/msg/f15e3c663545c8ef
14,080 6-digit decimal fractions,
140,672 7-digit decimal fractions
1,406,848 8-digit decimal fractions
...
The ones I have counted are ones that occur when the integer part of the
number is between 2^15 and 2^16. I do not know if it can happen for decimal
fractions that are displayed correctly when the integer part is in this
range, but I do know that thousands of these decimal fractions are also
mis-displayed with much smaller integer parts (As I recall, I had seen it
with integer parts as small as 2^7=128).
You can easily identify these in VBA, since if x is a Double that is
mis-displayed, then
CStr(x) <> Evaluate(x)
Jerry