Kit And Extract Beer Spreadsheet

[woodwormm]

hi, sorry if this has been asked previously.

but is there more than one version of the spreadsheet?

it just wont work on my windows 7 with Excel 2010...

I can't even open it, i get some kind of win32 error.. yet it works on my work computer (xp and Excel 2003 or 2007....) however i prefer to tinker my recipes at home at my own pace rather than alt tabbing everytime the boss comes by!

 

[carniebrew]

hi, sorry if this has been asked previously.

but is there more than one version of the spreadsheet?

it just wont work on my windows 7 with Excel 2010...

I can't even open it, i get some kind of win32 error.. yet it works on my work computer (xp and Excel 2003 or 2007....) however i prefer to tinker my recipes at home at my own pace rather than alt tabbing everytime the boss comes by!

Wassup with your user name??

I'm using the latest version on Win 7 with Excel 2010, and even 2013 on my Win 8 laptop, so no probs with the spreadsheet. You will have to enable macros when you first open the xls, and possibly even have to re-save the spreadsheet as a 'macro enabled workbook' which gives it an xlsm extension.

What's your exact error?

 

[woodwormm]

username? what you talking bout willus?

so after a few right click download/save as's and open withs i've managed to save it as macro enabled workbook. .. cheers

 

[carniebrew]

username? what you talking bout willus?

so after a few right click download/save as's and open withs i've managed to save it as macro enabled workbook. .. cheers

Good to hear.

It says your user name is "Printed Forms Section". I don't geddit?

 

[woodwormm]

Good to hear.

It says your user name is "Printed Forms Section". I don't geddit?

I don't really get it either... my beer/brew room is a tiny old office in the corner of my shed that the previous owner put tgere... it was obviously taken from a factory somewhere... one of the widows has printing on it..." printed forms section"

 

[carniebrew]

I'm wondering if anyone's having similar issues to me when it comes to matching the spreadsheet's calculation of OG/FG when using specialty grains? It seems the larger my spec grain bill, the further off my expected OG/FG are.

As examples, when i make my go-to kit hefe, with a Thomas Coopers Wheat kit, 500gm ldme & 300gm dex with WB-06 yeast into 18.5 litres, the expected OG/FG are spot on what I get when measuring with my hydrometer (1044/1011). Same again for a kit Irish Ale I made with only kit and adjuncts.

However, since i've moved to extract/spec grain brewing, i'm finding my OG is a lot less than the spreadsheet predicts. While my Dr Smurto's Golden Ale with 250gm of Crystal was only a few points under on the OG (1047 vs 1043), it was a bit closer on the FG (1014 vs 1012), meaning the overall abv was close enough to right. But my more recent Dunkelweizen, with 900gm of various grain was a whopping 13 points below what the spreadsheet calculated (1054 vs 1042), and the predicted FG of 1018 is way off the 1011 its currently sitting at on day 8 of fermentation.

I'm guessing this has to do with the amount of sugars the grains are expected to add vs what i'm actually getting, and i'm wondering if it has something to do with the steeping process I'm using? I put the grain in a grain/hop bag and steep it at the recommended temps in about 3 litres of water, swirling and dunking occasionally....but when taking them out after 30 minutes, I don't do much in terms of squeezing/sparging, so i'm wondering if that's affecting how much i'm getting out of the grain. Anyone got any thoughts on this?

 

[ianh]

I'm wondering if anyone's having similar issues to me when it comes to matching the spreadsheet's calculation of OG/FG when using specialty grains? It seems the larger my spec grain bill, the further off my expected OG/FG are.

As examples, when i make my go-to kit hefe, with a Thomas Coopers Wheat kit, 500gm ldme & 300gm dex with WB-06 yeast into 18.5 litres, the expected OG/FG are spot on what I get when measuring with my hydrometer (1044/1011). Same again for a kit Irish Ale I made with only kit and adjuncts.

However, since i've moved to extract/spec grain brewing, i'm finding my OG is a lot less than the spreadsheet predicts. While my Dr Smurto's Golden Ale with 250gm of Crystal was only a few points under on the OG (1047 vs 1043), it was a bit closer on the FG (1014 vs 1012), meaning the overall abv was close enough to right. But my more recent Dunkelweizen, with 900gm of various grain was a whopping 13 points below what the spreadsheet calculated (1054 vs 1042), and the predicted FG of 1018 is way off the 1011 its currently sitting at on day 8 of fermentation.

