MS Excel: Multiple IF Range Statements in One Cell

I have dug and dug but cannot find an answer that works.

I need an IF statement that includes multiple ranges and different results.
For example

IF B31 is <0 but >=100, THEN C31 equal 15, BUT IF B31 is <100 but >=200, THEN C31 equals 50, BUT IF B31 is <200 but >=400, THEN C31 equals 75.

Etc...

So, how the heck do you structure something like that in Excel?

 

Log in or Sign up to hide all adverts.

=IF(AND(B31>0,B31<=100),15,IF(100<B31<=200,50,IF(200<B31<=400,75)))

Some of your logic seemed wrong ,like as if you had the < or > signs around the wrong way around. Can you have "IF B31 is <200 but >=400"? Something <200 but also greater than 400? That is why i have changed the signs around.

I liked the style "IF(200<B31<=400,75)" rather than "IF(AND(B31>0,B31<=100),15)" but they both might work ok.

 

Log in or Sign up to hide all adverts.

Place a formula in the target cell according to this general format:

=If((Condition to be tested),(Result if true "Then"),(Result if false "Else"))

These statements can be nested.


I can't parse your example though because I can't understand your syntax.

IF B31 is <0 but >=100, THEN C31 equal 15, BUT IF B31 is <100 but >=200, THEN C31 equals 50, BUT IF B31 is <200 but >=400, THEN C31 equals 75.

For instance, what does "If B31 < 0 but >=100" mean? How can B31 simultaneously be < 0 AND >=100? Same for "IF B31 is <100 but >=200" and "IF B31 is <200 but >=400".

If you meant "If B31 < 0 OR >= 100" then three of your conditions could be true, i.e. if B31 = 500 then B31 is >= 400 and >= 200 and >= 100. What do you want as an answer in this case? Please rephrase using "IF, THEN, ELSE, AND" syntax.


Perhaps you mean this: IF (B31 >= 400 THEN C31 = 75) ELSE IF (B31 >= 200 AND B31 < 400 THEN C31 = 75) ELSE IF (B31 >=100 AND B31 < 200 THEN C31 = 50)

If that's the case what do you want the value to be for (B31 >= 0 AND B31 < 100)? What about (B31 < 0)?

If you can give clarification I should be able to help further.

 

Log in or Sign up to hide all adverts.

My reply was queued for Moderators but I had the same question as RobbityRob - your syntax is very hard to follow and seems inconsistent. Please clarify.

 

Thanks Robittybob1. My brain is a little bit fried, hence the reversed signs. You're correct. Thanks again!