HELP!!! Trouble with IF, AND functions

HI there:

I am trying to create a function in a spreadsheet to run a test on two columns which computes the result for the third column, I think I need an additional test though because when a negative number is involved I 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 the total in column B but if Column B is greater than column A my answer is the total in column A. However, if the answer is going to be a negative 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, the negative number is put in columm C. I want it to be 0. Can anyone tell me how I can do this. I have tried using an "AND" function to do this but then my answer always winds up being 0.

Your help is greatly appreciated.

hi

try this one!

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

-via135

Originally Posted by Ally1977

HI there:

I am trying to create a function in a spreadsheet to run a test on two columns which computes the result for the third column, I think I need an additional test though because when a negative number is involved I 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 the total in column B but if Column B is greater than column A my answer is the total in column A. However, if the answer is going to be a negative 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, the negative number is put in columm C. I want it to be 0. Can anyone tell me how I can do this. I have tried using an "AND" function to do this but then my answer always winds up being 0.

Your help is greatly appreciated.

THANK YOU SO MUCH!!!

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

via135" wrote:
> =IF(AND(B1<A1,A1>0),B1,IF(A1<0,0,A1))

"Ally1977" wrote apparently in response:
> 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))

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"Ally1977" <Ally1977.235jmy_1139807702.1871@excelforum-nospam.com> wrote in
message news:Ally1977.235jmy_1139807702.1871@excelforum-nospam.com...
>
> HI there:
>
> I am trying to create a function in a spreadsheet to run a test on two
> columns which computes the result for the third column, I think I need
> an additional test though because when a negative number is involved I
> 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 the
> total in column B but if Column B is greater than column A my answer is
> the total in column A. However, if the answer is going to be a negative
> 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, the
> negative number is put in columm C. I want it to be 0. Can anyone
> tell me how I can do this. I have tried using an "AND" function to do
> this but then my answer always winds up being 0.
>
> Your help is greatly appreciated.
>
>
> --
> Ally1977
> ------------------------------------------------------------------------
> Ally1977's Profile:
> http://www.excelforum.com/member.php...o&userid=31483
> View this thread: http://www.excelforum.com/showthread...hreadid=511605
>