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

I don't see anything wrong with your formula except the missing = sign at the
beginning, and I'm sure you have that in your actual sheet.

Meanwhile, I would accomplish the same effect with this formula:

=MAX(AT1-AV1,0)

It will always subtract the second value from the first, but if the value is
less than zero, the MAX will cause the answer 0 to be returned.

Does that work better for you?
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


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

thanks

That formula works OK for me. Another way to write it would be:

=MAX(AT1-AV1,0)

--
Biff
Microsoft Excel MVP


"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

Your formula should work, assuming we are talking about positive values in
AT1 and AV1
Here is an alternative: =MAX(0,AT1-AV1)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"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

"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

"LCCHELP" wrote:
I have input the following if function
if(at1av1,at1-av1,0)

"Bernard Liengme" wrote:
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 -----

"Bernard Liengme" wrote in message
...
Your formula should work, assuming we are talking about positive values in
AT1 and AV1
Here is an alternative: =MAX(0,AT1-AV1)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"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

A1=3, A2=4, A2-A1 = 1, A2A1 = TRUE
A1=-3, A4=-4, A2-A1=-1, A2A1=FALSE
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"JoeU2004" wrote in message
...
"LCCHELP" wrote:
I have input the following if function
if(at1av1,at1-av1,0)

"Bernard Liengme" wrote:
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 -----

"Bernard Liengme" wrote in message
...
Your formula should work, assuming we are talking about positive values
in AT1 and AV1
Here is an alternative: =MAX(0,AT1-AV1)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"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

"Bernard Liengme" wrote:
A1=3, A2=4, A2-A1 = 1, A2A1 = TRUE
A1=-3, A4=-4, A2-A1=-1, A2A1=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(at1av1,at1-av1,0). Your implied expression is:
if(A2A1,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(A2A1,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(A2A1,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(A2A1,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(A2A1,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 -----

"Bernard Liengme" wrote in message
...
A1=3, A2=4, A2-A1 = 1, A2A1 = TRUE
A1=-3, A4=-4, A2-A1=-1, A2A1=FALSE
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"JoeU2004" wrote in message
...
"LCCHELP" wrote:
I have input the following if function
if(at1av1,at1-av1,0)

"Bernard Liengme" wrote:
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 -----

"Bernard Liengme" wrote in message
...
Your formula should work, assuming we are talking about positive values
in AT1 and AV1
Here is an alternative: =MAX(0,AT1-AV1)
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"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