Ice is the cheapest thing on the truck and the fastest thing to run out of. Two jobs consume it: the ice in the drinks, which scales with guests, and the ice packed around bottles and cans, which scales with how much you brought to chill.
150 guests at 1.5 lb each is 225 lb, plus 60 lb chilling the coolers — 15 twenty-pound bags.
The example
Three events, each with a guest count, a per-guest ice allowance, the chilling ice for the coolers, and the bag size at the store.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Event | Guests | Lb/guest | Chill lb | Bag lb | Bags |
| 2 | Backyard party | 80 | 1.5 | 40 | 20 | 8 |
| 3 | Reception | 150 | 1.5 | 60 | 20 | 15 |
| 4 | Summer gala | 220 | 2 | 80 | 20 | 26 |
The formula
Two ice budgets added together, then converted to bags:
How it works
Four moving parts:
B2*C2is drinking ice. One to one and a half pounds per guest covers a normal service window; two pounds is a hot outdoor day.+D2adds the ice that lives in coolers around bottles and cans. It never reaches a glass and it melts on its own schedule./E2converts pounds to bags. Party stores sell 7, 10, and 20 pound bags — keep the size in a cell because it changes by supplier.ROUNDUP(...,0)because you cannot buy 14.25 bags.
For an outdoor summer event, raise the per-guest figure rather than padding the bag count — that keeps the reason for the increase visible on the sheet.
Try it: interactive demo
Enter guests, your per-guest allowance, chilling ice, and bag size.
Variations
Cost of the ice
Multiply bags by the bag price to line-item it on the invoice.
Hot-weather bump
Raise the whole order by a melt factor for a summer afternoon.
Pitfalls & errors
Chilling ice is not optional and it is not small. A pair of loaded 120-quart coolers can swallow 60–80 lb before a single drink is poured.
Buy the last third of the order on the way to the venue, not the night before. Ice bought early is ice you already paid to melt in your own freezer.
Practice workbook
Frequently asked questions
How much ice per guest is right?
Does cocktail ice change the math?
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