From: Sam Whitfield, operations, Lakeview Distribution Center (a fictional company)
About: This week's timesheet
Payroll closes tonight. The hours for our ten warehouse staff are in the sheet, and the hourly rates by role are on the Rates sheet. Overtime is anything over 40 hours a week and pays time and a half.
I need each person's total, regular and overtime hours, their rate and their pay, then the total payroll, how many people worked overtime, and what share of the payroll was overtime.
Payroll is one of the most common jobs given to Excel, and it shows why breaking a calculation into steps pays off. Splitting each week into regular and overtime hours first makes the pay formula short and easy to check.
Two small patterns do the splitting: MIN(total, 40) caps a value, and MAX(total - 40, 0) floors one at zero. Together they always add back up to the total. The rate comes from a lookup on another sheet, with locked ranges so the formula fills down cleanly.