Help with data calculations for a brewery

New Contributor

Hello! I work in the planning department of a local brewery and needed some help with a data sheet we have been trying to create. 
I need a spreadsheet to calculate the weight of kegs (empty and full) and cases (cans) of beer and assign a pallet count to these totals.

example: 100 cases = 1 pallets. I have three hundred cases.  When I type in three hundred, I'd like the total pallet count to reflect. I.e. (3 pallets)


I'm not a very tech savvy person...but any help or guidance would be much appreciated.

4 Replies


Let's say you enter the number of cases in C2.

In D2, enter the formula


This can be filled down if required.



Additional data is needed here from you to help you with your plans.
Example, would you like in liters or in kg?
or in quart or pound?
quart English or US?
Which unit of weight would you like for which product?
As you can see, many questions arise.

At the same time, you need to know which version of Excel and which operating system are you using?

It is always good to include a file (without sensitive data) with your plans. So everyone is helped faster and more precisely.

Enclosed is a demo file where you can describe your project exactly and send it to the forum again so that a solution proposal can be submitted to you quickly and tailored to your needs.

Thank you for your understanding and patience


I know I don't know anything (Socrates)

@NikolinoDE we measure in US Gallons for our kegs, but we weigh all assets by LBS. Cases of beer are 20.6 lbs each, and 100 can go on 1 pallet (which weighs 25 lbs)   1/6 BBL (barrel) kegs weigh 60 lbs each and empty weigh 16 lbs. We can fit 20 of those kegs on 1 pallet when they are full. but they can also stack in stacks of 2. So, 40 1/6 bbl kegs on 1 pallet position, using 2 pallets. 

1/2 BBL kegs are our largest. 160.5 LBS when full, 29.8 LBS when empty, and 8 can fit on one pallet. We generally stack these as well so 16 kegs, one pallet position, using two pallets....


I know all the weights and info I need, I just go blank whenever I use Excel ..



I hope that I do not strain your patience, but I am not a beer brewer and do not know about your products.

Unfortunately not with the American masses either, but there are comparison tables for this.

Although German, I only drink non-alcoholic beer and only on holidays :)) ...Prost!
Maybe I'm not the right person for drinking beer ... but I'll try to offer you a tailor-made solution as far as possible :) and many others here in this Community.

Therefore I ask you to open the file, insert the requested information and send it back.


Thank you for your understanding and patience



I know I don't know anything (Socrates)