Group bookings earn volume discounts. A nested IF picks the tier, then the total applies it to price times tanners and adds travel.
8 tanners at $45 get 10% off and a $30 travel fee: $354.00.
The example
Three bookings landing in different tiers.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Case | Price | Tanners | Travel | Discount | Total |
| 2 | Bridal party | $45 | 4 | $30 | 0.0% | $210.00 |
| 3 | Sorority | $40 | 12 | $50 | 20.0% | $434.00 |
| 4 | Prom group | $45 | 8 | $30 | 10.0% | $354.00 |
The formula
Check the largest tier first, then the next:
How it works
Here is the logic, one piece at a time:
IF(C2>=10,0.2,...)tests the top tier first.- The inner IF tests 5 or more for 10%.
- Otherwise 0.
- The total multiplies price by tanners, applies the discount and adds travel.
Put the tier thresholds on the price sheet.
Try it: interactive demo
Enter price, number of tanners and travel fee.
Variations
Tier table
Use LOOKUP with a table.
Cap discount
Limit the dollar discount.
Pitfalls & errors
Test the largest tier first; reverse order gives everyone 10%.
Decide if travel is discounted; here it is not.
Show the discount on the quote.
Practice workbook
Frequently asked questions
Why >= and not >?
More tiers?
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