Instructions: 1. For cell D16 you need to use Vlookup it is based on the job...

 

Transcribed Image Text:

Instructions: 1. For cell D16 you need to use Vlookup it is based on the job title of each employee. (3 columns in the table below) 2. Naming a range: the yellow cells in the table below-cells B31:D36 should be named "Rate" 3. Use the range named Rate in your vlookup D16 and copy the vlookup till cell D23 4. Use an IF calculations for over 40 hours, time and half pay in E16. Copy your if down till cell E23 5. For column Fand G. You should refer to the cell F14 in the formula and G14 as well when you calculate the taxes. Use the fixed refrence-cell address ($F$1 for example) to refer to the 20% and the 7.65% 6. Finish all the calculation (hint: Gross-taxes=Net pay). Auto sum for cell e24 till H24 Lexington Hospital for Special Surgery Payroll Summary For the Week Ending November 16, 2020 FED INC SOC SEC EMPLOYEE HOURS HOURLY GROSS TAX WITH TAX WITH NET NAME JOB TITLE WORKED RATE PAY 20% 7.65% PAY Trainee Technician Level 2 Technician Level 1 Floor Assistant Trainee Technician Level 2 Barnes Chen Clifford 15 35 35 Gold 25 Mangano Murphy 15 50 Laboratory Specialist Technician Level 1 Rashad 40 Ruiz Totals 45 Employee Rate Schedule Hourly Rate Job Title 18.00 Floor Assistant Laboratory Specialist 38.50 22.00 31.00 Technician Level 1 Technician Level 2 Technician Level 3 40.00 Trainee 11.00 Instructions: 1. For cell D16 you need to use Vlookup it is based on the job title of each employee. (3 columns in the table below) 2. Naming a range: the yellow cells in the table below-cells B31:D36 should be named "Rate" 3. Use the range named Rate in your vlookup D16 and copy the vlookup till cell D23 4. Use an IF calculations for over 40 hours, time and half pay in E16. Copy your if down till cell E23 5. For column Fand G. You should refer to the cell F14 in the formula and G14 as well when you calculate the taxes. Use the fixed refrence-cell address ($F$1 for example) to refer to the 20% and the 7.65% 6. Finish all the calculation (hint: Gross-taxes=Net pay). Auto sum for cell e24 till H24 Lexington Hospital for Special Surgery Payroll Summary For the Week Ending November 16, 2020 FED INC SOC SEC EMPLOYEE HOURS HOURLY GROSS TAX WITH TAX WITH NET NAME JOB TITLE WORKED RATE PAY 20% 7.65% PAY Trainee Technician Level 2 Technician Level 1 Floor Assistant Trainee Technician Level 2 Barnes Chen Clifford 15 35 35 Gold 25 Mangano Murphy 15 50 Laboratory Specialist Technician Level 1 Rashad 40 Ruiz Totals 45 Employee Rate Schedule Hourly Rate Job Title 18.00 Floor Assistant Laboratory Specialist 38.50 22.00 31.00 Technician Level 1 Technician Level 2 Technician Level 3 40.00 Trainee 11.00

Answer rating: 100% (QA)

Answer The final result of above applications of step is 1 We use function F4 to fix cell reference ... View the full answer