Kit And Extract Beer Spreadsheet

[kelbygreen]

nop that didnt work as you say even if I select another hop it stays the same. Should I edit the hop list and input my AA% in there not enter it manualy in when selecting the hop would this work?

 

[jzani]

nop that didnt work as you say even if I select another hop it stays the same. Should I edit the hop list and input my AA% in there not enter it manualy in when selecting the hop would this work?

I made the mistake of manually inputting hop values and it caused me a few problems. Ended up re-downloading the spreadsheet and discovering the hop values down the bottom. Now I only adjust the hop values below the blue line and it seems to work.

 

[ianh]

nop that didnt work as you say even if I select another hop it stays the same. Should I edit the hop list and input my AA% in there not enter it manualy in when selecting the hop would this work?

You should have a formula like =IF(ISBLANK($B26),"",VLOOKUP($B26,$S$100:$T$179,2,FALSE)) in cells E26-E31. This formula looks up cells S100-T179 to find the Hop name and corresponding %AA.

Hope that helps

cheers

Ian

 

[davboyle1]

G'day Ian,

Just wanted to say good work and cheers for the spreadsheet.

Has really helped me get a handle on the whole boil and additions, opening the door to some calculated experimentation.

cheers,

David

 

[ianh]

G'day Ian,

Just wanted to say good work and cheers for the spreadsheet.

Has really helped me get a handle on the whole boil and additions, opening the door to some calculated experimentation.

cheers,

David

You are welcome David. I did it to help me understand the homebrewing processes associated with kits and extracts. Hopefully will have the latest update before the end of the month, fix a few problems.

cheers

Ian

 

[davboyle1]

Cheers, i look forward to the next edition, just like the brew in my fermenter.

Thanks again,

David

 

[ianh]

I have updated the Kit & Extract Beer Designer spreadsheet to version 2.3. I have produced two versions 2.3a which has the AABC styles and 2.3b which has the BJCP styles as in the previous versions.

The update to the new version is mainly to fix problems which existed in version 2.2 plus I have just started using a Refractometer to calculate my sg's and have included some columns on the Brews worksheet for that.

Problems fixed include Inventory weights for Dextrose and Maltodextrin plus the Inventory yeast total. Fixed the problem with Inventory weights due to rounding errors, you thought you had 1.0 kg but spreadsheet had say 0.99999995 and then said you had insufficient when you did a recipe requiring 1.0kg.

There was a problem with the Hop drop-down list not covering all the hops listed.

Fixed a problem when you saved a recipe that already existed it was saved in the wrong place and consequently recipes appeared dotted round the Recipes worksheet.

View attachment Kit___Extract_Beer_Designer_V2.3b.xls

You will need to copy your existing Brews and Recipes to the new spreadsheet.

I left a couple of brews as examples on the Brews worksheet. If you are not going to use a Refractometer for sg's you can delete columns T to Y. Delete the 2 existing brews and then copy the rows from your old Brews worksheet to the new one. There are formulas and formatting in the Rows down to brew 100, if you have more brews than this you will need to copy the rows down.

If you leave columns T to Y on the Brews worksheet,then you will need copy your Brews across in two lots. First copy the Rows but only columns A to S and paste into the new Brews worksheet. Then copy the rows and columns T to Y from the old worksheet and past in Columns Z to AE on the new worksheet.

The recipes can be copied across to the new spreadsheet Recipes worksheet, do not transfer any that that appear to be located in odd spots, they should all be in sequence in Columns E to AO.

One done save your spreadsheet with a new name.

cheers

Ian

Edit:

If you are using a version with the refractometer columns on the Brews worksheet, you need to change a line in the makerecipe macro to get the dextrose for bulk priming into the correct column. Near the end of the makerecipe macro is the line

ActiveCell.Offset(0, 19).Value = Worksheets("MAIN").Range("m19").Value

this needs to be changed to

ActiveCell.Offset(0, 25).Value = Worksheets("MAIN").Range("m19").Value

If you have problems doing this, you can just delete the dex bulk prime value in column V and put it in column AB each time you run the make recipe macro.

 

[ianh]

Here is version 2.3a of the spreadsheet for those who want to use the AABC style guidelines.

View attachment Kit___Extract_Beer_Designer_V2.3a.xls

Please note that some Styles have different names. So when you get a recipe if may give you all #### for the style data. It is necessary to reselect the new AABC style and then resave the recipe. Also some of the Style parameters are slightly different so you may have to slightly adjust the ingredients to fit the new Style.

Also note some old recipe had incorrect hop names, again reselect correct name and save.

See above post for transferring Brews and Recipes across.

cheers

Ian

 

[Lodan]

Ian,

Just a quick thanks for your spreadsheet; it's a great help to me in my understanding, balancing and styling of kit and extract brewing

Cheers :icon_cheers:
Lodan

 

[mkstalen]

I love this Ian, really helps you get a great handle on the whole process.

For the next revision is there any chance you can add in Lactose as another fermentable under the grains section under Maltodextrose. I do a few ciders/ginger beers and like them a little sweeter, so this would really help out.

cheers,
Mike

 

[Yob]

Here is version 2.3a of the spreadsheet for those who want to use the AABC style guidelines.

View attachment 38825

Awesome stuff Ian, ben screwing about with this for a while now and used it on my last brew and it came in exactly as the spreadsheet said (Og 1052 fg 1012)

Really good tool mate, well done indeed

Cheers
:icon_cheers:

 

[bignath]

Ian, your spreadsheet is amazing. I have been wanting something like for a long time!

