Excel 2004 not processing IF correctly

[piersd]

I have a spreadsheet as follows

Col A (numeric, 3dp) value =0.996
Col B (numeric, 3dp) value = 1.001
Col C (Numeric, 3DP) formula = ABS( ColB - ColA)
Col D (General) formula = IF ( ColC >= 0.005, "Y","N")

Col D gives answer of "N", which is clearly wrong.

If you modify ColC to be = ROUND(ABS( ColB - ColA ) ,3)
it gives the correct answer whats up with that?

 

[JE McGimpsey]

I have a spreadsheet as follows

Col A (numeric, 3dp) value =0.996
Col B (numeric, 3dp) value = 1.001
Col C (Numeric, 3DP) formula = ABS( ColB - ColA)
Col D (General) formula = IF ( ColC >= 0.005, "Y","N")

Col D gives answer of "N", which is clearly wrong.

If you modify ColC to be = ROUND(ABS( ColB - ColA ) ,3)
it gives the correct answer whats up with that?

Welcome to IEEE Double Precision Floating Point math.

This behavior happens whenever one does calculations with numbers that
aren't exactly representable in a finite number of binary digits.

XL values have approximately 15 decimal digits of precision. So on
example of the problem in decimal is that while:

100 1/3 - 1/3 = 100 exactly

in 15 digits:

100.333333333333
- 0.333333333333333
====================
99.9999999999997

If you expand

=ABS(1.001 - 0.0996)

to 15 digits, you'll see the value returned is

0.00499999999999989

So when working with non-integers, using ROUND() is an appropriate
technique.

You can find more info here:

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

and

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

 

[CyberTaz]

Can't give you an explanation, but thought you might like to know that the
same thing happens in XL 2003 on a PC. That suggests that there is some sort
of logical reasoning behind it, but I have no idea what

 

[jpdphd]

It is not the IF, it is a rounding problem.
In another cell put =ColC - 0.005
the answer will be -1.06685E-16
close to 0, but ...

jpdphd

 

[Jan Putcuyps]

I successfully recreated the error.

You can work around it if you put in an extra column to split up the
ABS( ColB - ColA)

Create one Col D that does (ColB - ColA)
And then another separate column ABS (ColD)

It doesnt give any rounding errors anymore.

Hope this works for you.

Jan Putcuyps

 

[JE McGimpsey]

Unfortunately, while that may for the example values, it won't always
work if the values change.

The problem is inherent in the way XL (and every other spreadsheet)
calculate floating point numbers.