How to use IFS for commission tiers and grade bands
Turning a number into a band, a commission tier, a grade, a traffic-light status, is a job for IFS. It checks your thresholds in order and stops at the first one that fits.
The formula
=IFS(B2>20000,"Tier 1",B2>10000,"Tier 2",TRUE,"Tier 3") in column C
What each part does
B2>20000, "Tier 1"
If B on this row is > 20000, the answer is "Tier 1".
B2>10000, "Tier 2"
If B on this row is > 10000, the answer is "Tier 2".
TRUE, "Tier 3"
What to show when none of the conditions above are met.
How it works, and what to watch for
Order is everything here. Excel takes the first test that passes, so the highest threshold has to come first. List them the other way round and everyone lands in the bottom band.
Once you are past three or four bands, a little lookup table is kinder to maintain than a long formula. You can change a threshold without going near the formula itself.
IFS wants Excel 2019 or later. The nested IF shown alongside does the same job on any version.
Questions
Why does everyone end up in the lowest band?
The bands are being tested from the bottom up. Excel stops at the first test that is true, so the strictest threshold has to come first.
Nested IF, IFS, or a lookup table?
IFS for a handful of bands and easy reading. A lookup table once there are many, so a threshold change is a one-cell edit rather than a formula rewrite.
Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.
Excel version
XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.
