Tiered commission with IFS or nested IF

How it works, step by step

1. 01B2 holds the rep's quarterly sales, for example 64,000.
2. 02IFS tests B2>=100000 first. 64,000 fails, so it moves to the next pair.
3. 03B2>=50000 is true, so IFS returns 7% and stops checking. Order matters: the highest threshold has to come first.
4. 04TRUE,0 at the end is the catch-all. Sales under 20,000 reach it and get 0% instead of #N/A.
5. 05Multiply sales by the rate to get the payout: =B2*C2 gives 4,480 for this rep.

The same logic in four tools

=IFS(B2>=100000,10%,B2>=50000,7%,B2>=20000,5%,TRUE,0)

=IFS(B2>=100000,10%,B2>=50000,7%,B2>=20000,5%,TRUE,0)

SELECT rep,
       quarterly_sales,
       CASE
         WHEN quarterly_sales >= 100000 THEN 0.10
         WHEN quarterly_sales >= 50000 THEN 0.07
         WHEN quarterly_sales >= 20000 THEN 0.05
         ELSE 0
       END AS commission_rate
FROM reps;

import numpy as np
import pandas as pd

conditions = [
    reps["quarterly_sales"] >= 100000,
    reps["quarterly_sales"] >= 50000,
    reps["quarterly_sales"] >= 20000,
]
reps["commission_rate"] = np.select(conditions, [0.10, 0.07, 0.05], default=0)

Mistakes that break it

• !IFS needs Excel 2019 or later. Older Excel shows #NAME?, so use nested IF: =IF(B2>=100000,10%,IF(B2>=50000,7%,IF(B2>=20000,5%,0))).
• !Conditions are checked in order. Putting B2>=20000 first gives every rep above 20,000 the 5% rate.
• !Without the final TRUE,0 pair, IFS returns #N/A when no condition is true.
• !Hard-coded thresholds are easy to miss when the plan changes. With more than four tiers, a rate table with an approximate-match lookup is easier to maintain.

Common questions

How do I write a nested IF for commission tiers?

Check the highest tier first and nest each lower tier in the value_if_false argument: =IF(B2>=100000,10%,IF(B2>=50000,7%,IF(B2>=20000,5%,0))).

What is the difference between IF and IFS in Excel?

IFS takes condition and value pairs in one function, so there is no nesting. It has no built-in else, so you add TRUE and a default value as the last pair.

What is the SQL equivalent of IFS?

A CASE expression. Each WHEN line is one condition, they are checked from top to bottom, and ELSE supplies the default.