software

Why if I type in 51570.6213 Excel converts it to 51570.5213999999. I am
unable to get Excel to accept this number.

Interesting quirk. I believe you'll find the number registers as
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.

quot;chdquot; wrote:

gt; Why if I type in 51570.6213 Excel converts it to 51570.5213999999. I am
gt; unable to get Excel to accept this number.

I would say it's stored in some kind of internal binary format, which can
only approximate the value. Maybe it has too many significant digits to
store exactly as a number..

quot;chdquot; wrote:

gt; Why if I type in 51570.6213 Excel converts it to 51570.5213999999. I am
gt; unable to get Excel to accept this number.

It is the display bug that is minimized in
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.
groups.google.com/group/micro...5e3c663545c8ef
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
groups.google.com/group/micro...06871cf92f8465
you can verify that the binary representation of your number is
1.100100101110010100111110000110110000100010011010 0000B15
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.100100101110010100111110000110110000100010011010 0001B15
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

quot;chdquot; wrote:

gt; Why if I type in 51570.6213 Excel converts it to 51570.5213999999. I am
gt; unable to get Excel to accept this number.

gt; It is the display bug that is minimized in
gt; 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. :gt;)
Dana DeLouis
Windows XP, Office 2003quot;Jerry W. Lewisquot; gt; wrote in message
...
gt; It is the display bug that is minimized in
gt; support.microsoft.com/kb/161234
gt; Contrary to the discussion in that KB article, there are literally
gt; thousands
gt; of decimal fractions that do not display properly in Excel.
gt; groups.google.com/group/micro...5e3c663545c8ef
gt; gives another. I recall at least one other newsgroup thread that I cannot
gt; immediately locate
gt;
gt; In all instances that I have seen (I programmed a systematic search a year
gt; or two ago), each number is correctly represented in binary, and that
gt; binary
gt; approximation should display to 15 figures identically to the input
gt; number,
gt; but for some unaccountable reason the display is off by 1 in the 15
gt; figure.
gt;
gt; Using the functions at
gt; groups.google.com/group/micro...06871cf92f8465
gt; you can verify that the binary representation of your number is
gt; 1.100100101110010100111110000110110000100010011010 0000B15
gt; whose decimal value is 51570.62129999999888241291046142578125 which to
gt; Excel's display limit of 15 figures is 51570.6213000000. If you increment
gt; the binary representation by 1 in the last bit
gt; 1.100100101110010100111110000110110000100010011010 0001B15
gt; then its decimal value is 5.15706213000000061583705246448516845703125
gt; which
gt; is farther from 51570.6213 than the representation that Excel used. Hence
gt; Excel is representing the number correctly, but for some unknown reason
gt; fails
gt; to display that representation properly.
gt;
gt; Given that it appears to be a display issue rather than a numeric issue,
gt; and
gt; the display is off by so little, it is more of an annoyance than an
gt; accuracy
gt; concern, but it is a surprising that MS has done nothing about it in over
gt; a
gt; decade.
gt;
gt; Jerry
gt;
gt; quot;chdquot; wrote:
gt;
gt;gt; Why if I type in 51570.6213 Excel converts it to 51570.5213999999. I am
gt;gt; unable to get Excel to accept this number.
Sorry if I was not clear. When I say that MS minimized this display bug, I
meant that they had not only overlooked the other seven 3-digit decimal
fractions that you mentioned, including the one we discussed at
groups.google.com/group/micro...13c573c4423a27
also 136 4-digit decimal fractions, such as the one the OP noticed,
1,392 5-digit decimal fractions, such as the one at
groups.google.com/group/micro...5e3c663545c8ef
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) lt;gt; Evaluate(x)

Jerry

quot;Dana DeLouisquot; wrote:

gt; gt; It is the display bug that is minimized in
gt; gt; support.microsoft.com/kb/161234
gt;
gt; Just a side note. The .848 bug listed in this article isn't quite
gt; complete.
gt; I believe it applies to all numbers that end with .848 and offset by a
gt; factor of 1/8 (.125) within that same range of 32,768 and 65,535.
gt; For example, .848 .125 = .973.
gt; Numbers like 30000.973 will not display this bug,
gt; but a number like 40000.973 will.
gt;
gt; --
gt; HTH. :gt;)
gt; Dana DeLouis
gt; Windows XP, Office 2003
gt;
gt;
gt; quot;Jerry W. Lewisquot; gt; wrote in message
gt; ...
gt; gt; It is the display bug that is minimized in
gt; gt; support.microsoft.com/kb/161234
gt; gt; Contrary to the discussion in that KB article, there are literally
gt; gt; thousands
gt; gt; of decimal fractions that do not display properly in Excel.
gt; gt; groups.google.com/group/micro...5e3c663545c8ef
gt; gt; gives another. I recall at least one other newsgroup thread that I cannot
gt; gt; immediately locate
gt; gt;
gt; gt; In all instances that I have seen (I programmed a systematic search a year
gt; gt; or two ago), each number is correctly represented in binary, and that
gt; gt; binary
gt; gt; approximation should display to 15 figures identically to the input
gt; gt; number,
gt; gt; but for some unaccountable reason the display is off by 1 in the 15
gt; gt; figure.
gt; gt;
gt; gt; Using the functions at
gt; gt; groups.google.com/group/micro...06871cf92f8465
gt; gt; you can verify that the binary representation of your number is
gt; gt; 1.100100101110010100111110000110110000100010011010 0000B15
gt; gt; whose decimal value is 51570.62129999999888241291046142578125 which to
gt; gt; Excel's display limit of 15 figures is 51570.6213000000. If you increment
gt; gt; the binary representation by 1 in the last bit
gt; gt; 1.100100101110010100111110000110110000100010011010 0001B15
gt; gt; then its decimal value is 5.15706213000000061583705246448516845703125
gt; gt; which
gt; gt; is farther from 51570.6213 than the representation that Excel used. Hence
gt; gt; Excel is representing the number correctly, but for some unknown reason
gt; gt; fails
gt; gt; to display that representation properly.
gt; gt;
gt; gt; Given that it appears to be a display issue rather than a numeric issue,
gt; gt; and
gt; gt; the display is off by so little, it is more of an annoyance than an
gt; gt; accuracy
gt; gt; concern, but it is a surprising that MS has done nothing about it in over
gt; gt; a
gt; gt; decade.
gt; gt;
gt; gt; Jerry
gt; gt;
gt; gt; quot;chdquot; wrote:
gt; gt;
gt; gt;gt; Why if I type in 51570.6213 Excel converts it to 51570.5213999999. I am
gt; gt;gt; unable to get Excel to accept this number.
gt;
gt;
gt;