Hi all,
This is my second thread as thanks to the assistance of the amazing ajdelange in my first thread I was finally able to add the finishing touches to my long neglected Keg Volume Calculator (be advised that the Carbonation Calculator in my first thread is still very much a work in progress but for all intents and purposes here it is accurate enough).
If you just want the spreadsheet rather than reading on you can download my Keg Volume Calculator here.
Otherwise, once again, thanks for stopping by!
So my journey began here on HomeBrewTalk.com in the RaspberryPints - Digital Taplist Solution thread (which I invite everyone who hasn't already seen it to take a look), which now that I have set up is absolutely fabulous for tracking keg volume under normal circumstances (if you start with a completely full keg and you have no phantom pours, premature kicks, etc), but what happens when circumstances go outside of the norm? Well you can join the rest of the planet that do not have flow meters on their kegs, or you can use the new and improved Keg Volume Calculator.
So the base formula comes from a post in the RaspberryPints thread by DrunkleJon:
So armed with that I just expanded it to include a VLOOKUP table to allow for water density changes according to temperature, so all you needed to do was weigh your keg (before and after), enter in the temperature and final gravity and it would do the rest. The only caveat was of course that it was based on the pressure being a standard atmosphere (pretty big caveat if you've read my first thread).
Enter version two: "to allow for pressure/altitude (not that it makes a huge difference on the volumes we are using, but for the sake of accuracy, scalability and to satisfy my inner pedant), and also to select between metric and imperial units.", so now we were getting closer with the exception of Henry's Law of course:
So the final piece in the puzzle was being able to allow for the weight of the dissolved CO2, which began an entirely new journey of incorrect assumptions, followed by the patient guidance of ajdelange (further details in the other thread so I won't bore you all with the details again), which brings me here.
So in a nutshell here's how it works:
So, good people, please road test it for me and let me know if it is of value or not, and any suggestions/corrections/criticisms are always welcome.
More information on the above can be located at the following links:
Better Carbonation Calculator (hopefully) thread
[Version 2 Release] RaspberryPints - Digital Taplist Solution thread
https://www.simetric.co.uk/si_water.htm
http://www.wetnewf.org/pdfs/Brewing_articles/CO2%20Volumes.pdf
Keg Volume Calculator
This is my second thread as thanks to the assistance of the amazing ajdelange in my first thread I was finally able to add the finishing touches to my long neglected Keg Volume Calculator (be advised that the Carbonation Calculator in my first thread is still very much a work in progress but for all intents and purposes here it is accurate enough).
If you just want the spreadsheet rather than reading on you can download my Keg Volume Calculator here.
Otherwise, once again, thanks for stopping by!
So my journey began here on HomeBrewTalk.com in the RaspberryPints - Digital Taplist Solution thread (which I invite everyone who hasn't already seen it to take a look), which now that I have set up is absolutely fabulous for tracking keg volume under normal circumstances (if you start with a completely full keg and you have no phantom pours, premature kicks, etc), but what happens when circumstances go outside of the norm? Well you can join the rest of the planet that do not have flow meters on their kegs, or you can use the new and improved Keg Volume Calculator.
So the base formula comes from a post in the RaspberryPints thread by DrunkleJon:
Personally, I have been guessing. Otherwise you can do a little math.
Weight of keg with beer - Weight of empty keg = FG of beer * weight of volume as water
so
(Weight of keg with beer - Weight of empty keg)/FG of beer = (Gallons of beer in keg / 8.34)
since 1 gallon of water is 8.34 pounds
Should work pretty well.
So armed with that I just expanded it to include a VLOOKUP table to allow for water density changes according to temperature, so all you needed to do was weigh your keg (before and after), enter in the temperature and final gravity and it would do the rest. The only caveat was of course that it was based on the pressure being a standard atmosphere (pretty big caveat if you've read my first thread).
Enter version two: "to allow for pressure/altitude (not that it makes a huge difference on the volumes we are using, but for the sake of accuracy, scalability and to satisfy my inner pedant), and also to select between metric and imperial units.", so now we were getting closer with the exception of Henry's Law of course:
"In chemistry, Henry's law is a gas law that states that the amount of dissolved gas is proportional to its partial pressure in the gas phase. The proportionality factor is called the Henry's law constant. It was formulated by the English chemist William Henry, who studied the topic in the early 19th century."
So the final piece in the puzzle was being able to allow for the weight of the dissolved CO2, which began an entirely new journey of incorrect assumptions, followed by the patient guidance of ajdelange (further details in the other thread so I won't bore you all with the details again), which brings me here.
So in a nutshell here's how it works:
- Calculate the weight of the beer less the keg
- Calculate the volume of pure water at the same weight and temperature
- Adjust based on absolute pressure: gauge pressure (at equilibrium) plus barometric pressure
- Adjust the volume based on the gravity of the beer
- Calculate the weight of CO2 dissolved in the beer
- Adjust the volume based on the weight of the dissolved CO2
- Enter your measurements in column B
- Select you units of measurement from the dropdowns in column C (see screenshot)
- Note: Gauge pressure is only required if it is already force carbonated, if it's a new keg ensure row 6 is set to 0 psi/kPa
So, good people, please road test it for me and let me know if it is of value or not, and any suggestions/corrections/criticisms are always welcome.
More information on the above can be located at the following links:
Better Carbonation Calculator (hopefully) thread
[Version 2 Release] RaspberryPints - Digital Taplist Solution thread
https://www.simetric.co.uk/si_water.htm
http://www.wetnewf.org/pdfs/Brewing_articles/CO2%20Volumes.pdf
Keg Volume Calculator
Last edited: