Most portable toilet companies price a daily rate that stops at a weekly cap. MIN picks whichever is lower for each rental, so a five-day rental is not billed more than the weekly price.
10 units for 5 days at $25 with a $95 cap cost $950, or $19 per unit-day.
The example
Three rentals. The festival and build site hit the cap; the wedding does not.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Case | Units | Days | Daily $ | Cap $ | Total | Per unit-day |
| 2 | Festival | 10 | 5 | $25.00 | $95.00 | $950.00 | $19.00 |
| 3 | Wedding | 4 | 1 | $25.00 | $95.00 | $100.00 | $25.00 |
| 4 | Build site | 6 | 3 | $25.00 | $95.00 | $450.00 | $25.00 |
The formula
MIN compares the uncapped daily total with the cap for one unit, then multiplies by units:
How it works
Here is the logic, one piece at a time:
C2*D2is days times the daily rate for one unit.MIN(...,E2)returns the lower of that and the weekly cap.B2*multiplies by the number of units.- The second formula divides by unit-days to show the effective rate.
Add a delivery fee as a separate cell; it should not be capped.
Try it: interactive demo
Enter units, days, daily rate and weekly cap.
Variations
Add delivery
Add a flat delivery charge after the cap.
Weeks and days
Split long rentals into whole weeks plus leftover days.
Pitfalls & errors
The cap usually covers seven days. Beyond seven days use the weeks-and-days variation.
Do not multiply units before MIN; that compares the wrong totals.
Show the effective per unit-day rate on the quote so customers see the savings.
Practice workbook
Frequently asked questions
Why MIN and not IF?
Can the cap vary by unit type?
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