Beer Making Spreadsheet

[macr]

Hey guys, I've slapped together a spreadsheet for my beer making exploits (K+K and soon partial mashes).
Can you see anything, that I should add?
The formula for the alcohol % is based on the new Coopers formula of OG-FG/7.46+0.5.
To get it to work on excel it looks more like this:
=(((C18-C19)/7.46)+0.5)/100
with the cell formatted to percent

How can I make this better. Mind you I will make a new spreadsheet if I move to AG beers.

View attachment My_beer_datasheet.zip

 

[Stuster]

Strange that you should mention this as I have been confused about the calculation of alcohol in my brews too. I found that the online calculators I checked did not agree with the results of this formula. Grumpy's Brew Analyser
:unsure:

Then found a few extremely complicated formulas which I found almost impossible to write into excel. Then found this link BYO Write your own brewing spreadsheet which seems to have a useful formula ABV = (OG - FG) * 0.129. This formula seems to agree with all the calculator at Grumpy's. :super: Bit lower alcohol per beer with this which obviously means it's time to crack open another one. :beer:

Stuart

 

[macr]

Hmm. I don't know which one is more correct. The Coopers club magazine has an article on yeast and at the end, they have an article on how much alcohol. They have a complicated formula, but they think the formula supplied is nearly as accurate. But I feel that 1045-1010 as OG and FG, is more like 4.6% at Grumpys, than the 5.19% from Coopers, but I may be wrong.

 

[macr]

I just had a brainwave. I now have both formulas in the spreadsheet, with a name as to who came up with what formula. So now I can speculate the %ALC/VOL is some where between these two figures. :beer:

 

[nonicman]

Inputting 1045-1010 as OG and FG into Promash returns 4.6% ABV.

 

[macr]

I will err on the side of the BYO formula as it came out at 4.52%, but still say it is somewhere between the two results. Thanxs Nonicman

 

[Spun]

It seems to me that the Promash, BYO and Grumpys formulas are not adding an adjustment for priming sugar where the Coopers one is (the +0.5 bit). I use the coopers formula in my dodgy spreadsheet with a +0.2 as I don't use heaps of priming sugar. Of course if you're kegging then don't add any adjustment.

 

[macr]

Here is a quote from the Coopers Club News:

ABV = [76.08*FG*(OG-FG)] / [(1.775-OG) * 0.794]

Too complicated? We think so!

We have tested the alcohol content of numerous home brews with our electronic alcohol analyser while recording the Original Gravity, Final Gravity and the Gravity following secondary fermentation.

The results have convinced us that our current formula is close to the mark with only needing slight adjustment. Instead of adding 0.2% to allow for priming we should add 0.5%.
REVISED ALCHOHOL FORMULA:

OG-FG/7.46+0.5 = APPROX%ALC/VOL

We emphasise that although we have adjusted the formula to more closely reflect the alcohol content, it remains an approximation only.