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.

  • CreatedDecember 19, 2014
  • Files Included
Post your question