Conditional formula with summation not returning correct result

[Fattyfatfat Kid]

Hey everyone! This isn't necessarily a problem persay, as there are other ways of doing what I'm trying to do, but it just boggles my mind why this has worked just fine for years and now it doesn't in this particular case.

Here's what's going on, it's very simple:

______A___________B__________C__________D_______
1)_-$16.00_______-$0.50_______"Total 4"____=sum(A1:B1)
2)__-$4.50________$0.00______"Total 5"_____=sum(A2:B2)
3) ===========================================
4)_=sum(A1:A2)__=sum(B1:B2)__"Total"____PROBLEM CODE


Here's the problem code: =if(sum(A4:B4)=sum(D1:D2),sum(A4:B4),"Error")

The problem is that even though these two sums are equal I keep getting an error back! I've checked for formatting issues, but these are definitely numbers I'm trying to sum up. But since this anomaly is located in such a simple case, I don't really know what else to check for.

Interestingly enough, if you start messing with the values in the spreadsheet I attached you'll get varying results. If B7 drops below -$244.65 suddenly the error disappears! Or if you zero out cell C5 the error disappears. None of this makes logical sense to me. Obviously I don't need to set up my spreadsheet like this, I can do it a different way and thereby eliminate the problem, but I wanna know what in the world is going on here!! Any help would be much appreciated!


frustratingly yours,
-Mike

[JBeaucaire]

Click on E10 and then activate the Evaluate Formula icon on the Formula Auditing toolbar. Step through the formula one calc at a time and you'll see the problem occur.

So, I would change the E10 formula to:

=SUM(B10:C10) ...and not bother with the checking, the table is simple enough to just trust the basic math.


Excel does odd things sometimes with precision decimal values. It's not a bad idea to apply forced rounding to your calculations to push out these odd variances before they ever have a chance to take hold your data.

You could use: =IF(ROUND(SUM(B10:C10),2)=ROUND(SUM(E5:E9),2),SUM(B10:C10),"Error")

...but then what's the point, the simpler formula above is just as effective for this simple scenario.

[Fattyfatfat Kid]

Interesting, I never knew about that "Evaluate Formula" button, it's really useful! So now we know WHAT the problem is, but I'm still curious about WHY it's happening, and why it's happening in only this particular case.

Like I mentioned in my first post, I've been using this format for years in a much bigger spreadsheet and I've never had any problem with it before. And I don't really want to change it because this redundant double checking has saved me hours of work at times!

Any ideas?
-Mike

[pmalen]

Mike

Here is your answer. Because of the arithmetic of the computer processor, sometimes zero does not equal zero in excel. Look at the attached file - "WHY - Just Because". I added some formulas to show that your answers are not what they appear to be. 14 places past the decimal is your problem in the result. I modified your table with the ROUND formula and it works great. I found this out late one night about 20 years ago trying to balance an excel forecast model for a publically traded company. It had to be right! Don't know how or why I tried to extend the decimals out but found out the rounding issue. You can google it now and find it, but back then google was not around - just brute force attacking the issue. Hope this helps. I use ROUND all the time to prevent this.

Paul

[Fattyfatfat Kid]

That's pretty weird, but it explains everything! Thanks for your help JBeaucaire and pmalen!

In case anyone is reading this in the future and you're interested in learning more about what pmalen was talking about, here's a link that explains it in detail:
http://support.microsoft.com/kb/78113

Thanks again!
-Mike