Question

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

Answer 1

2) The required formula for column H2 (Total Pay) is =IF(E2="Sales",F2+(VLOOKUP(D2,$B$14:$C$23,2,0)*G2),F2). Copy it down till H5

ID Num	Last Name	First Name	completed Years of Service	Position	Salary	Sales	Total Pay
1111	Nimitz	Chester	0	Sales	32550		32550
2222	Patton	George	5	Service	38350		38350
3333	McArthur	Douglass	1	Sales	31550	102850	41835
4444	Sherman	William	9	Sales	52500	100000	72500
Total Payroll							185235
Total Commissions							30285
	Completed Years of service	Commission percentage
	0	0%
	1	10%
	2	15%
	3	20%
	4	20%
	5	20%
	6	20%
	7	20%
	8	20%
	9	20%

3) The required formula for column H10 is =SUM(H2:H5)

4) The required formula for column H11 is =H10-SUM(F2:F5)