Kit And Extract Beer Spreadsheet

[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!

 

[ianh]

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!

Hi SimonT

Thanks, welcome to the forum and yes amongst others.

If you want to add extra hops what I suggest you do is make a second copy of the spreadsheet. You can add extra rows by copying cells B33:G33 down to the rows below. Plus you need to change the formula in cell K14 (change the G33 value to the new last hop row). This will allow you to design the recipe and the save the spreadsheet with a new name (Beer Name).

However most of the spreadsheet macros will not operate correctly as they will not take into account of the extra hop rows plus you would need to write your Brewday sheet down as it also would not include the extra hop rows. I would suggest you make a new copy of the spreadsheet everytime you do this so as to keep a record.

As an alternative, if you are adding hops at the same time, you can add hops to the hop list to reflect the addition, simple example, 50/50 Amarillo 8.9 AA% and Cascade 7.8 AA% add a new hop to the list 50Amarillo/50Cascade with a 8.35 AA% value.

cheers

Ian

 

[carniebrew]

Something I've only just noticed on the spreadsheet...if using LME, the "Brewday" worksheet doesn't show the total amount in the recipe like it does for DME. I needed to insert a couple of rows then copy and tweak the DME formulas to get it to show.

 

[mosto]

Hi ianh,

Like others, I have found this tool extremely useful. The only problem is I've never really been able to save and store recipes in it. I've tried most of the versions, including the latest, but get the same errors. Below (hopefully) is a screenshot of the error I receive when I click on 'Save Recipe'.



If I delete the Chr$(13) references, both here and in "RECIPE ALREADY EXISTS", it saves fine. The problem then is if I click 'Get Recipe' it brings up the list of saved recipes, I highlight one and click 'Select' and the program stops responding.

I'm using Excel 2003.

Any light you, or anyone else can sehd on this would be much appreciated.

Cheers,

 

[ianh]

Hi ianh,

Like others, I have found this tool extremely useful. The only problem is I've never really been able to save and store recipes in it. I've tried most of the versions, including the latest, but get the same errors. Below (hopefully) is a screenshot of the error I receive when I click on 'Save Recipe'.

K&E1.jpg

If I delete the Chr$(13) references, both here and in "RECIPE ALREADY EXISTS", it saves fine. The problem then is if I click 'Get Recipe' it brings up the list of saved recipes, I highlight one and click 'Select' and the program stops responding.

I'm using Excel 2003.

Any light you, or anyone else can sehd on this would be much appreciated.

Cheers,

Hi mosto

I think you may be missing one of the project libraries.

If you open the spreadsheet then hit ALT & F11 should bring up visual basic.

In the visual basic Window go to the Tools Menu and select References I have ticked

Visual Basic for Applications
MS Excel 12.0 Object Library
OLE Automation
MS Office 12.0 Object Library
MS Forms 2.0 Object Library
MS Word 12.0 Object Library

This is for Excel 2007 so using a different version will get a number other than 12.0.

Hopefully this should fix the problem.

cheers

Ian

 

[mosto]

Thanks mate, the MS Word 12.0 Object Library in 2003 is missing, so I ticked 11.0 instead. All good now

 

[carniebrew]

Can someone help me understand the fermentables introduced by steeping specialty grain? I am thinking of using "Carawheat" steeped into an upcoming dunkelweizen, and it wasn't in the spreadsheet...so I added a row on the 'grains' tab and put it in, with an EBC of 120. It was the "potential" that got me thinking...most of the other grains on that tab are listed around 1.034. Is that meant to be the potential fermentability of that grain if it was mashed? And then we have the "Efficiency of Steeping Grains" in the latest version, set to 60%...is that the estimate of how much malt sugar we're extracting from the grain because we're steeping, not mashing?

 

[ianh]

Hi carniebrew

I believe you are correct in your assumptions. Carawheat potential is 1.034 or 1.035 depending where you look (EBC 110 -150).

In the spreadsheet the efficiency is an estimate of the sugars extracted by steeping. I could find little information on steeping efficiency and even in the bits available the numbers vary widely..

 

[gc.camel]

I thought i'd do some experimenting with making two 'half' batches with different hops. Pour half the tin in one fermenter and the other half in a second and top them up to 11 or so liters. When i attempt this on spreadsheet the IBU's and EBC of the kit seems to be based on using the full weight of the tin. doesn't seem to be a function of the weight used. Should i be making half kits in the kit list or do i need to change a formula? btw I'm under the assumption that the IBU's and EBC of full kit +20L is equal to half a kit +10L

thanks guys

edit: i changed the IBU on the first kit to
=IF(($C6)>0,(VLOOKUP($B6,KITS!$A$3:$D$150,3,FALSE)*23/$C$3)*($C6/(VLOOKUP($B6,KITS!$A$3:$D$150,4,FALSE))),"")
and the EBC on the first kit to
=IF(($C6)>0,(VLOOKUP($B6,KITS!$A$3:$D$150,2,FALSE)*23/$C$3)*($C6/(VLOOKUP($B6,KITS!$A$3:$D$150,4,FALSE))),"")

Both multiply the IBU/EBC by the normal weight of the tin (via lookup) divided by the amount used. seems to work!

 

[ianh]

Hi gc

The calculation involving kits is based on the full kit and picks up values from the Kits worksheet. What you are doing is correct if only using part of the kit.

 

[OzPaleAle]

I'm pretty new to brewing and I have got a Fat Yak Clone Kit from Brewcraft that I put on yesterday, I thought I would put the values of the kit into the spreadsheet to get to know how to use the form.
I got the results pictured below, I had a little bit of a newbie boil over issue with the Hop additions so that area is a bit sketchy but I noticed the spreadsheet shows the expected %Alc as 3%, not sure if I should be adding some more DME or not to boost %Alc, the OG was 1032 when I measured it.

Thanks

 

[Scobieb]

Thanks so much for this. Having just started to brew with dry extract its been a lifesaver.

One question, what does the HCF control do?

 

[carniebrew]

Hop Concentration Factor. It helps if you're doing boils under say 8 litres, in guiding you as to how much less your IBU will be because of the small volume boil. It seems most of us, including Ian, have agreed that when doing anything over 8 litre boils you can leave it off, as the amount of hops don't really need changing.

 

[Summy]

Thanks very much for the spreadsheet, will come in very useful

Cheers

 

[Summy]

Not sure if it's been asked before but could we have Ella added as a hop variety please?

Thanks in advance

 

[DU99]

you can edit yourself...long as you have the details of the hops Alpha acids (%) 14—16

 

[carniebrew]

Yeah just go to the "Hops" tab, insert a row where Ella belongs alphabetically, enter the details and it'll then appear in the drop down box on the main sheet.

 

[PeteBaudo]

G'day Ian

I assume given the recent posts to the thread that the spreadsheet attached at the beginning of the thread is the current one?

It was posted several years ago so I just wanted to double check if you had made any recent changes?

Regards

Pete

 

[brente1982]

No recent changes, but like the above posts say, you can manually enter data yourself. Most kit cans these days come with the data provided so entering it isnt a problem. And hops you can manually adjust also.

 

[carniebrew]

G'day Ian

I assume given the recent posts to the thread that the spreadsheet attached at the beginning of the thread is the current one?

It was posted several years ago so I just wanted to double check if you had made any recent changes?

Regards

Pete

Yep, Ian is able to edit his original post when he comes up with a new version. v4.1 is quite new, and the latest we've seen.

 

[ianh]

G'day Ian

I assume given the recent posts to the thread that the spreadsheet attached at the beginning of the thread is the current one?

It was posted several years ago so I just wanted to double check if you had made any recent changes?

Regards

Pete

Hi pete

Welcome to the forum.

Yes the one in the first post is the current version. I got one of the Moderators to edit the first post for the latest version.

Hope you find it useful.