Microsoft Excel eases repetitive calculations by allowing you to create custom spreadsheets. As an example, you can keep track of hours worked and the pay received. However, you may run into difficulty when calculating hourly pay, particularly if you are paid extra for overtime, which is the amount of time over the normal 40-hour workweek. To solve this problem, Excel allows conditional calculations so that you can calculate the normal rate for up to 40 hours worked and then a higher amount for overtime hours worked.
-
1.
Open a new spreadsheet in Microsoft Excel.
-
2.
Enter the number of hours worked in cell A1. As an example, you might enter "48."
-
3.
Enter the normal hourly wage in cell A2. Continuing with the example, you might enter "$10" if you are paid $10 per hour up to 40 hours per week.
-
4.
Enter the overtime pay rate in cell A3. If you are paid time and a half for overtime, you might enter "=A2*1.5" to calculate the overtime pay rate.
-
5.
Enter "=IF(A1<40,A1A2,40A2)" in cell A4. This calculates the pay amount for hours up to 40 hours. If you worked 48 hours, then this only calculates 40 hours.
-
6.
Enter "=IF(A1-40>0,(A1-40)*A3,0)" in cell A5. This calculates the amount of overtime pay, if any. In the example, this would calculate 8 hours of overtime pay.
-
7.
Enter "=A5+A4" in cell A6 to add regular and overtime pay.
