Calculate Effective Hourly Pay Rate (Weighted Shift Pay vs Simple Mean)

Divide total earnings by total hours

For shifts of unequal length, the overall earnings rate is total earnings divided by total worked hours. The example has 2 hours and $60, 4 hours and $76, and 8 hours and $160: $296/14≈$21.142857 per hour.

The individual shift rates are $30, $19 and $20. Their unweighted mean is $23, which describes the average shift rate but not earnings per hour across all fourteen hours. The discrepancy can go in either direction; the simple mean does not always overstate the overall rate.

Define the money and time consistently

C2:C4 holds gross earnings allocated to those same shifts: wages, retained tips and allocated commissions, without counting an amount twice. Enter monetary totals rather than base hourly rates. Use a consistent currency and accounting period.

This is a descriptive gross-earnings measure, not take-home pay or gig-work profit after expenses. If a commission covers a different period, allocate its associated hours and earnings consistently before comparing rates. Overtime earnings already received can be included in this descriptive total, but the sheet does not calculate overtime owed.

=IF(E6<>"OK",E6,E10/E9)

Compare with an illustrative target

E2 is a user-chosen target, initially $18 per hour, not a statutory minimum-wage value. E12 calculates MAX(0,target×total hours−total pay). Initially the target amount is $252 and recorded earnings are $296, so the arithmetic gap is zero.

If Friday earnings C4 fall to $80, total earnings become $216 and the gap to that same target becomes $36. Alternatively, with the original $296 total and a target of $23.71, the gap is $35.94, not $36. The formula retains the raw value; format the output to cents for display.

=IF(E6<>"OK",E6,IF(E7<>"OK",E7,MAX(0,E2*E9-E10)))

Do not mistake the comparison for payroll compliance

A gross total that includes all customer tips is not automatically the FLSA regular rate. This descriptive earnings measure and its target comparison do not determine an exact statutory tip-credit top-up or wages owed.

US tip-credit and overtime calculations have additional requirements, including cash-wage obligations and which compensation counts. State or local rules may differ. Use the linked Department of Labor guidance for the distinction; this exercise does not implement those rules.

Handle empty and zero-hour rows explicitly

All three rows require numeric hours and pay. Use 0 hours and 0 pay for an unused row; its shift-rate helper returns blank and is omitted from the unweighted mean. Negative amounts and missing inputs are rejected in this gross-earnings example.

Positive pay with zero hours requests allocation to worked hours. An entirely zero-hour period has no defined hourly rate and displays a message rather than zero. A worked shift with zero pay is valid and contributes a zero rate.

Change one shift’s hours while keeping pay fixed and watch E11 update. An invalid target affects E12 only; E11 still describes the recorded earnings and hours.

Further reading

• Microsoft Support — SUM function
• US Department of Labor — Fact Sheet 15, Tipped Employees under the FLSA