When is 0 <> 0 ?

Oorang

Well-known Member

Joined

Mar 4, 2005

Messages

2,071

• Oct 27, 2005

• #2

That is bizarre

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

73,700

Office Version

1. 365

Platform

1. Windows

• Oct 27, 2005

• #3

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

 

Upvote 0

Oorang

Well-known Member

Joined

Mar 4, 2005

Messages

2,071

• Oct 27, 2005

• #4

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

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

73,700

Office Version

1. 365

Platform

1. Windows

• Oct 27, 2005

• #5

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

 

Upvote 0

Andrew Fergus

MrExcel MVP

Joined

Sep 9, 2004

Messages

5,462

Office Version

1. 365
2. 2021
3. 2016

Platform

1. Windows

• Nov 1, 2005

• #6

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

 

Upvote 0

S

spleve

Board Regular

Joined

Jun 3, 2003

Messages

106

• Nov 1, 2005

• #7

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

 

Upvote 0

S

SharonFinLV

New Member

Joined

Nov 1, 2005

Messages

2

• Nov 1, 2005

• #8

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

 

Upvote 0

Greg Truby

MrExcel MVP

Joined

Jun 19, 2002

Messages

10,030

• Nov 1, 2005

• #9

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

 

Upvote 0

Oorang

Well-known Member

Joined

Mar 4, 2005

Messages

2,071

• Nov 7, 2005

• #10

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

 

Upvote 0