1.

To begin, start Excel and open the file named Practice 3 in your Excel 2003 Practice folder.

You can use File->Open, or click the file open button on the standard tool bar.

In this worksheet we have 4 salespeople, each with a different total sales figure that they have achieved. We would like to calculate a bonus for each salesperson based on a percentage of their sales. For example, for sales under 5000 dollars the bonus is 2% of the sales. For sales over 5000 but less than 10, 0000 the bonus is 5% of the sales. For sales over 10,000 dollars it is 10% of the sales, and over 20, 0000 dollars it is 15% of sales.

2.

The best way to achieve this, is to construct a nested if function.

IF (logical test, value if true, value if false)

If we select the cell under the Janet sales columns in the bonus row (cell E18) we can enter the IF function in the formula bar. First, let us define the structure of our nested IF function.

IF(E16<5000,E18*0.02,

This part of the IF function means that, if the value in E16 (total sales) is less than 5000 dollars, the value displayed for a bonus will be the value in cell E16 multiplied .02. The next argument in the IF function should be a nested IF function with the next condition to test.

IF(E16<5000,E18*0.02, IF(E16<10000,E16*0.05,

Here we have implemented the second condition (sales less than 10000) for our bonus structure. We can continue building or IF function in a similar way for the rest of the bonus conditions.

IF(E16<5000, E16*.02,IF(E16<10000, E16*.05, IF( E16<20000, E16*.1, E16*.15)))

The last part of this IF function tests if the value in cell E16 is less than 20000. If it is, it will be multiplied by .1 to get the bonus. Else, if it is greater or equal to 20,000 it will be multiplied by .15 (15%).

3.

Select the appropriate cell in the bonus row, (cell E18) and enter the nested IF function into the formula bar.

=IF(E16<5000, E16*0.02, IF(E16<10000, E16*0.05, IF( E16<20000, E16*0.1, E16*0.15)))

Don’t forget the equals sign at the beginning.

You will now see that Janet’s bonus is 10% of her sales.

4.

Make the cell containing Janet’s bonus the active cell, and use your mouse to AutoFill the adjacent bonus cells for the other sales people (hold your left mouse button and drag with the thin cross pointer +).

Select the bonus cells and click the $ button on the formatting toolbar to make them currency style.

5.

You can now see that the bonus for each salesperson has been calculated based on the amount of sales they generated. You can easily modify the IF function to change the bonus structure if you wish.

6.

Save your work and exit Excel