$ - 0.00 nevgative zero

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" wrote:

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

Michele wrote:

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

--

Dave Peterson

On Jun 18, 6:48 am, Michele wrote:
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
[see below]

That proves nothing to me. First, the arithmetic above could
introduce rounding error. Second, you do not know say how the column
of numbers that are summed is created. Perhaps those cells have
rounding errors.

The real proof is to format the cell with the formula above using
Scientific format and 14 or 15 decimal places. Actually, even that is
not "real proof" since neither format is a truly accurate picture of
the internal binary number. But they usually expose the internal
rounding error.

When I cut and paste the column of numbers that you posted, then
compute =SUM(A1:A16), the result is (positive) 0.00 when formatted as
Number with 2 decimal places. But it is about 7E-14 when formatted as
Scientific with 14 decimal places.

I'm not sure why you see -0.00. I can only guess that the numbers
that you posted were not entered directly, and the rounding error in
some of those cells tips the sum in the negative direction.

This is an annoying property of binary computers: they cannot store
even the simplest numbers accurately, for the most part. So there is
almost always some miniscule rounding error. Some work-arounds:

(1) Compute =round(C3-SUM(...),2)

(2) Set the Calculation option "Precision as displayed".

Personally, I do not like #2. It can have unforeseeable consequences.

Theoretically, even #1 should not be guaranteed to solve the problem.
I can only guess that it triggers internal heuristics that try to root
out the rounding error inherent in binary computers.

As an aside....

Earlier you wrote:
Zero is Zero.......LOL

Not a ones-complement computer like the CDC 6400 (circa 1960s) ;-).
But that's beside the point.


----- complete previous posting -----

On Jun 18, 6:48 am, Michele wrote:
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

i reformated all my cells to 30 decimal places and the numbers look correct
and look as follows:
-400.000000000000000000000000000000
-923.450000000000000000000000000000
60.000000000000000000000000000000
36.000000000000000000000000000000
44.000000000000000000000000000000
283.180000000000000000000000000000
36.000000000000000000000000000000
36.000000000000000000000000000000
200.000000000000000000000000000000
145.100000000000000000000000000000
127.350000000000000000000000000000
35.500000000000000000000000000000
30.600000000000000000000000000000
162.600000000000000000000000000000
106.320000000000000000000000000000
20.800000000000000000000000000000
my beginning balance reads: 0.000000000000000000000000000000
but my ending balance is: 0.000000000000074606987254810500
i still don't understand if all this reads as above then how can there be a
rounding error.
Thank you all for helping me try to figure this out. i'm not argueing with
anyone, just trying to understand.

Hi Michele

I think JE McGimpsey summed it up perfectly

