IF with 3 possible solutions

[jomili]

Okay, I'm having trouble with this one. Here's what I want to do:

column A is my projection, column B is my allocation. I can't exceed my allocation, and I can't have a negative value.

In column C I want to put my value:
If A1 is greater than B1, I want to put B1.
If A1 is less than zero, I want to put 0.
If A1 is less than B1 but is equal or greater than 0 I want to put A1.

I tried to do it with an IF formula, but couldn't figure out how to get it with 3 possible results. Can anyone advise me?

Thanks,
John

[contaminated]

Maybe this...

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

[jomili]

Thanks Contaminated,

You pointed me in the right direction. I used your formula, but whittled it down a little bit. This one seems to do the trick:

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

If tested it with negatives and with amounts equal to or greater than B1, and it seems to be working correctly.

Thanks for your help.
John

[contaminated]

I suspect I could do it, but I was afraid of being another conditions where FALSE could be. Hope you understood.

[jomili]

ooh, goog point! I didn't think about what would happen to the formula if a FALSE or #N/A shows up. I'll test that in the morning. Thank you!

[romperstomper]

=median(a1,b1,0)

[jomili]

Alright! Elegant, simple, easy to remember. I like that formula!