HI the

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

hi

try this one!

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

-via135



Ally1977 Wrote:
HI the

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.


--
via135
------------------------------------------------------------------------
via135's Profile: http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=511605

THANK YOU SO MUCH!!!

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


--
Ally1977
------------------------------------------------------------------------
Ally1977's Profile: http://www.excelforum.com/member.php...o&userid=31483
View this thread: http://www.excelforum.com/showthread...hreadid=511605

Hi

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


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"Ally1977" wrote in
message ...

HI the

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

via135" wrote:
=IF(AND(B1<A1,A10),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))