IF with 3 possible solutions

**jomili** · 09-13-2010, 04:43 PM

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** · 09-13-2010, 04:48 PM

Maybe this...

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

**jomili** · 09-13-2010, 05:02 PM

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:

Please Login or Register  to view this content.

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** · 09-13-2010, 05:04 PM

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

**jomili** · 09-13-2010, 05:07 PM

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** · 09-13-2010, 05:39 PM

=median(a1,b1,0)

**jomili** · 09-14-2010, 08:41 AM

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

IF with 3 possible solutions

LinkBack

Thread Tools

Rate This Thread

Display

IF with 3 possible solutions

Re: IF with 3 possible solutions

Re: IF with 3 possible solutions

Re: IF with 3 possible solutions

Re: IF with 3 possible solutions

Re: IF with 3 possible solutions

Re: IF with 3 possible solutions

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions