I do not understand how to write the formulas for this problem. I do not need the exact answers (since I cannot provide the Excel file with the information) I just need to know how to complete this problem. Please answer completely and show exactly how I should type in the formulas, I really need help!

Use IF functions to calculate the regular pay and overtime pay based on a regular 40-hour workweek. Pay overtime only for overtime hours. Calculate the gross pay based on the regular and overtime pay. Mark's regular pay is $518. With eight overtime hours, Mark's overtime pay is $155.40.

Hint: This question will require 2 if statements:

Regular Pay: The first if statement will determine the amount of regular pay that the employee will receive. In its simplest form, this can be calculated by multiplying the number of Hours Worked by the Hourly Wage. However, you need a more advanced formula that will only apply the hourly wage to the first 40 hours. This is where the IF function will come in.

Your function will test whether the Hours Worked is greater than 40 (logical test). If it is, you will return the result of a formula multiplying just the 40 hours by the Hourly Wage (true value). If the Hours Worked is not greater than 40, you will multiply the Hours Worked by the Hourly Wage.

Remember to use absolute cell references where applicable. Any cells you refer to outside of row 5 where you find the information specific to the first employee will need an absolute cell reference. F4 is the keyboard shortcut to absolute reference a cell.

A correct formula will yield the result: $518

Overtime Pay: This sections IF function will calculate the overtime pay. If the employee has worked more than 40 hours, you will multiply only the hours over 40 by the hourly rate and then multiply by the Overtime Rate (Hours Worked-40)*Hourly Rate*Overtime Rate. If the employee did not earn overtime, you should return a zero.

Gross Pay: Do not forget to calculate the Gross Pay, which is the Total amount of pay the employee earned.

Create a formula to calculate the taxable pay. With 2 dependents, Mark's taxable pay is $573.40. Use the Deduction Amount found in B24.

Use the appropriate function to identify and calculate the federal withholding tax. With a taxable pay of $573.40, Mark's tax rate is 15%, and the withholding tax is $86.01.

Hint: This question will use a VLOOKUP. The formula will determine the employee Taxable Pay. It will then refer to the table in D21:E25 to determine the correct Tax Rate.

The VLOOKUP should refer to the Taxable Pay found in Column H (lookup value)

Use the table range as your Table Array. Remember to use an absolute reference to the table since you will copy the formula and do not want the table reference to change.

The column index number argument indicates which column of the table you want your result to come from. In this case, you want to pull your result from the second column of the table using a col_index_num of 2

You will multiply your result (15%) by the Taxable Pay. A correct answer will give the actual amount of tax that will be paid, or $86.01 for the first employee.

Calculate FICA based on gross pay and the FICA rate, and then calculate the net pay. The net pay can be calculated by subtracting the Federal Withholding Tax & FICA from the Gross Pay. Finally, calculate the Yearly Gross Pay based on the Gross Pay and number of weeks per year found in B25.

Copy all formulas down their respective columns.

Calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, net pay, and yearly gross pay. These calculations will go in Row 17.