"for the SUM(J7:Jx) part. It's an inherent problem with representing
finite decimal numbers in binary (just like in decimal, nearly ALL
numbers can't be represented in a fixed number of binary digits).

When performing operations on currency it's usually helpful to use
ROUND(xxxx,2) to ensure that those small errors are discarded."


"Michele" wrote:

i reformated all my cells to 30 decimal places and the numbers look correct
and look as follows:
-400.000000000000000000000000000000
-923.450000000000000000000000000000
60.000000000000000000000000000000
36.000000000000000000000000000000
44.000000000000000000000000000000
283.180000000000000000000000000000
36.000000000000000000000000000000
36.000000000000000000000000000000
200.000000000000000000000000000000
145.100000000000000000000000000000
127.350000000000000000000000000000
35.500000000000000000000000000000
30.600000000000000000000000000000
162.600000000000000000000000000000
106.320000000000000000000000000000
20.800000000000000000000000000000
my beginning balance reads: 0.000000000000000000000000000000
but my ending balance is: 0.000000000000074606987254810500
i still don't understand if all this reads as above then how can there be a
rounding error.
Thank you all for helping me try to figure this out. i'm not argueing with
anyone, just trying to understand.

i reformated all my cells to 30 decimal places and the numbers look correct
and look as follows:
-400.000000000000000000000000000000
-923.450000000000000000000000000000
60.000000000000000000000000000000
36.000000000000000000000000000000
44.000000000000000000000000000000
283.180000000000000000000000000000
36.000000000000000000000000000000
36.000000000000000000000000000000
200.000000000000000000000000000000
145.100000000000000000000000000000
127.350000000000000000000000000000
35.500000000000000000000000000000
30.600000000000000000000000000000
162.600000000000000000000000000000
106.320000000000000000000000000000
20.800000000000000000000000000000
my beginning balance reads: 0.000000000000000000000000000000
but my ending balance is: 0.000000000000074606987254810500
i still don't understand if all this reads as above then how can there be
a
rounding error.

I don't have an answer to **why** it is happening, but that ending balance
looks like it's a Single data type rather than a Double data type. When a
Single is coerced to a Double, the extra precision required by the Double is
just junk (I think the Double inherits the Single's value plus whatever is
in the "extra" bit locations taken over for storing the Double that is was
not supplied by the Single). Do you have an macros running as part of this
calculation?

Rick

In article ,
Michele wrote:

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.

There isn't a particular number that's causing your problem. It's a
problem with the fact that decimal numbers can't always be represented
by a fixed number of binary digits, which is what the math processor
deals with. So when the math processor has to work with these numbers
with slight errors in representation, the result often has a slight
error in representation.

This isn't a problem if you just want to display the number - the
display engine will round it for you.

But XL has no way of knowing, when you compare the value to zero,
whether that slight error is significant to you or not. With currency,
a value of that magnitude certainly is insignificant. With engineering
calculations - perhaps or perhaps not.


There is no way to prevent this other than using integers - It's
inherent in all computers and all spreadsheets. So one deals with it by
using ROUND(xxx,2) for currency.

ok so how would i put that in my formula? i looked and tried a few times, i
can get it to round but not add and round.

=C3-SUM(J7:J65536)

"steve_doc" wrote:

Hi Michele

I think JE McGimpsey summed it up perfectly

"for the SUM(J7:Jx) part. It's an inherent problem with representing
finite decimal numbers in binary (just like in decimal, nearly ALL
numbers can't be represented in a fixed number of binary digits).

When performing operations on currency it's usually helpful to use
ROUND(xxxx,2) to ensure that those small errors are discarded."


"Michele" wrote:

i reformated all my cells to 30 decimal places and the numbers look correct
and look as follows:
-400.000000000000000000000000000000
-923.450000000000000000000000000000
60.000000000000000000000000000000
36.000000000000000000000000000000
44.000000000000000000000000000000
283.180000000000000000000000000000
36.000000000000000000000000000000
36.000000000000000000000000000000
200.000000000000000000000000000000
145.100000000000000000000000000000
127.350000000000000000000000000000
35.500000000000000000000000000000
30.600000000000000000000000000000
162.600000000000000000000000000000
106.320000000000000000000000000000
20.800000000000000000000000000000
my beginning balance reads: 0.000000000000000000000000000000
but my ending balance is: 0.000000000000074606987254810500
i still don't understand if all this reads as above then how can there be a
rounding error.
Thank you all for helping me try to figure this out. i'm not argueing with
anyone, just trying to understand.

All XL numeric values are doubles.

The problem is inherent in double precision floating point math.

See

http://cpearson.com/excel/rounding.htm

for more.

In article ,
"Rick Rothstein \(MVP - VB\)"
wrote:

I don't have an answer to **why** it is happening, but that ending balance
looks like it's a Single data type rather than a Double data type. When a
Single is coerced to a Double, the extra precision required by the Double is
just junk (I think the Double inherits the Single's value plus whatever is
in the "extra" bit locations taken over for storing the Double that is was
not supplied by the Single). Do you have an macros running as part of this
calculation?

No
no maco or anything else running

"Rick Rothstein (MVP - VB)" wrote:

i reformated all my cells to 30 decimal places and the numbers look correct
and look as follows:
-400.000000000000000000000000000000
-923.450000000000000000000000000000
60.000000000000000000000000000000
36.000000000000000000000000000000
44.000000000000000000000000000000
283.180000000000000000000000000000
36.000000000000000000000000000000
36.000000000000000000000000000000
200.000000000000000000000000000000
145.100000000000000000000000000000
127.350000000000000000000000000000
35.500000000000000000000000000000
30.600000000000000000000000000000
162.600000000000000000000000000000
106.320000000000000000000000000000
20.800000000000000000000000000000
my beginning balance reads: 0.000000000000000000000000000000
but my ending balance is: 0.000000000000074606987254810500
i still don't understand if all this reads as above then how can there be
a
rounding error.

I don't have an answer to **why** it is happening, but that ending balance
looks like it's a Single data type rather than a Double data type. When a
Single is coerced to a Double, the extra precision required by the Double is
just junk (I think the Double inherits the Single's value plus whatever is
in the "extra" bit locations taken over for storing the Double that is was
not supplied by the Single). Do you have an macros running as part of this
calculation?

Rick

One way:

=ROUND(C3-SUM(J7:J65536),2)



In article ,
Michele wrote:

ok so how would i put that in my formula? i looked and tried a few times, i
can get it to round but not add and round.

=C3-SUM(J7:J65536)