## Explanation

Imagine a company that uses a tiered commission structure for its sales team. Each salesperson is assigned a commission rate based on the total sales they have made. The commission tiers are structured like this:

- For sales less than $10,000, the commission rate is 10%.
- For sales from $10,000 to $20,000, the commission rate is 15%.
- For sales from $20,000 to $30,000, the commission rate is 20%.
- For sales over $30,000, the commission rate is 25%.

The goal is to calculate a commission for each person using the tiered structure above.

*Note: the formulas below use decimal values like 0.1 for 10% but you can use percentages like 10%, 15%, etc. directly inside the formulas if you prefer, and Excel will correctly evaluate the percentage values.*

### IF function

One way to solve this problem is with the IF function as seen in the worksheet above where the formula in cell D5 is:

`=IF(C5<10000,0.1,IF(C5<=20000,0.15,IF(C5<=30000,0.2,0.25)))`

This kind of formula is referred to as a "nested if" because we have several IF functions nested inside each other. Here's how the formula works step by step:

`=IF(C5<10000,0.1,...)`

The first IF statement checks if the sales amount in C5 is less than $10,000. If so, it returns 0.1 as the commission rate. If not, it proceeds to the next IF statement.

`IF(C5<=20000,0.15,...)`

The second IF statement is nested inside the first. It checks if the sales amount is less than or equal to $20,000. If so, it returns 0.15 as the commission rate. If not, it moves on to the final IF statement:

`IF(C5<=30000,0.2,0.25)`

The third and final IF statement checks if the sales amount is less than or equal to $30,000. If so, it returns 0.2 as the commission rate. If not, it returns 0.25. Notice the last value is the value returned if *none of the previous conditions are met*, which only occurs if the sales amount is more than $30,000, the formula returns 0.25 as the commission rate.

*For more examples of nested if formulas, see How to use the IF function and 19 tips for nested if formulas.*

### IFS function

Nested IFs can become complicated and hard to read as more conditions are added because additional conditions result in additional IF statements. The IFS function offers a more streamlined way to manage multiple conditions in Excel because it eliminates the need to nest multiple IF statements together. As a result, the syntax is much simpler:

`=IFS(test1,result1,test2,result2,test2,result2,...)`

For example, to solve this same problem with the IFS function, you can use the formula below in cell D5:

`=IFS(C5<10000,0.1,C5<=20000,0.15,C5<=30000,0.2,C5>30000,0.25)`

For more details see: How to use the IFS function.

### Calculating commission

Both formulas above calculate the correct commission rate for each sales amount in column B, but they don't actually calculate the commission itself. This is done in a separate formula in cell E5:

`=C5*D5`

Alternatively, you can calculate the commission rate and the commission all in one formula like this:

`=IF(C5<10000,C5*0.1,IF(C5<=20000,C5*0.15,IF(C5<=30000,C5*0.2,C5*0.25)))`

This would save space on a crowded worksheet. Personally, I prefer to keep the calculations separate when possible because it makes the formulas simpler and easier to audit for errors.