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 ----

 

[Bernard Liengme]

A1=3, A2=4, A2-A1 = 1, A2>A1 = TRUE
A1=-3, A4=-4, A2-A1=-1, A2>A1=FALSE
best wishes

 

[JoeU2004]

A1=3, A2=4, A2-A1 = 1, A2>A1 = TRUE
A1=-3, A4=-4, A2-A1=-1, A2>A1=FALSE

I know that; but I do not understand what point you are trying to make with
that information.

The OP's expression is: if(at1>av1,at1-av1,0). Your implied expression is:
if(A2>A1,A2-A1,0). So AT1 and AV1 correspond to your A2 and A1
respectively.

The OP wrote [in your terms]: "if the number in av1 [A1] is greater than
at1 [A2] it returns a negative number, rather than returning 0".

If A1=-3 and A2=-4, AV1 [A1] is indeed greater than AT1 [A2]. But
if(A2>A1,A2-A1,0) correctly results in 0, not the __negative__ number that
the OP complained about.

Perhaps you are assuming that the OP thinks (or wants) -4 > -3 since 4 >
3 -- in other words, considering only the magnitude of numbers, not their
signs. But in that case, since if(A2>A1,A2-A1,0) results in 0, I would
expect the OP to complain about getting __0__.

Moreover, if that were the OP's thinking, the example, in your terms, would
be A1=-4 and A2=-3 so that "the number in av1 [A1] is greater than at1 [A2]"
(in magnitude). But in that case, since AT1 [A2] is actually greater and
if(A2>A1,A2-A1,0) correctly results in 1, I would expect the OP to complain
about __not__ getting 0 -- or perhaps about getting a __positive__ number.

I believe if(A2>A1,A2-A1,0) always has the correct non-negative result
regardless of the signs of A1 and A2, except for the anomalies that I noted
in my two postings, namely: (a) a corner-case with precision (inconsistent
Excel heuristics); and (b) numeric-like text instead of actual numeric
values (inconsistent Excel treatment in relational and arithmetic
expressions).

If the OP were expecting a negative result when AV1 [A1] and/or AT1 [A2] is
negative, I might understand a point you could make, namely: the IF()
expression will not return negative results normally. (And of course, we
could suggest changes to correct that.)

But that is not what the OP wrote. On that other hand, you might invoke the
Greg House Law and presume that the OP misspoke ;-).

If I missed your point or misread or misinterpreted something, please
explain further. Your feedback would be appreciated in any case. Thanks.


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