Question

a) Calculate the Overtime Hours (Column F). The formula for Overtime Hours is based on each employee’s Total Hours Worked and is dependent on which department the employee works in. If the Total Hours Worked (column D) is greater than the assigned hours designated by the employee’s department (as listed in the table on rows 24 and 25), then the Overtime Hours is the Total Hours Worked minus the assigned hours. If the employee’ Total Hours Worked are less than or equal to the assigned hours as designated by the employee’s department, then the Overtime Hours is to be left blank (not zero(0)) in column F.

b) Develop the formula to calculate the amount of Overtime Pay. Use an “IF” statement to calculate the value of the Overtime Pay. If an employee has Overtime Hours then simply calculate the extra pay (Overtime Pay) for that employee. But, if they have no Overtime Hours, then “NA” (which stands for ‘not applicable’) is to be displayed in the cell. The employee’s Overtime Pay is based on their Hourly Rate (C8), their number of Overtime Hours (F8) and the Overtime Rate found on the RATES worksheet. Create the formula so that it can be copied down to the other employees that work at your company. Write the formula so it can be copied downward to every applicable cell in the column.

Bc G H I J K L M 6 Employee Name Department Hourly Rate Total Hours Worked Regular Hours Overtime Hours Regular Pay Overtime Pay Gross Pay FICA Provincial Tax Federal Tax Net Pay Total Deductions 12 32 8 32 37,5 45 40 Sales Marketing Production HR HR Marketing Sales Sales Production 11 7.2. 19.25 37.5 40 35 35 37.5 35 10 Leeves, T. Sophogus, E. 10 Hopes, 1 11 Pert, X. 12 Rail, D. 13 Efant, L. 14 Knutt, P. 15 Hind, B. 16 Aman, B. 17 Bowe, L. 18 Fertue, T. 19 Pendex, A 40 35 40 30 14 37.5 HR 40 8 17 12.45 10.75 30 37.5 35 35 35 35 Production Sales 36 TOTALS 22 23 24 DEPARTMENT 25 Assigned Hours HR Marketing Production 37.5 Sales 4035 26

Answer 1

Hello! based on your requiremet I did this formula.

Copy and paste this formula and then see the results.

=IF(AND(B17="Sales",D17>35),D17-35,IF(AND(B17="Marketing",D17>37.5),D17-37.5,IF(AND(B17="HR",D17>35),D17-35,IF(AND(D17="Product",D17>40),D17-40,"Zero"))))

you can replace 0 or something in place of Zero

Follow the sample output of this formula

Dr - General D x AutoSum" AY # % Cut EB Copy Format Painter Clipboard A A A Wrap Text Merge & Center, Calibri - 11 BIU - Font === === Paste $ % , 898 Insert Delete Format Conditional Format as Formatting Table Styles Cell Styles Clear Sort & Find & Filter - Select Editing Alignment Number Cells X fac =IF(AND(B8="Sales",D8>35),D8-35,IF(AND(B8="Marketing",D8>37.5),D8-37.5, IF(AND(B8="HR",D8>35),D8-35,IF(AND(D8="Product",D8>40),D8-40, "Zero")))) D E F G H I J M N O B C P Dept Hourly Rate Total Hours Worked Regular Hours Overtime hours Sales Marketing Production 32 37.5 12 8 11 7.2 19.25 Zero Zero Zero 5 Zero HR HR 35 6 Emp Name 7 8 emp 1 9 emp 2 10 emp3 emp 4 12 emp 5 13 emp 6 14 emp 7 15 emp 8 16 emp 9 17 emp 10 18 emp 11 emp 12 32 37.5 40 35 35 37.5 35 30 37.5 35 10 40 2.5 Marketing Sales Sales Production 7 14 30 37.5 5 Zero Zero HR 40 17 12.45 10.75 35 Production Sales Zero 24 Dept HR 25 Assigned hours 26 Marketing Product 35 37.5 Sales 40 35

Hit like if you are happy with this. Instead of hitting dislike, it's better to ask the requirements or doubts.

Please comment below if you have any queries regarding this. Feel free to ask anything about this problem

Have a great day.