Sum - Sum < Zero ??? How is it? Bug in Excel formula?
 

Hi, I just found this "feature" at Excel formulas today.

Please, have a look at: http://danielgudang.multiply.com/journal/item/192
(in portuguese, but images show all)


Let me explain:


some cell C1 = sum (C2:C5)
some cell D1 = sum (D2:D5)
some cell E1 = sum (E2:E5)


A1 = C1 - D1 - E1
B1 = C1 - (D1 + E1)


A1 = B1 ??? Oh, not always!


Sometimes A1 will be +0, sometimes -0.


Really strange!

Sum - Sum < Zero ??? How is it? Bug in Excel formula?
 

You're likely looking at rounding errors.

Excel is limited to 15 digits of precision in its calculation.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"danieldc" wrote:

Hi, I just found this "feature" at Excel formulas today.

Please, have a look at: http://danielgudang.multiply.com/journal/item/192
(in portuguese, but images show all)


Let me explain:


some cell C1 = sum (C2:C5)
some cell D1 = sum (D2:D5)
some cell E1 = sum (E2:E5)


A1 = C1 - D1 - E1
B1 = C1 - (D1 + E1)


A1 = B1 ??? Oh, not always!


Sometimes A1 will be +0, sometimes -0.


Really strange!

Sum - Sum < Zero ??? How is it? Bug in Excel formula?
 

Here's an example of rounding errors:

I have in the formula =LN(RAND()^2) in C2:E5 and then I sum C2:C5, D2:D5,
and E5 and then do your formulas in A1 and B1. So far, everything is as you
create it.

Then I widened the cells so that 15 digits are displayed.

Finally, in A4 I put the formula =IF(A1=B1,"",1) so that a 1 shows if the
two cells are not exactly equal. Pressing F9 repeatedly yields a 1
eventually (and my guess is that the occurrence of 1s could be predicted if
one were so inclined.) In any event, the following two values triggered the
1:

0.0102071029117790000

and

0.0102071029117798000

As you can see these are not equal but only very approximately equal, due to
Excel's 15 digit of precision calculation limit.

Dave

--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"danieldc" wrote:

Hi, I just found this "feature" at Excel formulas today.

Please, have a look at: http://danielgudang.multiply.com/journal/item/192
(in portuguese, but images show all)


Let me explain:


some cell C1 = sum (C2:C5)
some cell D1 = sum (D2:D5)
some cell E1 = sum (E2:E5)


A1 = C1 - D1 - E1
B1 = C1 - (D1 + E1)


A1 = B1 ??? Oh, not always!


Sometimes A1 will be +0, sometimes -0.


Really strange!

Sum - Sum < Zero ??? How is it? Bug in Excel formula?
 

Not just an excel feature, but a well documented issue with binary fractions.
Basically, some decimals cannot be represented exactly in binary (same way
1/3 cannot be represented exactly as a decimal - using base 10 anyway).

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

Also, if you search on this site, you should find several threads on this
issue w/explanations from folks who are much more knowledgeable than I (Jerry
Lewis had some good posts on this).

My suggestion is to use the Round function whenever you use a conditional
statement involving numbers that are or could be decimals.


"danieldc" wrote:

Hi, I just found this "feature" at Excel formulas today.

Please, have a look at: http://danielgudang.multiply.com/journal/item/192
(in portuguese, but images show all)


Let me explain:


some cell C1 = sum (C2:C5)
some cell D1 = sum (D2:D5)
some cell E1 = sum (E2:E5)


A1 = C1 - D1 - E1
B1 = C1 - (D1 + E1)


A1 = B1 ??? Oh, not always!


Sometimes A1 will be +0, sometimes -0.


Really strange!

Sum - Sum < Zero ??? How is it? Bug in Excel formula?
 

On 29 mar, 17:08, Dave F wrote:
Here's an example of rounding errors:

I have in the formula =LN(RAND()^2) in C2:E5 and then I sum C2:C5, D2:D5,
and E5 and then do your formulas in A1 and B1. So far, everything is as you
create it.


Yes, you got it.


As you can see these are not equal but only very approximately equal, due to
Excel's 15 digit of precision calculation limit.


I had believed that Excel's 15 digit limit of precision calculation
could be enough...
But now, I changed my mind.

I will put some "truncate()" function at all places when I need
"exact" values.

Maybe that truncate(A1; 2) = truncate(B1; 2) will be always evaluated
TRUE.

yours, Daniel
from Brazil

Sum - Sum < Zero ??? How is it? Bug in Excel formula?
 

That will work.

This is a bit more efficient:

=ABS(A1-B1)<0.01

In article . com,
"danieldc" wrote:

Maybe that truncate(A1; 2) = truncate(B1; 2) will be always evaluated
TRUE.