When is 0 <> 0 ?

[spleve]

A: When you add it in excel!

Example: Place the following values in a column and sum them.

-116.33
98.53
17.80
0
=============
Excel returns: 0.0000000000000035527136788005

Correct Answer: 0

If you remove 0 from the list ( or put it in a different place in the list, the proper result, 0 is returned.

I assume this has to do with the known floating point issues, but I've never seen an example where 0<>0, so I thought I'd share.

I have a workaround, but would love to see comments...

 

[Greg Truby]

...The bad news is I have no idea how I will ever need this information. ...

Generally it goes something like this. A co-worker (good chance they'll be in the Finace Dept) will come to you and ask for your help trying to figure out why some reconcilation process is failing. There will be numbers coming in from two different directions, that at the end of the day should reconcile. There's some sort of a "test" either through conditional formatting or an IF() or a=b formula that is supposed to be passed before everything can be signed off. But the test is failing and your co-worker has spent hours going over every possible input and cannot see why the test is failing. Prior to knowing about FP errors you get sucked into the snipe hunt and you too spend hours doing this . But not now! Thanks to Mr. Excel here you know about FP errors and after checking that all of the input really are okay, you'll think to just pull the difference and you'll see that the difference is something like 1.2345E-14 and you'll remember all of this and will be the hero for solving the big mystery! Anyway, that's been my experience...

 

[Oorang]

That sounds very much like life as I know it