Create an Excel sheet with 50 employees, random hourly rates (12.00-82.50), and random hours worked (20-52). Calculate weekly pay, considering overtime.

In your Excel spreadsheet, enter the names of the fifty employees starting in cell B2.

In column C, starting from cell C2, generate a random hourly rate between $12.00 and $82.50. Since RANDBETWEEN only works with integers, you can use the following formula to get decimal values:

Then, copy this formula down for all employees.

The problem defines normal working hours as 8 am to 4 pm. Any hours worked outside this period are considered overtime.

In column D, starting from cell D2, generate the hours worked for each employee for the first week. The hours should be between 20 and 52.

Then, copy this formula down for all employees.

The first pay period ends on May 7th, 2025. You will need a column for the pay period (e.g., column E initially) and a column for the initial Total Pay (e.g., column F initially).

In column E (before any insertions), starting from cell E2, calculate the total weekly pay by multiplying the hourly rate (column C) by the hours worked (column D).

Then, copy this formula down for all employees.

You need to expand your spreadsheet to include separate fields for normal hours and overtime hours to calculate the exact total pay. The total pay will be the sum of normal pay and overtime pay.

Insert two new columns between the "Hours Worked" (column D) and the "Total Pay" (which is now column F) columns. Label the first new column "Normal Hours" (now column E) and the second new column "Normal Pay" (now column F).

In the "Normal Hours" column (now column E), starting from cell E2, use an IF function to determine the normal hours. Assume 40 hours per week is the normal working hours.

Then, copy this formula down for all employees.

In the "Normal Pay" column (now column F), starting from cell F2, calculate the normal pay by multiplying the hourly rate (column C) by the normal hours (column E).

Then, copy this formula down for all employees.

Insert two new columns between the "Normal Pay" (column F) and the "Total Pay" (which is now column I) columns. Label the first new column "Overtime Hours" (now column G) and the second new column "Overtime Pay" (now column H). In the "Overtime Hours" column (now column G), starting from cell G2, use an IF function to calculate the overtime hours.

Then, copy this formula down for all employees.

In the "Overtime Pay" column (now column H), starting from cell H2, calculate the overtime pay. The overtime rate is 1.5 times the normal hourly rate.

Then, copy this formula down for all employees.

In the final "Total Pay" column (now column I), starting from cell I2, calculate the employee's total pay by adding their normal pay (column F) and overtime pay (column H).

Then, copy this formula down for all employees.