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...

 

[Oorang]

That is bizarre

 

[Joe4]

Yeah, that floating point issues raises its ugly head in Access a lot too. I often use the ROUND function to avoid the problem (I round all my answers to 2 decimal places).

 

[Oorang]

I was unaware of floating point issues, could someone bring me up to speed?

 

[Joe4]

Check out these two articles from the Microsoft Knowledge Base:

http://support.microsoft.com/default.aspx?scid=kb;en-us;78113
http://support.microsoft.com/default.aspx?scid=kb;en-us;214118

 

[Andrew Fergus]

I believe it is a hardware issue, not an Excel or Access issue. I too frequently see this in Access where calculations that should equal 0 do not. There is some history on this here on Wikipedia. Interestingly enough, if you rearrange the numbers from the OP such that the 0 is not the last number in the list, then the sum = 0. What is also interesting (IMO) is the history of zero, without which we wouldn't have computers.
My 2c
Andrew

 

[spleve]

More discussion of 0<>0

Now don't be jealous, but I posted the question on the newsgroups as well. I think Jerry W. Lewis' answer helped me understand their are two issues involved.

After all, it didn't make sense to me that converting 0 to binary would cause a problem..

http://groups.google.com/group/micr...480c7d4b1a8/4f1ff6d78dd5039e#4f1ff6d78dd5039e

 

[SharonFinLV]

0<>0 occurs in other programs too

As well as Excel and Access, we've also seen these "mysterious" fractional values occur with data in our Paradox 3.5 (no, that's not a typo) from time to time. The rounding and delta comparing are ways we get around this issue too, but it would be a whole lot easier if we didn't need to do that, of course.

Compound that issue with receiving data from multiple sources that deals with rounding fractional cents at different points in the process, and welcome to my nightmare!

Great discussion topic... if it helps just one person realize they are not going insane, then our efforts are worth it.

-SharonFinLV

 

[Greg Truby]

• While fp errors were not new to me, I had no idea that input sequence or parenthesis use could impact the output. A very interesting find.
• I must add that I'm impressed that spleve managed to get both Chip and Harlan to respond on the same thread.

 

[Oorang]

Well the good news is I feel very informed now.

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

But hey, you never know