5

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?

  • 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". – Rook Feb 3 '12 at 16:17
6

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))
6

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)

5

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)
2

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.

  • 2
    There is no SWITCH function in Excel. It is CHOOSE. – orad Aug 13 '14 at 21:15
  • The SWITCH function is only available in the Data Analysis Expressions language with Power Pivot. More information: office.microsoft.com/en-za/excel-help/… – Aaa Nov 21 '14 at 15:29
2

The shortest solution should be:

=IF(A1<0.5,10,IF(A1<=1,15,20))

Due to the first part the AND in the second part is unnecessary.

2

The Switch function is now available, in Excel 2016 / Office 365

SWITCH(expression, value1, result1, [default or value2, result2],…[default or value3, result3])

example:
=SWITCH(A1,0,"FALSE",-1,"TRUE","SORT OF")

Microsoft -Office Support

  • 2
    Could you possible edit your answer to include some references? – Burgi Aug 9 '16 at 14:56
1

For setting a value based on the input string i.e. Set cell value = 2 if C5="Likely" OR set cell value =3 if C5=Unlikely OR set cell value = 2 if C5="Quite Likely". then the formula will be as below:

IF(C5="Likely",1,(IF(C5="Unlikely",3,(IF(C5="Quite Likely",2)))))

  • if A < 0.5 ... B = 10. b) if A >= 0.5 ... B = 15. c) if A > 1.0 ... B = 20. – salesh Jun 4 '15 at 10:34
0

There is no switch function in Excel. For your question, nested ifs are the best solution.

I found this page looking for a true switch function in Excel. Although it doesn't exist, you can emulate this functionality with a lookup table and the VLOOKUP function. Here's an example...

Let's say we want to calculate the subtotal for each item in an order. First we create the lookup table, let's say this is in cells A1 through B4:

Product  Price
Candy    1
Apples   15
Chicken  21

Now let's say we have the receipt from the vendor in calls D1 through E5:

Product  Quantity
Candy          25
Apples         15
Chicken        10
-----------------
Tax           $37
Total        $497

We can add a subtotal column using this formula in cell F2:

=E2 * VLOOKUP(D2, $A$2:$B$4, 2, FALSE)

This will take the quantity and multiply it by the price of that product, producing the subtotal.

To explain that VLOOKUP function a bit further, we're looking up the name of the product (D2) in our lookup table of prices ($A$2:$B$4), then taking the second column (2) and we only want exact matches (FALSE).

  • I came across this old question and was going to suggest VLOOKUP until I saw your answer. It's on the right track, but it doesn't really show how to implement a value-based lookup as asked in the question. Your answer would be much better if you tweak it to better match the problem. – fixer1234 Aug 9 '16 at 17:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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