Stock the bar from a simple rule: about one drink per guest per hour. Multiply guests by hours for total drinks, split by type, and round up to whole bottles or cases.
The example
120 guests, 5 hours.
| A | B | |
|---|---|---|
| 1 | Item | Value |
| 2 | 120 × 5 × 1 | — |
| 3 | Drinks | → 600 |
The formula
The formula:
How it works
How it works:
- The rule of thumb: ~1 drink per guest per hour (more early, fewer late).
- Total drinks =
guests × hours × rate; split by a mix (e.g. 50% beer, 30% wine, 20% spirits). - Convert to bottles: a 750ml wine pours ~5 glasses; a case of beer is 24.
- Always
ROUNDUPto whole units — you can’t buy a partial bottle.
Front-load the estimate. Guests drink more in the first hour (cocktail hour) and taper off, so a flat 1/guest/hour can under-stock the start and over-stock the end. A common refinement: 2 drinks in hour one, then 1 per hour after. Build the per-hour rate as a small table and SUMPRODUCT it for a more realistic total.
Try it: interactive demo
Guests, hours, drinks per guest-hour.
Variations
Wine bottles
5 glasses each:
Beer cases
24 per case:
Front-loaded total
By hour:
Pitfalls & errors
Round up units. Bottles and cases come whole — never round down.
Front-load. Early hours run higher than 1/guest/hour.
Non-drinkers. Adjust the rate for the crowd and add soft drinks.
Practice workbook
Frequently asked questions
How do I calculate drinks needed for an event in Excel?
How do I convert drinks to bottles?
Should I assume a flat drink rate?
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