ABS <= and logic functions

  • Thread starter Thread starter DrPhil
  • Start date Start date
D

DrPhil

I'm having trouble with this function

=IF(ABS(G30-F30)<=0.2,"PASS","FAIL")

If the numbers in G30 and F30 are bigger than 31 it returs false when equal
to 0.2.
If I replace the <= with an OR statement it will work for values up to 500.
A workaround is to change the criteria to < 0.200000000001 but its not really
the same thing. It like some wierd residual significant figures thing.
 
Hi Phil,

What Version of Excel? Do you have all the updates installed?

Also, are you sure the values in G30 and F30 really are exactly +/-0.2 apart? If they're calculated values, maybe they're not and
some rounding errors are occurring or the values need to be rounded off.

Functionally, your formula is equivalent to:
=IF(0.2>ABS(G30-F30),"PASS","FAIL")
and
=IF(ABS(G30-F30)>0.2,"FAIL","PASS")
See if either of those work any better.
 
Thanks for the reply. I'm running 2003 SP3. They are exactly +/- 0.2 eg
G30 F30
1.2 1.0 works
30 30.2 works
32.2 32 does not work

I tried the equivalent functions and they return the wrong value too. I
don't know how the ABS and <= work but they are rounding. I've tried to do
the sum in a different cell and then convert that cell to a value but it
isn't different.

macropod said:
Hi Phil,

What Version of Excel? Do you have all the updates installed?

Also, are you sure the values in G30 and F30 really are exactly +/-0.2 apart? If they're calculated values, maybe they're not and
some rounding errors are occurring or the values need to be rounded off.

Functionally, your formula is equivalent to:
=IF(0.2>ABS(G30-F30),"PASS","FAIL")
and
=IF(ABS(G30-F30)>0.2,"FAIL","PASS")
See if either of those work any better.

--
Cheers
macropod
[MVP - Microsoft Word]


DrPhil said:
I'm having trouble with this function

=IF(ABS(G30-F30)<=0.2,"PASS","FAIL")

If the numbers in G30 and F30 are bigger than 31 it returs false when equal
to 0.2.
If I replace the <= with an OR statement it will work for values up to 500.
A workaround is to change the criteria to < 0.200000000001 but its not really
the same thing. It like some wierd residual significant figures thing.
Click to expand...
Click to expand...
 
Computers work in binary. Most terminating decimal fractions (including .2)
have no exact binary representation and hence must be approximated (just as
1/3 can only be approximated as a decimal fraction).

In that context, a similar question would be why does
=IF(ABS(30.67-30)<=0.6667,"PASS","FAIL")
return "FAIL"? It is the same thing, just more obvious in this second
problem.

Comparisons should allow for some tolerance due to the unavoidable
approximatiosn to input numbers. Often this can be effectively handled by
simply rounding results before the comparison.

Jerry
 
DrPhil said:
I'm having trouble with this function
=IF(ABS(G30-F30)<=0.2,"PASS","FAIL")
Click to expand...

You should use:

=if(round(abs(G30-F30),1)<=0.2,"PASS","FAIL")


DrPhil said:
G30 F30
1.2 1.0 works
30 30.2 works
32.2 32 does not work
Click to expand...

Because most decimal fractions cannot be represented exactly in the binary
format that Excel uses internally. This sometimes causes small numerical
abberations in arithmetic results. In this case, you can see the problem if
you enter =ABS(G30-F30) in a cell formatted as Scientific with 14 decimal
places. In your last example, you will see that ABS() returns
2.00000000000003E-01, not 2.00000000000000E-01.
 
Thanks Jerry and Joe.

The rounding works. The terminating decimal fractions can be avoided by
this as well.

=IF(ABS(G30*10-F30*10)<=2,"PASS","FAIL")

It seems like the best fix for me is to have the criterion one significant
digit beyond the input.
 
Back
Top