I'm guessing this has to do with the amount of sugars the grains are expected to add vs what i'm actually getting, and i'm wondering if it has something to do with the steeping process I'm using? I put the grain in a grain/hop bag and steep it at the recommended temps in about 3 litres of water, swirling and dunking occasionally....but when taking them out after 30 minutes, I don't do much in terms of squeezing/sparging, so i'm wondering if that's affecting how much i'm getting out of the grain. Anyone got any thoughts on this?

I was mainly an extract brewer and most of my brews had between 0.6 and 0.9kg of speciality grains, whilst some OGs were lower it was only .002 or .003 some were in agreement and an occasional one was higher.

One of my regular brews was a mid irish red 2.1kg DME .3kg Caraaroma, 0.2kg Carared and 0.2kg CaraPils, spreadsheet 1043 my range 1040 to 1043. Used a variety of yeasts so FG changed.

So sorry cannot explain.

 

[carniebrew]

I was mainly an extract brewer and most of my brews had between 0.6 and 0.9kg of speciality grains, whilst some OGs were lower it was only .002 or .003 some were in agreement and an occasional one was higher.

One of my regular brews was a mid irish red 2.1kg DME .3kg Caraaroma, 0.2kg Carared and 0.2kg CaraPils, spreadsheet 1043 my range 1040 to 1043. Used a variety of yeasts so FG changed.

So sorry cannot explain.

Thanks for the reply. What did you do to steep your grain? If in a grain/hop bag, how aggressive were you in squeezing/sparging after taking the bag out? I'm thinking it might just be a process thing on my end.

 

[carniebrew]

I want to just add another note here about HCF, using a real life example. I've just tasted the first of my extract version of Dr Smurto's Golden Ale, after 10 days of bottle conditioning (yes I know it's still early but i'm impatient). When designing my version of the brew I turned HCF on in Ian's spreadsheet. In order to get it up to my desired ~32 IBU's, I had to up my 60 minute Amarillo addition to 30 grams (7 litre boil), with a 20 minute addition of 20 grams. With a full boil (and thereby no HCF) I would have done something like 20 @ 60 and 15 @ 20. I also did a 30 gram addition at flameout, but that's got nothing to do with IBU's I suppose.

Anyway, on tasting today it is very hoppy, i'd guess quite a bit more than the 32 IBU's I was aiming for. Carbonation is perfect (my first go at bulk priming as I wanted only ~2.6 vols in this brew). This is the hoppiest beer i've brewed though, and I understand they mellow with age? But i'm thinking I might be better off brewing with HCF off from now on, or just tempering the results a little. YMMV of course, especially if you love your hops.

 

[ianh]

When steeping grains I used a grain bag (fairly coarse weave) and gave good squeeze after 30 minutes at 65C.

Boiling 7 litres I would leave the HCF off, I use to boil 10 litres when I did Extracts and did not use the HCF. It's there as an alternative for people who only boil say 3 litres. It is a matter of finding what suits you and sticking to it.

I am sure your current brew will mellow with time. Build up a stock then you can drink things after say three months, a lot of my extracts, depending on the style and strength were not drunk till after 6 months in the bottle. Also suggest keeping back a couple of bottles from each brew and drinking them after say 9 and 12 months.

 

[Mattrox]

I am having trouble with the save recipe macro. It wants to debug it.
It highlights Chr$ in this line: MsgBox ("NO NAME IN CELL H4" & Chr$(13) & Chr$(13) & "ENTER A NAME AND TRY AGAIN") and gives a compile error : "Can't find project or library".
Maybe I'll try to download it again.... maybe I did something wrong when I enabled macros?

 

[carniebrew]

You do have to have a name in cell H4, which is your name for whatever recipe you're putting together. I can recreate the error by clicking "New Recipe" then immediately hitting save. Are you leaving it blank?

 

[Mattrox]

You do have to have a name in cell H4, which is your name for whatever recipe you're putting together. I can recreate the error by clicking "New Recipe" then immediately hitting save. Are you leaving it blank?

