IntermediateLogical Functions
Apply commission tiers with IFS
Return the right rate from several tiers without nesting.
IFS
10 min25 points
IFS
Checks several conditions in order and returns the first match.
Syntax
IFS(logical_test1, value1, [logical_test2, value2], ...)
Logical Functions. Available from Excel 2019.
Practise IFS2 interactive exercises, free, no account needed
Need the formula for your own sheet? Use the IF Formula Generator
A worked example
=IFS(B2>120000,0.06,B2>100000,0.05,B2>80000,0.04)
Returns 6%
Checks each tier in order and returns the first rate that matches.
Where it goes wrong
Conditions are checked top down and the first match wins, so ascending thresholds cap everyone at the lowest tier. It also has no built-in else — an unmatched value returns #N/A.
What the exercises catch
Each of these is a wrong turn the practice exercises recognise and name. They are the misconceptions, not the answers.
- Ascending order breaks IFS the same way it breaks nested IFs — 143000 matches the first test and gets 4%. Put the highest tier first.Apply commission tiers with IFS
- That gives you the prefix — HW — but not the category name. The prefix still needs translating into something readable.Turn a product code into a category
Practise IFS
Knowing what a function does and being able to write it from memory are different skills. These close the gap.