Calculation inconsistent

[PMBO]

I have two columns of numbers that should sum exactly the same, to zero. In
Column One I have negative 50,000 and a negative 113.78 (separately). In
Column Two I have negative 50113.78. The result should be the same. I'm
using the accounting format with two decimal places. Column One gives me a
(0.00) result and Column Two gives me a dash. I know that if I use the Set
Precision as Displayed I can force them to look consistent. Can anyone
explain why this is happening, please?

Column One
100,113.78
(50,000.00)
(50,000.00)
(113.78)
Total (0.00)


Column Two
100,113.78
(50,000.00)
-
(50,113.78)
Total -

 

[Eduardo]

Hi,
That is a format issue
Go to where you have the "0" and copy, then go where you have the "-" and
paste special formats, that will solve your problem

 

[PMBO]

Eduardo -

That doesn't work for me. Any other suggestions?

 

[joeu2004]

I have two columns of numbers that should sum exactly
the same, to zero.
[....]
Column One gives me a (0.00) result and Column Two
gives me a dash. I know that if I use the Set
Precision as Displayed I can force them to look
consistent. Can anyone explain why this is
happening, please?

The (0.00) result is telling you that the sum is not exactly zero.
The dash represents exactly zero.

The reason for the difference -- in particular, the non-zero result in
Column One -- is because of the way that Excel and most applications
do arithmetic. They rely on the binary representation of numbers. So
most numbers with decimal fractions cannot be represented exactly.
Consequently, arithmetic operations frequently introduce numeric
"errors"; that is, differences from expectations.

For example, 100,113.78 is represented internally exactly as
100113.77999999999883584678173065185546875, and -113.78 is exactly
-113.780000000000001136868377216160297393798828125. More to the
point, 100,113.78 - 50,000 - 50,000 is
113.77999999999883584678173065185546875. So when -113.78 is added to
that, the result is
-0.000000000001165290086646564304828643798828125.

In contrast, by coincidence, -50113.78 is represented internally
exactly as -50113.77999999999883584678173065185546875, and 100,113.78
- 50,000 results in exactly the same value, just positive. So the sum
is indeed exactly zero.

As you noted, the work-arounds include using Precision as Displayed or
liberal use of ROUND(...,2), where "..." is any compuational
expression. For example, =ROUND(SUM(A1:A4),2).


----- original posting -----

 

[PMBO]

Thanks Joe - I appreciate the explanation. PMBO

I have two columns of numbers that should sum exactly
the same, to zero.
[....]
Column One gives me a (0.00) result and Column Two
gives me a dash. I know that if I use the Set
Precision as Displayed I can force them to look
consistent. Can anyone explain why this is
happening, please?

The (0.00) result is telling you that the sum is not exactly zero.
The dash represents exactly zero.

The reason for the difference -- in particular, the non-zero result in
Column One -- is because of the way that Excel and most applications
do arithmetic. They rely on the binary representation of numbers. So
most numbers with decimal fractions cannot be represented exactly.
Consequently, arithmetic operations frequently introduce numeric
"errors"; that is, differences from expectations.

For example, 100,113.78 is represented internally exactly as
100113.77999999999883584678173065185546875, and -113.78 is exactly
-113.780000000000001136868377216160297393798828125. More to the
point, 100,113.78 - 50,000 - 50,000 is
113.77999999999883584678173065185546875. So when -113.78 is added to
that, the result is
-0.000000000001165290086646564304828643798828125.

In contrast, by coincidence, -50113.78 is represented internally
exactly as -50113.77999999999883584678173065185546875, and 100,113.78
- 50,000 results in exactly the same value, just positive. So the sum
is indeed exactly zero.

As you noted, the work-arounds include using Precision as Displayed or
liberal use of ROUND(...,2), where "..." is any compuational
expression. For example, =ROUND(SUM(A1:A4),2).


----- original posting -----

I have two columns of numbers that should sum exactly the same, to zero. In
Column One I have negative 50,000 and a negative 113.78 (separately). In
Column Two I have negative 50113.78. The result should be the same. I'm
using the accounting format with two decimal places. Column One gives me a
(0.00) result and Column Two gives me a dash. I know that if I use the Set
Precision as Displayed I can force them to look consistent. Can anyone
explain why this is happening, please?

Column One
100,113.78
(50,000.00)
(50,000.00)
(113.78)
Total (0.00)

Column Two
100,113.78
(50,000.00)
-
(50,113.78)
Total -