All versions
Groups split the bill, and gratuity is part of it. Rate times hours plus gratuity, divided by riders, is the per-person cost.
Quick formula: Rate times hours, plus gratuity:
=ROUND(B2*C2*(1+D2),2)
5 hours at $180 with 20% is $1,080, or $54 for each of 20 riders.
The example
Three bookings.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Case | Rate | Hours | Gratuity | Riders | Total | Per rider |
| 2 | Bachelorette | $180 | 5 | 20.0% | 20 | $1,080.00 | $54.00 |
| 3 | Prom | $150 | 4 | 18.0% | 28 | $708.00 | $25.29 |
| 4 | Wedding shuttle | $200 | 3 | 20.0% | 40 | $720.00 | $18.00 |
The formula
Subtotal, add gratuity, then split:
=ROUND(B2*C2*(1+D2),2) // total; per rider = ROUND(Total/Riders,2)
How it works
Here is the logic, one piece at a time:
B2*C2is the subtotal.(1+D2)adds gratuity.ROUNDrounds to cents.- Dividing by
E2splits it.
Enter gratuity as a percent.
Try it: interactive demo
Interactive
Enter rate, hours, gratuity and riders.
Variations
Minimum hours
Charge at least 4 hours.
=ROUND(B2*MAX(4,C2)*(1+D2),2)
Deposit
Half upfront.
=F2/2
Pitfalls & errors
Fuel surcharges are often separate.
Divide the total with gratuity, not the subtotal.
Share the per-rider cost when quoting.
Practice workbook
Download the free Limo & Party Bus: Total With Gratuity And Cost Per Rider practice workbook
Edit the yellow rate, hours and gratuity cells; total and per rider recalculate.
Frequently asked questions
Include tax?
Add a tax cell if needed.
Is gratuity mandatory?
Whatever your contract says.
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