impossible zero value

  • Thread starter Thread starter sybmathics
  • Start date Start date
S

sybmathics

Hi,

I have e very strange experience in Excel 2002.

I have unchecked the zero values in the options.
In AA18 I have the following formula:
=B18+C18-D18+E18-F18+G18-H18+I18-J18+K18-L18+M18-N18+O18-P18+Q18-R18+S18-T18+U18-V18+W18-X18+Y18-Z18

All the subtractions in the formula lead to zero. B18 value is zero.

One would expect that the result of the formula also leads to zero.
However, the value in AA18 shows 0,00. (a zero value that should not be
shown.)
Now when I inspect the value more precise: using more decimals or using
scientific format it appears that there is a value slightly higher than
zero, namely: 2,8421709430404E-14 or 0,00000000000002842170943404
I am absolutely sure that the numbers in cells B18 : Z18 only have values
with 1 or 2 decimals.
Where does this strange value come from?

Anyone any ideas?

Sybolt
 
Yeah, but J.E. points to Chip on his web site, too. So I got it all covered!

(or should that be J.E. has it all covered???)
 
sybmathics said:
Hi,

I have e very strange experience in Excel 2002.

I have unchecked the zero values in the options.
In AA18 I have the following formula:
=B18+C18-D18+E18-F18+G18-H18+I18-J18+K18-L18+M18-N18+O18-P18+Q18-R18+S18-T18+U18-V18+W18-X18+Y18-Z18

All the subtractions in the formula lead to zero. B18 value is zero.

One would expect that the result of the formula also leads to zero.
However, the value in AA18 shows 0,00. (a zero value that should not be
shown.)
Now when I inspect the value more precise: using more decimals or using
scientific format it appears that there is a value slightly higher than
zero, namely: 2,8421709430404E-14 or 0,00000000000002842170943404
I am absolutely sure that the numbers in cells B18 : Z18 only have values
with 1 or 2 decimals.
Where does this strange value come from?

Anyone any ideas?

Sybolt
Click to expand...
A scientist would say the result isn't exactly zero. An engineer would
say the result is close enough to zero for all practical purposes. The
other posters have explained or pointed to an explanation that
specifically applies to Excel.

Bill
A retired engineer
 
Bill said:
A scientist would say the result isn't exactly zero. An engineer would
say the result is close enough to zero for all practical purposes. The
other posters have explained or pointed to an explanation that
specifically applies to Excel.

Bill
A retired engineer
Click to expand...

The point on Chip's page is that this applies not just Excel, it is
inherent in finite precision binary floating point math, and therefore
inherent in almost all software.

Jerry
 
Back
Top