Tip Distribution to Assistants

Excel Formulas › Salon, Spa & Beauty

All versionsROUND

When tips are shared with assistants or shampoo techs, split a pooled tip by hours worked (or points) — and make the rounded shares tie back exactly to the pool with a last-person plug.

Quick formula: each person’s share of the tip pool:
=ROUND(pool * hours / SUM(hours), 2)
Pro-rate the pool by each person's hours, rounded to cents; the last person absorbs the rounding remainder.

Functions used (tap for the full reference guide):

ROUNDSUMPRODUCT

The example

$120 pool split by hours.

AB
1ItemValue
2Shares
3Sum ties to→ $120.00

The formula

The formula:

=ROUND(pool * hours / SUM(all_hours), 2) // pro-rate by hours

How it works

How it works:

  1. Each share = pool × hours ÷ SUM(hours), rounded to cents.
  2. Rounded shares may not sum to the pool by a penny or two.
  3. The last person takes the plug: pool − SUM(other shares) — so it ties exactly.
  4. Use points instead of hours to weight by role (stylist 2, assistant 1).

The plug keeps a tip pool honest. Independently rounding each share to cents can leave the total a penny off — not acceptable when you’re handing out cash. Compute everyone except the last with ROUND, then set the last share to pool − SUM(the rest). Every share is fair to the cent and the envelope balances to zero.

Try it: interactive demo

Live demo

Pool and hours (comma-separated).

Shares:

Variations

Last-person plug

Absorb rounding:

=pool - SUM(other_shares)

By points

Weight by role:

=ROUND(pool * points / SUM(points), 2)

Check the tie-out

Should be zero:

=pool - SUM(shares)

Pitfalls & errors

Plug the last share. Rounded parts need a residual to total exactly.

Round to cents. ROUND(…, 2) on every share.

Zero hours. No hours/points gives #DIV/0!.

Practice workbook

📊
Download the free Tip Distribution to Assistants practice workbook
A tip-split sheet with the plug, by-points, and tie-out variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I split a tip pool in Excel?
Pro-rate by hours: =ROUND(pool * hours / SUM(hours), 2), then plug the last share so the parts tie to the pool.
Why don't the rounded shares add up?
Independent rounding to cents drifts a penny or two. Set the last share to pool - SUM(the rest) so it ties exactly.
How do I weight by role?
Use points instead of hours: =ROUND(pool * points / SUM(points), 2), giving stylists more points than assistants.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Tip pooling by hours · Tip and split · Round currency

Function references: ROUNDSUMPRODUCT