impossible zero value

[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

 

[Dave Peterson]

J.E. McGimpsey explains it all at:
http://mcgimpsey.com/excel/pennyoff.html

 

[JE McGimpsey]

It comes from the inherent small rounding errors involved with IEEE
double precision floating point math. See

http://cpearson.com/excel/rounding.htm

 

[Sandy Mann]

J.E. McGimpsey explains it all at:

http://mcgimpsey.com/excel/pennyoff.html

It comes from the inherent small rounding errors involved with IEEE double
precision floating point math. See

http://cpearson.com/excel/rounding.htm

Dave points to John and John Points to Chip - I can't wait to see who Chip
is going to point to <g>


--
HTH

Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[Dave Peterson]

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

 

[Bill Sharpe]

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

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

 

[Jerry W. Lewis]

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

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