Objective: Learn how to use the VLOOKUP function for payroll calculations.

a. Read the section titled “Data in Different Places” and create the spreadsheet illustrated in Exhibit 6. Print a screen shot of your work, and save your spreadsheet.

b. Create a formula that calculates total bonuses. Also create a cell entry that indicates what that number represents. Print a screen shot of your work, and save it.

c. Add the following data validation controls to your spreadsheet, including explanatory error messages. Save your work.

• Sales must be positive.

• Sales cannot exceed 125.

• Amount of bonus must be nonnegative.

• Amount of bonus cannot exceed 20% of unit sales.

d. Modify your worksheet by placing the sales data and resulting bonus on a different worksheet from the bonus table. Name your table array, and modify the VLOOKUP function accordingly. Then add another employee: Johnson, who sold 115 units. Print a screen shot of your new worksheet showing the bonuses for each employee, including Johnson. Save your work.