if function

[LCCHELP]

I have input the following if function

if(at1>av1,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

 

[JoeU2004]

I have input the following if function
if(at1>av1,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 -----

 

[JoeU2004]

I have input the following if function
if(at1>av1,at1-av1,0)

Your formula should work, assuming we are talking
about positive values in AT1 and AV1

Forgive me if I'm being dense, but what difference does it make whether we
are talking about positive or negative numbers or a mix?


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

 

[JoeU2004]

PS....

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, AT1>AV1 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 ----