Guests drink faster in the first hour and slower afterward. A two-rate estimate gives a more realistic drink count than one flat number, and dividing by pours per bottle turns it into a shopping list.
100 guests over 5 hours at 2 then 1 drink per hour is 600 drinks and 36 bottles.
The example
Three events with different lengths and drink rates.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Case | Guests | Hours | First hr | After | Drinks | Bottles |
| 2 | Wedding | 100 | 5 | 2.0 | 1.0 | 600 | 36 |
| 3 | Corporate | 60 | 3 | 1.5 | 1.0 | 210 | 13 |
| 4 | Birthday | 30 | 4 | 2.0 | 1.2 | 168 | 10 |
The formula
The first hour rate plus the later rate for the remaining hours, times guests:
How it works
Here is the logic, one piece at a time:
C2-1counts hours after the first.E2*(C2-1)adds the slower later rate.- Adding
D2and multiplying by guestsB2gives drinks. - Dividing by 17 pours per 750 ml bottle gives bottles;
ROUNDUProunds up.
A 1.5 oz pour gives roughly 17 drinks from a 750 ml bottle.
Try it: interactive demo
Enter guests, hours and the two drink rates.
Variations
Add buffer
Add 10% to cover heavy pours.
Beer share
Split drinks by a beer percentage.
Pitfalls & errors
Count only drinking guests, not designated drivers or children.
Do not treat hours as zero-based; the first hour uses the first-hour rate only.
Round bottle counts up to whole cases when buying wholesale.
Practice workbook
Frequently asked questions
Why 17?
Can I use different rates per event 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