"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

PS....

I wrote:
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.

Perhaps a more likely explanation: AT1 is formatted as Text or contains
text, but the string looks like a number.

Example: AT1: =if(true,"15"); and AV1: 21. In that case, AT1AV1 results
in TRUE (!), and AT1-AV1 results in a negative number.

Again, MAX(0,AT1-AV1) works around this, to a degree. But if AT1 is
formatted as Text, not General, the formula =MAX(0,AT1-AV1) results in text
(left-aligned by default), and the cell format is changed to Text (!).

Although MAX(0,AT1-AV1) is the better solution anyway, the real correction
might be to change AT1 so that it contains a number, not text that looks
like a number, a common mistake.


----- original message ----

"JoeU2004" wrote in message
...
"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