Michael Scott once famously gave Jim 10 points, Dwight a gold star, and Stanley a thumbs up. If Michael had known how to use the SWITCH function, it would have been easy as pie to convert his employee's ratings to determine the winner of beach day.
Check out the gif above for an example of SWITCH in action. We’ve got a list of product ratings from 1-5, and we need to return whether that rating is considered poor, below average, average, above average, or great for each of our products. Rather than entering this manually, we can use the SWITCH function to swap a descriptive rating in for the numeric rating.
The SWITCH function has four core arguments that we’ll need to set to return our product rating:
- Expression Argument: the value we’re swapping out
- Value/Results Argument: the value we’re swapping in
Default Argument: allows us to set a default return value if the function can’t find the defined results argument
To swap our numeric ratings for our text descriptors, we’ll enter the SWITCH function, set our old 1-5 number as our expression argument, and set the adjective outcome possibilities as the value/results argument as follows[1,”Poor”,2,”Below Average”,3,”Average”,4,”Above Average”,5,”Great”]. This completes our function formula, so we’ll just enter the function and watch as Excel returns our product rating’s corresponding value! We can easily flash fill this down our column to complete this transformation for all products. Done!
