When one payment or flat fee covers several matters, allocate it by each matter’s share of hours (or value) — and make the rounded pieces tie back exactly to the total with a plug on the last row.
The example
$5,000 split across three matters by hours.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | Allocated parts | |
| 3 | Sum ties to | → $5,000.00 |
The formula
The formula:
How it works
How it works:
- Each matter’s share =
total_fee × matter_hours ÷ SUM(hours), rounded to cents. - Rounded pieces may not sum to the total by a penny or two.
- The last row takes a plug:
total_fee − SUM(other_allocations)— so it ties exactly. - Allocate by value or headcount instead of hours by swapping the weight.
The plug guarantees the tie-out. Independently rounding each share to cents can leave the parts off the total by a penny — unacceptable in billing. Compute every matter except the last with ROUND(...), then set the last to total − SUM(the rest). The pieces are individually fair and collectively exact, which is what a client and an auditor both expect.
Try it: interactive demo
Total fee and hours per matter (comma-separated).
Variations
Last-row plug
Absorb rounding:
Allocate by value
Weight by fees not hours:
Check the tie-out
Should be zero:
Pitfalls & errors
Plug the last row. Rounded shares need one residual line so they sum exactly.
Round to cents. Use ROUND(…, 2) on currency allocations.
Zero total weight. No hours/value gives #DIV/0! — guard it.
Practice workbook
Frequently asked questions
How do I allocate a combined fee across matters in Excel?
Why do the rounded allocations not add up?
Can I allocate by value instead of hours?
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