2. The employees in sales positions are paid a commission on top of their base salaries, beginning in their second year. Add a column to the spreadsheet to calculate the total pay for each employee. For employees in service positions, this is just their salary. For sales people, this means adding the commission to their base salary. The commission is 0 for first year sales people, 10% in the second year, 15% in the third, and 20% in the fourth or more. A table with the completed years of service and commission percentages has been created for you in cells B14 to C17 of the spreadsheet. Create a formula in cell H5 to calculate the total pay for each employee. Use vlookup in your solution. In order to work for both sales and service employees, this formula will require both an IF and a VLOOKUP. Write one formula that will work for all employees and copy it down the column, using the fill handle.

3. Write a formula in cell H10 to calculate the total company payroll for last year.

4. Write a formula in cell H11 to calculate the total commissions paid last year. This can be done in one formula without adding any additional data to the spreadsheet.

3 ID Num 4 Last Name First Name Years of Servic e Position Salary Sales Total Pay 1111 Nimitz Chester 2222 Patton George 3333 McArthur Douglass 4444 Sherman William 0 sales 5 service 1 sales 9 sales 32550 38350 31550 102850 52500 100000 10 Total Payroll 11 Total Commissions 12 Completed Years of Commission Service Percentage 14 15 16 0% 10% 15% 20% 0