Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In short, if one wanted to make an IF switch

a) if A < 0.5 ... B = 10.
b) if A >= 0.5 ... B = 15.
c) if A > 1.0 ... B = 20.

How would one go about that?

share|improve this question
    
This is a simple basic excel question, but i think you may not looking for straight forward answer that is in terms of you using nested if. Is that right? –  Siva Charan Feb 3 '12 at 12:30
    
@SivaCharan - Yeah, I'm generally looking for an alternative to nested IF. Cause when you have a bit more complicated switch case, or a more then three choices, it gets very "ugly". –  ldigas Feb 3 '12 at 16:17
add comment

4 Answers

up vote 2 down vote accepted

This will work as a SWITCH function:-

=CHOOSE(IF(A1<0.5,1,IF(AND(A1>=0.5,A1<=1),2,3)),10,15,20)

But this is internally doing Nested IF.

Best Way to use 3 way switch is as follows:-

=IF(A1<0.5,10,IF(AND(A1>=0.5,A1<=1),15,20))
share|improve this answer
add comment

If is the easiest way to do this.

A more flexible method to calculate is to exploit Excel's use of True=1. Assuming the value is in A1:

=((A1<0.5)*10)+((A1>=0.5)*15)+((A1>1)*20)

This will allow you to add any number of conditions and will get you out of the nested IFs.

This works because in each grouping the evaluation is a logic function returning True or False, ie (A1<0.5) will return True if A1 is less than 0.5. Excel uses the value of 1 when True is used in an arithmetical function.

The above example I've put in isn't exactly correct, some numbers will generate True for two different groups. The middle group would need an AND() function to limit like:

AND(A1>=0.5,A1<=1)
share|improve this answer
add comment

Have a look here if this link helps

A typical switch function would look like, =switch(condition, outcome1, outcome2, outcome3...), for eg, =switch(3,"good","average","poor") would return “poor” when used.

share|improve this answer
add comment

You can nest "if" expressions like this:

=IF(A1<0.5,10,IF(AND(A1>=0.5,A1<=1),15,IF(A1>1,20,"Something else")))

(I added A1<=1 otherwise because A>1.0 implies A also >= 0.5)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.