This article shows how to use the Excel function SWITCH and how to solve complex logic with only one formula.
Preface
I decided to write this article after reading a job description on Upwork. An employer offered this task:
- We have payment rates for views:
| Views | Payment Amount ($) |
| 500 000 – 1 000 000 | 0.00064 |
| 1 000 000 – 2 000 000 | 0.00048 |
| 2 000 000 – 4 000 000 | 0.000336 |
| 4 000 000 – 8 000 000 | 0.000218 |
| 8 000 000 + | 0.000120 |
- We have a number of views in the cell A1.
- Write only one formula in the cell B1 to calculate the amount of payment for the views in A1.
And he offered good compensation to those who first solved this task, who first sent him the formula. :).
I resolved this task, but meanwhile the employer canceled the job – maybe because it was against the Upwork rules.
Solution
You can solve this task using standard IF function, but in this case we’ll get tremendous formula which is hard to maintain.
So, I decided to use the function SWITCH.
You can not use the logical operators inside the SWITCH function. It performs only an exact match. For example, if you have a fruit type ID in cell B1 and you know that fruit IDs are the following:
| ID | Fruit |
| 1 | Apple |
| 2 | Banana |
| 3 | Pear |
| 4 | Peach |
And you want to write a fruit name in cell B1, if you have a fruit ID in cell A1.
Then you can write in cell B1:
=SWITCH(A1,1, "apple", 2, "Banana", 3, "Pear", 4, "Peach", "No Fruit")Take note of the last string in the formula “No Fruit”. It is without a paired ID, that because it’s the default value. If you write 7 (or any other number not listed in the formula) in A1, then you get “No Fruit” in B1.
But you can not write:
=SWITCH(A1, A1<1, "Error",1, "apple", 2, "Banana", 3, "Pear", 4, "Peach", "No Fruit")To implement logic inside the SWITCH function, you can use a Boolean value instead of the switcher. In the case of the abovementioned task:
=SWITCH(TRUE,A1<5*10^5,0,AND(A1>=5*10^5, A1<10^6),0.00064,AND(A1>=10^6, A1<2*10^6),0.00048,AND(A1>=2*10^6, A1<4*10^6),0.000336,AND(A1>=4*10^6, A1<8*10^6),0.000218,A1>=8*10^6,0.00012)*A1As you see, the formula is pretty compact, and you can construct a useful table like this:
Was this helpful?
2 / 0