The first sheet is called payroll and the second sheet is called data. I am trying to make it so that if the job level is 7-9 then they do not get paid overtime pay included in the gross pay. The equation for OT rate is 1.5 x Hourly Rate & Gross pay = HourlyRate*RegularHrs +HourlyRate* OTHours*1.5. What would be the correct formula?

Llarl Olle lile. 2 POIL) 9. Employees with Job Levels of 7 or greater are not paid overtime. Instead their overtime hours are recorded and then converted to time off (lieu days). You must make sure employees at Job Level 7 or greater are not paid overtime. (4 point deduction if not done) 10 All cell entries (labels and numbers must be visible No hidden letters no L M 3 E5 X V f =((VLOOKUP(B5,data!$B$5:$C$13,2)*C5)+((VLOOKUP(B5,data!$B$5:$C$13,2)*D5*1.5))) |_ A B C D E F G H I J K 1 2 For the month 2016 October XYZ Corporation Payroll 4 Employeeld Job Level Regular Hrs OT Hours Gross Income Tax CPP EI RRSP Medical Union 1000240 40 | $1,060.00! $481.19 $47.70 $19.08 $180.20 $9.00 $15.90 1000380 40 5 $783.75 $80.19 $35.27 $14.11 $133.24 $6.00 $11.76 1000406 40 $880.00 $320.77 $39.60 $15.84 $149.60 $8.00 $13.20 1000557 40 $996.00 $401.00 $44.82 $17.93 $169.32 $8.00 $14.94 1000629 40 $720.00 $160.38 $32.40 $12.96 $122.40 $6.00 $10.80 1000755 40 1 $830.00 $246.77 $37.35 $14.94 $141.10 $8.00 $12.45 1001156 $960.00 $561.38 $43.20 $17.28 $163.20 $12.00 $14.40 1001252 3 $1,068.00 $401.00 $48.06 $19.22 $181.56 $8.00 $16.02 1001292 $403.00 $0.00 $18.14 $7.25 $68.51 $6.00 $6.05 1001297 3 $801.00 $160.38 $36.05 $14.42 $136.17 $6.00 $12.02 1001307 $1,060.00 $481.19 $47.70 $19.08 $180.20 $9.00 $15.90 1001310 $800.00 $246.77 $36.00 $14.40 $136.00 $8.00 $12.00 1 vw #0 wa NV Life Ins $4.50 $3.00 $4.00 $4.00 $3.00 $4.00 $6.00 $4.00 $3.00 $3.00 $4.50 $4.00 Net $783.62 $580.38 $649.76 $736.99 $532.44 $612.16 $703.92 $791.14 $294.06 $593.35 $783.62 $589.60 A B C H I D E G Salary and Deduction information Life Job Level Hourly Rate Medical $15.50 $16.50 $18.00 $20.00 $22.00 $24.00 $26.50 $30.00 $40.00 $6.00 $6.00 $6.00 $8.00 $8.00 $8.00 $9.00 $12.00 $16.00 Insurance $3.00 $3.00 $3.00 $4.00 $4.00 $4.00 $4.50 $6.00 $8.00 Income Tax Deductions Weekly Annual Weekly Income Income Deduction $0.00 $0.00 $0.00 $200.00 $ 10,400.00 $ 40.10 $400.00 $ 20,800.00 $ 80.19 $ 600.00 $ 31,200.00 $ 123.38 $ 800.00 $ 41,600.00 $ 160.38 $ 1,000.00 $ 52,000.00 $ 200.50 $ 1,200.00 $ 62,400.00 $ 240.60 $ 1,400.00 $ 72,800.00 $ 280.69 $ 1,600.00 $ 83,200.00 $ 320.79 $ 1,800.00 $ 93,600.00 $ 457.29 8 docs payroll data + Llarl Olle lile. 2 POIL) 9. Employees with Job Levels of 7 or greater are not paid overtime. Instead their overtime hours are recorded and then converted to time off (lieu days). You must make sure employees at Job Level 7 or greater are not paid overtime. (4 point deduction if not done) 10 All cell entries (labels and numbers must be visible No hidden letters no L M 3 E5 X V f =((VLOOKUP(B5,data!$B$5:$C$13,2)*C5)+((VLOOKUP(B5,data!$B$5:$C$13,2)*D5*1.5))) |_ A B C D E F G H I J K 1 2 For the month 2016 October XYZ Corporation Payroll 4 Employeeld Job Level Regular Hrs OT Hours Gross Income Tax CPP EI RRSP Medical Union 1000240 40 | $1,060.00! $481.19 $47.70 $19.08 $180.20 $9.00 $15.90 1000380 40 5 $783.75 $80.19 $35.27 $14.11 $133.24 $6.00 $11.76 1000406 40 $880.00 $320.77 $39.60 $15.84 $149.60 $8.00 $13.20 1000557 40 $996.00 $401.00 $44.82 $17.93 $169.32 $8.00 $14.94 1000629 40 $720.00 $160.38 $32.40 $12.96 $122.40 $6.00 $10.80 1000755 40 1 $830.00 $246.77 $37.35 $14.94 $141.10 $8.00 $12.45 1001156 $960.00 $561.38 $43.20 $17.28 $163.20 $12.00 $14.40 1001252 3 $1,068.00 $401.00 $48.06 $19.22 $181.56 $8.00 $16.02 1001292 $403.00 $0.00 $18.14 $7.25 $68.51 $6.00 $6.05 1001297 3 $801.00 $160.38 $36.05 $14.42 $136.17 $6.00 $12.02 1001307 $1,060.00 $481.19 $47.70 $19.08 $180.20 $9.00 $15.90 1001310 $800.00 $246.77 $36.00 $14.40 $136.00 $8.00 $12.00 1 vw #0 wa NV Life Ins $4.50 $3.00 $4.00 $4.00 $3.00 $4.00 $6.00 $4.00 $3.00 $3.00 $4.50 $4.00 Net $783.62 $580.38 $649.76 $736.99 $532.44 $612.16 $703.92 $791.14 $294.06 $593.35 $783.62 $589.60 A B C H I D E G Salary and Deduction information Life Job Level Hourly Rate Medical $15.50 $16.50 $18.00 $20.00 $22.00 $24.00 $26.50 $30.00 $40.00 $6.00 $6.00 $6.00 $8.00 $8.00 $8.00 $9.00 $12.00 $16.00 Insurance $3.00 $3.00 $3.00 $4.00 $4.00 $4.00 $4.50 $6.00 $8.00 Income Tax Deductions Weekly Annual Weekly Income Income Deduction $0.00 $0.00 $0.00 $200.00 $ 10,400.00 $ 40.10 $400.00 $ 20,800.00 $ 80.19 $ 600.00 $ 31,200.00 $ 123.38 $ 800.00 $ 41,600.00 $ 160.38 $ 1,000.00 $ 52,000.00 $ 200.50 $ 1,200.00 $ 62,400.00 $ 240.60 $ 1,400.00 $ 72,800.00 $ 280.69 $ 1,600.00 $ 83,200.00 $ 320.79 $ 1,800.00 $ 93,600.00 $ 457.29 8 docs payroll data +