62723.973
MPC
62723.9729999999 show in the formula bar?
Just wondering. I can't seem to get it to BE 62723.973
TIA,
MPC
Bernard Liengme
You have discovered one of the properties of all binary number as used
on all computer - they cannot represent every decimal number exactly.
There just is not an exact binary representation of decimal 62723.973
(at least when the precision is the IEEE value of 16)
But then our "human" decimal system has it faults. Can you tell me
EXACTLY what 10/3 works out to be? Answer is
3.3333333333333333333333333333333333333333333333333333 and so on. No
exact
representation of the simple value.
Best wishes
Bernard
PS to the MVP's: Anyone have a URL for a site that gives a full
explanation? This question is so common.
Jerry W. Lewis
this appears to also involve a bug in the numeric display routine. If
you enter 62723.973 in A1, it will display 62723.9729999999 in the
formula bar. If you then enter 62723.9729999999 in A2 and compute
=(A1-A2)/A1, you will get 1.508E-15, which indicates that Excel is
numerically preserving the difference between the numbers, but failing
to display them correctly to 15 figures as Excel's specs claim.
Jerry
Myrna Larson
translating a number from decimal to binary. In the other you are adding floating point
subtraction AND division to the situation. Each of those operations has its own limitations.
Jim Rech
This is a good article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q78113
--
Jim Rech
Excel MVP
Jerry W. Lewis
between the binary representation of both numbers that Excel displays as
62723.9729999999 is bigger than 1E-15, which Excel documentation claims
can be differentiated (but isn't in this case, i.e. a bug in the decimal
display of numbers).
If you prefer, enter 62723.973 in A1, copy the displayed
62723.9729999999 from the formula bar, and paste it into the formula bar
for A2, then put =A1*10000000000 in B1 and copy the formula down to B2.
You will again see that the issue is that Excel doesn't display
62723.973 correctly, even though its binary approximation is adequate to
the task.
My point is that this is a display bug, not a math bug.
Jerry
Jerry W. Lewis
that both display as 62723.9729999999 but which have binary
representations that differ by more than 1E-15, and thus according to
Excel's specifications should have different decimal representations.
If you prefer, you could enter 62723.973 in A1, copy its decimal
representation 62723.9729999999 from the formula bar and paste it into
the formula bar of A2. Then enter =A1*10000000000 into B1 and copy the
formula down to B2. You will again see that the two numbers that are
both displayed as 62723.9729999999 have binary representations that are
different enough that A1 should have displayed as 62723.973
Bottom line is that this is a display bug, not a math bug. I am not
aware of any bugs in basic arithmetic since MS fixed the recalc bugs in
Excel 97.
Jerry
Jerry W. Lewis
that both display as 62723.9729999999, even though their binary
representations differ by more than 1E-15, which according to Excel's
published specifications should result in different 15-digit decimal
representations.
If you prefer, you could could paste 62723.973 into A1, copy its decimal
representation, 62723.9729999999, from the formula bar and paste it into
the formula bar for A2. Now enter =A1*10000000000 into B1 and copy it
down to B2. Again you will see that binary representations are
sufficient to differentiate A1 and A2, even though Excel fails to do so.
Finally consider the tried and true recommendation: put
=ROUND(62723.973,3)
in A3 and format the cell to display 10 decimal places.
Three different approaches show that Excel cannot properly display the
binary representation of 62723.973.
Bottom line is that this is a bug in the display routine (verified in
Excel 97 and Excel XP), not a bug in the math. I am not aware of any
basic math errors in Excel since MS fixed the recalc bugs in Excel 97.
Jerry
Jerry W. Lewis
that both display as 62723.9729999999, even though their binary
representations differ by more than 1E-15, which according to Excel's
published specifications should result in different 15-digit decimal
representations.
If you prefer, you could could paste 62723.973 into A1, copy its decimal
representation, 62723.9729999999, from the formula bar and paste it into
the formula bar for A2. Now enter =A1*10000000000 into B1 and copy it
down to B2. Again you will see that binary representations are
sufficient to differentiate A1 and A2, even though Excel fails to do so.
Finally consider the tried and true recommendation: put
=ROUND(62723.973,3)
in A3 and format the cell to display 10 decimal places.
Three different approaches show that Excel cannot properly display the
binary representation of 62723.973.
Bottom line is that this is a bug in the display routine (verified in
Excel 97 and Excel XP), not a bug in the math. I am not aware of any
basic math errors in Excel since MS fixed the recalc bugs in Excel 97.
Jerry
Dana DeLouis
in Excel XP either.
The .973 is an error because it occurs between 32,768 and 65,535. (That is
2^15 thru 2^16)
For example, the numbers 32000.973 and 66000.973 are not adjusted by Excel
when entered because they fall outside the range.
There are a few other fractional numbers that exibit this BUG within this
same range.
For example, the other common number is .848. This particular number is
listed here.
It's a shame Microsoft did not fix this because it's been a known problem
for a long time.
XL: Numbers Ending in .848 Appear Incorrectly (Q161234)
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q161234
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:3CA30F08.8010903@no_e-mail.com...
Myrna Larson
So the numbers presumably differ by more than 1E-15. What if neither of these numbers can
accurately converted from binary to decimal? There's also the problem of accurately translating
the difference from binary to decimal.