Question

The bonus rates for each salesperson are determined by sales amounts using the following scale: Sales greater than $35,000 earn a bonus rate of 5%, sales greater than $25,000 earn a bonus rate of 4%. All other sales (any amount greater than 0) earn a bonus rate of 2%. With the sheets still grouped, in cell C5 use an IFS function to determine the commission rate for the first salesperson whose sales are in cell B5. Fill the formula down through cell C8.

Answer 1

Assuming that cell B5 contains the sales amount for the first salesperson, the IFS function to determine the commission rate in cell C5 is:

=IFS(B5>35000, 0.05, B5>25000, 0.04, B5>0, 0.02)

This formula checks the sales amount in B5 against each threshold amount in descending order (starting with $35,000), and returns the corresponding bonus rate if the sales amount is greater than that threshold. If the sales amount is less than or equal to zero, it returns a bonus rate of 0%.

To fill the formula down through cell C8, select cell C5 and drag the fill handle down to cell C8. This will copy the formula to the other cells in the group, adjusting the cell references as needed.