Kit And Extract Beer Spreadsheet

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