Build a better mousetrap, and the world will beat a path to your door...
If you just want the spreadsheets rather than reading on you can download all of my Carbonation Calculator files here.
Otherwise, thanks for stopping by!
I'll start off by saying I'm NOT a Chemical Engineer (in fact I only heard of Henry's Law about a week ago), so if anyone notices any flaws in the logic of my equations please feel free to pass judgement as I won't be offended and the end goal to build a better mousetrap (or in this case carbonation calculator) will be achieved.
So with the disclaimer and invitation for collaboration out of the way, the background for my motivation is quite simple: all the force carbonation charts/calculators are based at sea level and I live 707 metres (≈2320 feet) above sea level so not particularly accurate. Sure there's the Ray Daniel's guide of "Add 1psi for every 2,000ft. above sea level." but again not very accurate.
So a week ago I set off on a journey of (hopefully) understanding in a brave new world that I was little prepared for.
Along the way I stumbled across this: "CO2 Volumes in beer - Development of formulas which replace the ASBC (Zahm and Nagel) CO2 tables." where the author makes the following statements:
"The source of the ASBC data is not known and difficulty in coming up with more accurate formulas may derive from, for example, measurement, rounding or transcription errors in the table data."
"It should exhibit smooth dependence on temperature and little or no dependence on pressure. If there is any pressure dependence, it too should be smooth. As we will see the table data does show variation with pressure that is not smooth."
The author then goes on to provide three formulae to try to match the results of the ASBC data, the first of which is the following:
This "simple formula" (that's easy for you to say), roughly translates into this in Excel:
(where B4 is keg pressure and B5 is barometric pressure (both in psi), and B3 is keg temperature in Fahrenheit, so if you substitute '14.695' (sea level) in B5 with your local barometric pressure you'll get a pretty good calculator based on the ASBC CO2 tables)
At this point I decided it was time to find out what the 'Constant Henry Coefficient' was, to try and figure how the author came up with that "simple formula"...
According to Wikipedia:
"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."
That doesn't sound so bad right, how hard could it be? Well it turns out Henry's Law can have many definitions depending on the application and in this scenario (temperature dependence) it looks something like this:
Okay so this is the bit that took me a week to get my head around (or not) until finally I came up with this:
(where B4 is keg pressure in psi and B3 is temperature in Celsius (sorry guys I'm an Aussie), and if you substitute '0.92' with your local barometric pressure in atm you'll get another pretty good calculator based on Henry's Law temperature dependence)
So the last piece in the puzzle was the Barometric formula so that you didn't need to find out what your local barometric pressure in atm was.
The Barometric formula looks like this:
After Henry's Law this was actually easy, so in Excel it looks like this:
(where B5 is your altitude in metres)
So with this new equation I was able to update my Henry's Law equation to use this as follows:
(where B6 is the Barometric formula equation)
So what I've now done for anyone interested in road testing them and providing any feedback is to bundle both metric and imperial versions (with a breakdown of how I came to those equations) on Drive for you to download here.
More information on the above can be located at the following links:
http://www.zahmnagel.com/wp-content/uploads/2017/04/Zahm-Nagel-CO2-in-Beer-Chart.pdf
http://www.wetnewf.org/untitled.html
https://chemengineering.wikispaces.com/Henry's+Law
https://en.wikipedia.org/wiki/Henry's_law#Temperature_dependence
https://en.wikipedia.org/wiki/Barometric_formula
My Carbonation Calculator files
If you just want the spreadsheets rather than reading on you can download all of my Carbonation Calculator files here.
Otherwise, thanks for stopping by!
I'll start off by saying I'm NOT a Chemical Engineer (in fact I only heard of Henry's Law about a week ago), so if anyone notices any flaws in the logic of my equations please feel free to pass judgement as I won't be offended and the end goal to build a better mousetrap (or in this case carbonation calculator) will be achieved.
So with the disclaimer and invitation for collaboration out of the way, the background for my motivation is quite simple: all the force carbonation charts/calculators are based at sea level and I live 707 metres (≈2320 feet) above sea level so not particularly accurate. Sure there's the Ray Daniel's guide of "Add 1psi for every 2,000ft. above sea level." but again not very accurate.
So a week ago I set off on a journey of (hopefully) understanding in a brave new world that I was little prepared for.
Along the way I stumbled across this: "CO2 Volumes in beer - Development of formulas which replace the ASBC (Zahm and Nagel) CO2 tables." where the author makes the following statements:
"The source of the ASBC data is not known and difficulty in coming up with more accurate formulas may derive from, for example, measurement, rounding or transcription errors in the table data."
"It should exhibit smooth dependence on temperature and little or no dependence on pressure. If there is any pressure dependence, it too should be smooth. As we will see the table data does show variation with pressure that is not smooth."
The author then goes on to provide three formulae to try to match the results of the ASBC data, the first of which is the following:
This "simple formula" (that's easy for you to say), roughly translates into this in Excel:
=SUM((B4+B5)*(0.01821+(0.090115*(EXP(-(B3-32)/43.11))))-0.003342)
(where B4 is keg pressure and B5 is barometric pressure (both in psi), and B3 is keg temperature in Fahrenheit, so if you substitute '14.695' (sea level) in B5 with your local barometric pressure you'll get a pretty good calculator based on the ASBC CO2 tables)
At this point I decided it was time to find out what the 'Constant Henry Coefficient' was, to try and figure how the author came up with that "simple formula"...
According to Wikipedia:
"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."
That doesn't sound so bad right, how hard could it be? Well it turns out Henry's Law can have many definitions depending on the application and in this scenario (temperature dependence) it looks something like this:
Okay so this is the bit that took me a week to get my head around (or not) until finally I came up with this:
=SUM(((((B4*0.0681)+0.92)/29.41)*(0.0821*(B3+273.15)))*(EXP(2400*((1/(B3+273.15))-(1/298.15)))))
(where B4 is keg pressure in psi and B3 is temperature in Celsius (sorry guys I'm an Aussie), and if you substitute '0.92' with your local barometric pressure in atm you'll get another pretty good calculator based on Henry's Law temperature dependence)
So the last piece in the puzzle was the Barometric formula so that you didn't need to find out what your local barometric pressure in atm was.
The Barometric formula looks like this:
After Henry's Law this was actually easy, so in Excel it looks like this:
=SUM(((288.15/((288.15+(-0.0065)*B5)))^((9.80665*0.0289644)/(8.3144598*(-0.0065)))))
(where B5 is your altitude in metres)
So with this new equation I was able to update my Henry's Law equation to use this as follows:
=SUM(((((B4*0.0681)+B6)/29.41)*(0.0821*(B3+273.15)))*(EXP(2400*((1/(B3+273.15))-(1/298.15)))))
(where B6 is the Barometric formula equation)
So what I've now done for anyone interested in road testing them and providing any feedback is to bundle both metric and imperial versions (with a breakdown of how I came to those equations) on Drive for you to download here.
More information on the above can be located at the following links:
http://www.zahmnagel.com/wp-content/uploads/2017/04/Zahm-Nagel-CO2-in-Beer-Chart.pdf
http://www.wetnewf.org/untitled.html
https://chemengineering.wikispaces.com/Henry's+Law
https://en.wikipedia.org/wiki/Henry's_law#Temperature_dependence
https://en.wikipedia.org/wiki/Barometric_formula
My Carbonation Calculator files