Just one thing though, and my apologies if i am asking something that has been asked before, but i can't get most of the buttons at the bottom of the main page to work. I can create a new recipe, but can't save it, can't delete any of the others, can't make recipe etc.. I keep getting this message:

"compile error:
can't find project or library"

how do i get these functions to work? Otherwise this is fantastic!!

cheers,

bignath

 

[bignath]

Ian, your spreadsheet is amazing. I have been wanting something like for a long time!

Just one thing though, and my apologies if i am asking something that has been asked before, but i can't get most of the buttons at the bottom of the main page to work. I can create a new recipe, but can't save it, can't delete any of the others, can't make recipe etc.. I keep getting this message:

"compile error:
can't find project or library"

how do i get these functions to work? Otherwise this is fantastic!!

cheers,

bignath



EDIT:
Don't worry mate! Sorted it out!
Went to the Visual Basic by ALT + F11, and checked the macros under Tools-References, and noticed there was a listing with MISSING LIBRARY and there was a check box in it. I deselected it, and now all the buttons work.

Your spreadsheet is amazing!!!

 

[the_yobbo]

EDIT:
Don't worry mate! Sorted it out!
Went to the Visual Basic by ALT + F11, and checked the macros under Tools-References, and noticed there was a listing with MISSING LIBRARY and there was a check box in it. I deselected it, and now all the buttons work.

Your spreadsheet is amazing!!!

AWESOME!!! at last I can save my recipes. Woohoo.


Ian, awesome spreadsheet yet again.
As a request if you decided to work on it further, adding weight of priming sugar required for natural carbing kegs for a set CO2 vols, and the associated percentage alcohol. I beleive less sugar is used when natural carbonating kegs in comparison to bulk priming bottles.

 

[ianh]

AWESOME!!! at last I can save my recipes. Woohoo.


Ian, awesome spreadsheet yet again.
As a request if you decided to work on it further, adding weight of priming sugar required for natural carbing kegs for a set CO2 vols, and the associated percentage alcohol. I beleive less sugar is used when natural carbonating kegs in comparison to bulk priming bottles.

Thanks for the comments. Your natural carbing of kegs is already there. Click on cell N18 and change the bottle to keg and N19 and change the dextrose to sugar.

cheers

Ian

 

[the_yobbo]

Thanks for the comments. Your natural carbing of kegs is already there. Click on cell N18 and change the bottle to keg and N19 and change the dextrose to sugar.

cheers

Ian

Ian, I love you.

I can't beleive thats been there the whole time. Never thought there'd be a dropdown menu on that spot.

Playing with volumes and keg priming values, I assume the weight of dex required is based on the total brew volume and not what a cornie (or other keg at that matter) can hold.
Due to the difference in priming dex required for bottling and kegging, if intending to bottle the excess brew that can't fit in the keg, I assume you would:
1) Adjust the ferment volume on spreadsheet to the volume held by the keg (18-19L) and add dextrose stated directly to keg.
2) Remaining brew can then be bottled and primed individually. (Or re-adjust ferment volume to volume left after filling keg and bulk prime remaining brew before bottling).

Seems logical enough to me, but please confirm or correct my assumptions.

 

[ianh]

Ian, I love you.

I can't beleive thats been there the whole time. Never thought there'd be a dropdown menu on that spot.

Playing with volumes and keg priming values, I assume the weight of dex required is based on the total brew volume and not what a cornie (or other keg at that matter) can hold.
Due to the difference in priming dex required for bottling and kegging, if intending to bottle the excess brew that can't fit in the keg, I assume you would:
1) Adjust the ferment volume on spreadsheet to the volume held by the keg (18-19L) and add dextrose stated directly to keg.
2) Remaining brew can then be bottled and primed individually. (Or re-adjust ferment volume to volume left after filling keg and bulk prime remaining brew before bottling).

Seems logical enough to me, but please confirm or correct my assumptions.

Sounds reasonable to me too. The light green coloured cells contain a drop-down list

 

[the_yobbo]

In regards to the boil volume and LME quantity required to acheive 1040 SG....
If doing a 6L boil volume, do you start with 6L of water and then add DME/LME? or Do you start with ~5.5L of water, add the specified DME/LME then top up to 6L?
The first option seems logical, but wasn't sure on what the calculations were based on.

Just wanted to make sure I get it right for my second brew of Niells Centenamillo Ale.

 

[kbe]

In regards to the boil volume and LME quantity required to acheive 1040 SG....
If doing a 6L boil volume, do you start with 6L of water and then add DME/LME? or Do you start with ~5.5L of water, add the specified DME/LME then top up to 6L?
The first option seems logical, but wasn't sure on what the calculations were based on.

Just wanted to make sure I get it right for my second brew of Niells Centenamillo Ale.

The way I read it, is that you make your boil volume up to that amount and then add the DME/LME.

 

[Nodrog]

hi Ian,

I just used your spreadsheet for the 1st time, and it was bloody fantastic, thankyou indeed. Not only did it help building a recipe, but also with a great simple overview of the process itself.

There were 2 things which as a beginner I would have really like to have access to, so for the sake of continuous improvement I thought I'd mention them. I steeped some caramalt first, and used this as the base for the boil volume, but it had an unknown SG, and was warm. Trial and error got me close, ish, but a calculator that would let us enter the current SG reading, temp, and volume, and then show the amount of DME (g) that should be added to bring it to the target 1.040 would have been great.

is such a thing easily worked out?
I am pretty sure i've seen the calculations somewhere?

regardless, thanks indeed, I now have 20 litres of some heavenly smelling juice quietly doing its stuff downstairs in the garage.