G
Guest
THANK YOU EVERONE FOR ALL YOU HELP
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
R
Rick Rothstein \(MVP - VB\)
All XL numeric values are doubles.
No, I am aware of this problem (which usually only affects the last 2 of 3
decimal digits in a Double)... what happened is I looked at the number
Michele posted and, without counting to see the first non-zero digit was in
the 14th decimal digit, saw half the number was affected by the problem and
thought 8 zeroes followed by 8 garbage digits... it looked like the old
Single-to-Double coercion problem.
Rick
The problem is inherent in double precision floating point math.
No, I am aware of this problem (which usually only affects the last 2 of 3
decimal digits in a Double)... what happened is I looked at the number
Michele posted and, without counting to see the first non-zero digit was in
the 14th decimal digit, saw half the number was affected by the problem and
thought 8 zeroes followed by 8 garbage digits... it looked like the old
Single-to-Double coercion problem.
Rick
G
Guest
The only number that NOT "causing" the problem is 400. The only 2-place
decimal fractions that CAN be exactly represented in binary are .00, .25,
..50, and .75. The rest must be approximated, and will have non-zero figures
beyond the 15th figure. As documented, Excel will not display more than 15
figures (as you found, it will just show zeros instead of what is really
there, if you ask for more than 15 figures).
For example, the actual decimal value of the binary approximation to 923.45
is 923.450000000000045474735088646411895751953125.
Jerry
decimal fractions that CAN be exactly represented in binary are .00, .25,
..50, and .75. The rest must be approximated, and will have non-zero figures
beyond the 15th figure. As documented, Excel will not display more than 15
figures (as you found, it will just show zeros instead of what is really
there, if you ask for more than 15 figures).
For example, the actual decimal value of the binary approximation to 923.45
is 923.450000000000045474735088646411895751953125.
Jerry
Michele said:i have triple checked my numbers and can't find any that are not what they
shold be. Is there a way to find out which one is causing the prob.
Dave Peterson said:I put those numbers in a1:a16 and then used
=sum(a1:a16)
and saw $0.00
in the display.
But if I selected the cell and hit F2 (to edit it), then F9 to convert the
formula to a value, I saw:
7.46069872548105E-14
in the formula bar (the cell continued to display $0.00).
JE McGimpsey explains how a computer deals with numbers:
http://mcgimpsey.com/excel/pennyoff.html
i thought of the rounding error but that is not the case my formula is as
follows: =C3-SUM(J7:J65536) this formula is adding up the following amounts:
- $ 400.00
- $ 923.45
$ 60.00
$ 36.00
$ 44.00
$ 283.18
$ 36.00
$ 36.00
$ 200.00
$ 145.10
$ 127.35
$ 35.50
$ 30.60
$ 162.60
$ 106.32
$ 20.80
the format i have used is in these cells is: $ #,##0.00;[Red}-$ #,##0.00