$ - 0.00 nevgative zero

[Michele]

THANK YOU EVERONE FOR ALL YOU HELP

 

[Rick Rothstein \(MVP - VB\)]

All XL numeric values are doubles.

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

 

[Jerry W. Lewis]

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

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.

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