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.
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.
Practise IFS
Knowing what a function does and being able to write it from memory are different skills. These close the gap.