HELP!!! Trouble with IF, AND functions

[Ally1977]

HI there:

I am trying to create a function in a spreadsheet to run a test on tw
columns which computes the result for the third column, I think I nee
an additional test though because when a negative number is involved
get the wrong result.

For example:

I am trying to test column A and B. Right now my formula is =IF(B<A
B, A). So that if column B is less than column A my result is th
total in column B but if Column B is greater than column A my answer i
the total in column A. However, if the answer is going to be a negativ
I want column C to say 0 not a negative number.

Column A Column B Column C
100 50 s/b 50
25 100 s/b 25
-100 10 s/b 0

At this point if my answer is negative using the above function, th
negative number is put in columm C. I want it to be 0. Can anyon
tell me how I can do this. I have tried using an "AND" function to d
this but then my answer always winds up being 0.

Your help is greatly appreciated

 

[via135]

hi

try this one!

=IF(AND(B1<A1,A1>0),B1,IF(A1<0,0,A1))

-via135

 

[Ally1977]

THANK YOU SO MUCH!!!

I have had a headache all night trying to do this. Your suggestio
worked perfectly. Thank you

 

[Arvi Laanemets]

Hi

=MAX(0,MIN(A1,B1))

 

[joeu2004]

=IF(AND(B1<A1,A1>0),B1,IF(A1<0,0,A1))

Your suggestion worked perfectly.

I am surprised to hear you say that. In "via135's" solution,
if B is negative and A is positive, the result will be negative
-- B.

But you wrote: "if the answer is going to be a negative I
want column C to say 0 not a negative number". I assume
that should be true even if B is negative, although you only
have an example with A negative.

Perhaps you never expect B<0. But the solution offered by
"arvi" still seems to be the best one, just in case, namely:

=max(0, min(a1,b1))

That is, choose the smaller of A or B, but choose 0 if the
smaller is negative. If you prefer an IF() function, then:

=if(or(A1<0,B1<0), 0, if(A1<B1, A1, B1))