Question

1 Instructions: 2 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) 3 2. Naming a range: the yellow cells in the table below-cells B31:036 should be named "Rate" 4 3. Use the range named Rate in your vlookup D16 and copy the vlookup till cell D23 5 4. Use an IF calculations for over 40 hours, time and half pay in E16. Copy your if down till cell E23 6 5. For column F and 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% 7 6. Finish all the calculation (hint: Gross-taxes=Net pay). Auto sum for cell e24 till H24

Please help with all formulas, thank you in advance!

Answer 1

A B с F G Н. | J 10 D E Payroll Summary For The Week Ending November 16,2020 12 13 Employee Name Job Title 14 Net Pay 15 16 Barnes 17 Chen 18 Cliford 19 Gold 20 Mangano 21 Murphy 22 Rashed 23 Ruiz 24 Total 210.6225 1385.0025 982.905 574.425 Trainee Technician Level 2 Technician Level 1 Floor Assistant Trainee Technician Level 2 Laboratory Specialist Technician Level 1 Hours Worked Hourly Rate Gross Pay Fed Inc Tax With Soc Sec Tax With 0.2 0.0765 0.2 0.0765 15 11 165 33 12.6225 35 31 1085 217 83.0025 35 22 770 154 58.905 25 18 450 90 34.425 15 11 165 33 12.6225 50 31 1550 310 118.575 40 38.5 1540 308 117.81 45 22 990 198 75.735 210.6225 1978.575 1965.81 1263.735

G39 X for A B с D E 26 27 Emplyoee Rate Schedule 28 29 Job Title Hourly Rate 30 31 32 33 18 38.5 22 Floor Assistant Laboratory Specialist Technician Level 1 Technician Level 2 Technician Level 3 Trainee 34 31 35 40 36 11 37 38 39 40

Hourly Rate using Vlookup

D16 X for =VLOOKUP(B16,$B$27:$D$36,3,FALSE) H 1 Net Pay A B 13 Employee Name Job Title 14 15 16 Barnes Trainee 17 Chen Technician Level 2 18 Cliford Technician Level 1 19 Gold Floor Assistant 20 Mangano Trainee 21 Murphy Technician Level 2 22 Rashed Laboratory Specialist 23 Ruiz Technician Level 1 24 Total 25 с D E F G Hours Worked Hourly Rate Gross Pay Fed Inc Tax With Soc Sec Tax With 0.2 0.0765 0.2 0.0765 15 11 165 33 12.6225 35 31 1085 217 83.0025 35 22 770 154 58.905 25 18 450 90 34.425 15 11 165 33 12.6225 50 31 1550 310 118.575 40 38.5 1540 308 117.81 45 22 990 198 75.735 210.6225 1385.0025 982.905 574.425 210.6225 1978.575 1965.81 1263.735

Gross Pay

E16 fx =IF(D16>40,016*C16+(016-40) *D16)/2,016*C16) A B C D E F G H 12 13 Employee Name Job Title 14 Hours Worked Hourly Rate Gross Pay Net Pay Fed Inc Tax With 0.2 Soc Sec Tax with 0.0765 0.2 15 165 33 15 16 Barnes 17 Chen 18 Cliford 11 31 0.0765 12.6225 83.0025 35 1085 217 210.6225 1385.0025 982.905 574.425 35 22 770 154 58.905 19 Gold 25 18 90 34.425 Trainee Technician Level 2 Technician Level 1 Floor Assistant Trainee Technician Level 2 Laboratory Specialist Technician Level 1 450 165 15 11 33 310 210.6225 1978.575 50 31 20 Mangano 21 Murphy 22 Rashed 23 Ruiz 24 Total 1550 1540 12.6225 118.575 117.81 75.735 40 38.5 308 1965.81 1263.735 45 22 990 198 25

Fed ic with tax

F16 f =$F$15*E16 А B с D E F G H J 12 Net Pay 13 Employee Name Job Title 14 15 210.6225 1385.0025 982.905 16 Barnes 17 Chen 18 Cliford 19 Gold 20 Mangano 21 Murphy 22 Rashed 23 Ruiz 24 Total 25 Trainee Technician Level 2 Technician Level 1 Floor Assistant Trainee Technician Level 2 Laboratory Specialist Technician Level 1 Hours Worked Hourly Rate Gross Pay Fed Inc Tax With Soc Sec Tax With 0.2 0.0765 0.2 0.0765 15 11 1651 33 12.6225 35 31 1085 217 83.0025 35 22 770 154 58.905 25 18 450 90 34.425 15 11 165 33 12.6225 50 31 1550 310 118.575 40 38.5 1540 308 117.81 45 22 990 198 75.735 574.425 210.6225 1978.575 1965.81 1263.735

Soc Sec with tax

G16 X =E16*$G$15 A B с D E F H 12 13 Employee Name Job Title 14 Net Pay 15 210.6225 16 Barnes 17 Chen 18 Cliford 19 Gold 20 Mangano 21 Murphy 22 Rashed 1385.0025 982.905 Hours Worked Hourly Rate Gross Pay Fed Inc Tax With Soc Sec Tax with 0.2 0.0765 0.2 0.0765 15 11 165 33 12.6225 35 31 1085 217 83.0025 35 22 770 154 58.905 25 18 450 90 34.425 15 11 165 33 12.6225 50 31 1550 310 118.575 40 38.5 1540 308 117.81 45 22 990 198 75.735 Trainee Technician Level 2 Technician Level 1 Floor Assistant Trainee Technician Level 2 Laboratory Specialist Technician Level 1 574.425 210.6225 1978.575 1965.81 1263.735 23 Ruiz 24 Total P + 25

Net Pay

H16 x fx =SUM(E16:16) A B с D E F G H 1 12 Hours Worked Hourly Rate Gross Pay Net Pay 13 Employee Name Job Title 14 Fed Inc Tax With 0.2 Soc Sec Tax With 0.0765 15 0.2 0.0765 16 Barnes 15 11 165 33 12.6225 210.6225 1385.0025 35 31 1085 217 83.0025 35 22 770 154 982.905 25 18 450 90 Trainee Technician Level 2 Technician Level 1 Floor Assistant Trainee Technician Level 2 Laboratory Specialist Technician Level 1 15 11 17 Chen 18 Cliford 19 Gold 20 Mangano 21 Murphy 22 Rashed 23 Ruiz 24 Total 25 33 58.905 34.425 12.6225 118.575 117.81 75.735 50 165 1550 1540 990 31 38.5 574.425 210.6225 1978.575 1965.81 1263.735 310 308 198 40 45 22 But

I used the given data in the same cells*