Nah, it was a macro error. I re-downloaded the file and started again. I must have not done the enable macro procedure correctly the first time, and rather than work out where I stuffed up I just started again.

All good now.

 

[ianh]

Had a few queries lately re the FG values and also steeping grain efficiency. So please find version 4.1

View attachment Kit & Extract Beer Designer V4.1.xls

Made a correction to the FG formula when using LME and DME.

Changed the formula for calculating the OG when using specialty grains. Now uses an adjustable efficiency factor located on the Grains worksheet.

Cheers
Ian

 

[carniebrew]

You're a superstar Ian, thanks for keeping this up, despite having moved away from extract brewing yourself I understand.

Is there a dummies guide for copying across all your data from previous versions? Oh, and while we're at it, a column on the front page that shows the percentage of each ingredient in the overall fermentables bill would make for a great enhancement down the track. I've done it myself manually down the left hand side of each cell.

I can see the difference straight away, I was working on a new recipe for a hefe, using 2.8kg of wheat LME and 250gm of light crystal, with WB-06 yeast in 18 litres. The 4.0 sheet was telling me an FG of 1019, which sounded way too high. This new version is suggest 1015, which I'm sure will be closer to the mark.

 

[ianh]

I assume somewhere in the thread there are details for copying data across, can't remember now, just need to be careful of the calculated (shaded yellow) columns.

To add %s what you could do is change the cell colour for the two adjuncts (to remind you) delete the ADJ in column A then put the formula

=IF((C6>0),100*C6/SUM(C$6:C$7:C$10:C$11:C$14:C$15:C$18:C$23),"") in cell A6 then copy this down to the other malt/grain/adj cells, need to adjust number of decimals.

Glad you get a better estimate of FG.

cheers

Ian

 

[Ruddager]

Thanks for the update - I've just started getting into using this and I've learnt a lot from it.

I just found some EBC stats for those Briess liquid malts too - not sure why they're not in there. For anyone that wants to add them:


Briess Pilsen Light 3.9 1.5 0
Briess Bavarian Wheat 5.9 1.5 0
Briess Golden Light 7.8 1.5 0
Briess Munich 15.8 1.5 0
Briess Sparkling Amber 19.7 1.5 0
Briess Traditional Dark 59.1 1.5 0

 

[carniebrew]

Something I find quite curious with the spreadsheet is the result of pressing CTRL-C. I do this out of a lifelong habit to copy a cell, but for some reason in the spreadsheet it pastes a massive comment into the cell I'm trying to copy. The comment seems to be what's contained on the "Brewday" tab.

I can work around it by right-clicking the cell and choosing "Copy", but I forget waaay too often. Any idea how to stop this from happening Ian?

 

[carniebrew]

I assume somewhere in the thread there are details for copying data across, can't remember now, just need to be careful of the calculated (shaded yellow) columns.

To add %s what you could do is change the cell colour for the two adjuncts (to remind you) delete the ADJ in column A then put the formula

=IF((C6>0),100*C6/SUM(C$6:C$7:C$10:C$11:C$14:C$15:C$18:C$23),"") in cell A6 then copy this down to the other malt/grain/adj cells, need to adjust number of decimals.

Glad you get a better estimate of FG.

cheers

Ian

A variation on that formula, if you prefer you can format the cells to show a percentage, with 1 decimal point (or none if you're ok with rounding up and want to save space). If you do, use this formula instead:

=IF((C6>0),C6/SUM(C$6:C$7:C$10:C$11:C$14:C$15:C$18:C$23),"")

Then copy and paste down so the first two C6 entries change to the right cell reference.

 

[SimonT]

Awesome update, love the fact that steeping efficiency is adjustable - did I have something to do with that?

Only other thing I'd like to see is a whole lot more rows for 'HOPS', to take care of those crazy recipes that have say 2 lots of dry hops with 3 or 4 hops each, not to mention some of the boiling hops craziness with IPAs etc these days.

I made some new rows myself and it all looks OK, until you save the spreadsheet, re-open to a new recipe and the extra rows from the other recipe are still there. I assume the extra rows get saved when I save the entire spreadsheet, not as part of the individual recipe.

Love this spreadsheet, makes extract brewing easier than anything else I've used.

Cheers for all your work on it Ian, even though you're no longer an extract brewer yourself!