"LCCHELP" wrote:
I have input the following if function
if(at1av1,at1-av1,0)
however if the number in av1 is greater than at1 it
returns a negative number, rather than returning 0.
can someone enlighten me as to what I have missed.

I wonder if your conditional expression is really AT1=AV1. Then I can
explain it by example.

Consider the case where A1 is 1 and A2 is =1+2^-52. Note that 2^-52 is a
very small fraction. You cannot see the difference within the 15
significant digits that Excel formats; nonetheless, A2 is indeed slightly
larger than A1.

Excel employs a number of heuristics to try to "spare" us from dealing with
such infinitesimal differences. But the heuristics are flawed, as
demonstrated by this example.

In this example A1=A2 results in TRUE (!) because Excel treats them as
equal in that expression. In fact, =A1-A2 results in exactly zero.

But the expression A1-A2 as a parameter to the IF() function returns the
true difference, -(2^-52) or about -2.22045E-16. (That's
about -0.00000000000000022204.) So does =(A1-A2), by the way.

Also note that A1-A2=0 results in FALSE, which is surprising only when you
compare that with the result of A1=A2.

As others have noted, MAX(0,A1-A2) works around these anomalies because no
matter what Excel does, the function cannot result in a negative number.
But the real reason to use MAX is that it is a better way to implement the
same logic.


----- original message -----

"LCCHELP" wrote in message
...
I have input the following if function

if(at1av1,at1-av1,0) however if the number in av1 is greater than at1 it
returns a negative number, rather than returning 0.

can someone enlighten me as to what I have missed.

thanks