IanH Spreadsheet

[Lab Cat]

I've been using a basic ingredients addition app on my phone to log my recipes, as they sometimes stray a little from the coopers version - I can't always get the hops coopers specify, so use an available sub.

I've just revisited the IanH Spreadsheet (v4.1 downloaded). It was too much info for me a while back with KK brewing, but plugging a few of my recent coopers recipes in, it looks really useful for helping with my hop boil schedules - once I work out what IBUs are my preferred range. Adding some of my overhopped recipes to the sheet should help with that. Also handy for OG/FG guides.

But how do you add ingredients that haven't been included? My last brew is the Carib Siesta, but with Lemondrop hops, which aren't listed on the sheet. Do you just add a new hop name and AA reference to the hops tab? I know some people have added and changed the look etc, but I'm cautious of doing that, given there's a lot of inbuilt formulas at work.

Does anyone know how to add a hop steep to this sheet, the recipe formula page doesn't allow for that. I'm trying to record my recipes faithfully, which my other methods don't do very well.

cheers

[Shamus O'Sean]

3 hours ago, Lab Rat said:

But how do you add ingredients that haven't been included? My last brew is the Carib Siesta, but with Lemondrop hops, which aren't listed on the sheet. Do you just add a new hop name and AA reference to the hops tab? I know some people have added and changed the look etc, but I'm cautious of doing that, given there's a lot of inbuilt formulas at work.

Does anyone know how to add a hop steep to this sheet, the recipe formula page doesn't allow for that. I'm trying to record my recipes faithfully, which my other methods don't do very well.

Yep.  Just insert a row, alphabetically.  Type in the hop name and the alpha.  The other cells about styles,  substitutions and descriptions are optional.  I have added in Lemondrop, Vic Secret and a couple of others.  When I make these changes I save the file as a new version (4.1.3, 4, 5, 6, etc).

I also did this under the KITS tab with the Thomas Coopers and the Mr Beer Series cans that were not in the 4.1 version of the spreadsheet.

I do not know how to adjust the sheet for hop steeps.  I think some people just put in the hop and give it a short (say 5 minute) boil to add some IBU's for flame-out hop additions (still above 80°C).  You might need to do some research to find out the equivalent short boil duration for the corresponding IBU's extracted from a flame-out hop steep.  Steeps once the wort falls below about 80°C are not likely to add many perceptible IBU's. 

[Lab Cat]

Cheers

The hop steeps addition isn't for the IBUs, they are pretty negligible. It's to be able to record it. Other apps have a whirlpool option for hops additions, which would work fine, but this spreadsheet doesn't have it.

[Shamus O'Sean]

1 hour ago, Lab Rat said:

Cheers

The hop steeps addition isn't for the IBUs, they are pretty negligible. It's to be able to record it. Other apps have a whirlpool option for hops additions, which would work fine, but this spreadsheet doesn't have it.

Thanks for raising this matter @Lab Rat, it made me look much harder at the spreadsheet and I now have a better understanding of how it works and can make better use of it myself.

If you just want to be able to record a whirlpool addition and are not too fussed about what IBU's it might add, read on.

You can input d or D into the boil time for a dry hop addition.  So I tried to input w or W in the same cell to denote a whirlpool addition.  The spreadsheet did not like that.  Some N/A's appeared elsewhere on the sheet.  I thought, why does this not happen when D is in the boil time cell?

Looking at the formula in cell G26 (of the MAIN tab), it includes a function that allows d or D to be entered without affecting anything else.  Therefore I changed this formula to also allow w or W.  The new formula is:

=IF(OR(ISBLANK(C26),C26=0,ISBLANK(F26),F26=0,F26="d",F26="D",F26="w",F26="W"),"",((VLOOKUP($F26,$AO$1:$AP$20,2,TRUE)*C26*E26*10)/C$3)/J$24)

To copy the formula to the spreadsheet

1. Click and drag to select the whole of the above formula, hover over the highlighted text, right click and select Copy. 
2. Go to the MAIN tab of the spreadsheet.  
3. Click in cell G26 (the top cell of the hops IBU calculator).
4. Go to the formula bar near the top of the screen - the formula is similar to the one above.
5. Click and drag to select the whole of the formula in the formula bar.
6. Hover the mouse over the highlighted formula, right click and select paste.
7. Press Enter.
8. Right click on cell G26.
9. Select Copy
10. Click and drag to select cells G27 to G33.
11. Right click over the highlighted cells and select Paste.

You should now be able to enter W in the Boil min column without affecting anything else. 

If you use the BREWDAY tab, you might like to make a similar alteration to get it to show WHIRLPOOL for W or w additions.  Here's how you do that:

Looking at the formula in cell G20 (of the BREWDAY tab), it includes a function that either shows the boil time or DRY HOPPED if d or D is entered on the MAIN tab.  The formula has to be replaced to also show WHIRLPOOL if W or w is entered on the MAIN tab.  The new formula is:

=IF(ISNUMBER(MAIN!F26),MAIN!F26,IF(OR((MAIN!F26)="d",(MAIN!F26)="D"),"DRY HOPPED",IF(OR((MAIN!F26)="w",(MAIN!F26)="W"),"WHIRLPOOL","")))

To copy the formula to the spreadsheet

1. Click and drag to select the whole of the above formula, hover over the highlighted text, right click and select Copy. 
2. Go to the BREWDAY tab of the spreadsheet.  
3. Click in cell G20.
4. Go to the formula bar near the top of the screen - the formula is similar to the one above.
5. Click and drag to select the whole of the formula in the formula bar.
6. Hover the mouse over the highlighted formula, right click and select paste.
7. Press Enter.
8. Right click on cell G20.
9. Select Copy
10. Click and drag to select cells G21 to G27.
11. Right click over the highlighted cells and select Paste.

If you have entered W or w in the Boil min column of the MAIN tab, the BREWDAY tab should now show WHIRLPOOL as the addition type under the "HOPS add" section of the BREWDAY tab.

[kmar92]

Thanks for that Shamus!

[Journeyman]

15 hours ago, Shamus O'Sean said:

Looking at the formula in cell G20 (of the BREWDAY tab), it includes a function that either shows the boil time or DRY HOPPED if d or D is entered on the MAIN tab.  The formula has to be replaced to also show WHIRLPOOL if W or w is entered on the MAIN tab.  The new formula is:

=IF(ISNUMBER(MAIN!F26),MAIN!F26,IF(OR((MAIN!F26)="d",(MAIN!F26)="D"),"DRY HOPPED",IF(OR((MAIN!F26)="w",(MAIN!F26)="W"),"WHIRLPOOL","")))

I think that formula should not have the ! in it - it generates a #NAME error. Looking at the original that is meant to be a '.' not '!'

So... =IF(ISNUMBER($MAIN.F26),$MAIN.F26,IF(OR(($MAIN.F26)="d",($MAIN.F26)="D"),"DRY HOPPED",IF(OR(($MAIN.F26)="w",($MAIN.F26)="W"),"WHIRLPOOL","")))

[Lab Cat]

Does anyone use the Brews tab? This is a good place to list your brews which are saved in recipes, but can add things likes bottling date and tasting notes.

Can recipe be imported here, seems a chore to write them out again.

[Shamus O'Sean]

12 hours ago, Journeyman said:

I think that formula should not have the ! in it - it generates a #NAME error. Looking at the original that is meant to be a '.' not '!'

So... =IF(ISNUMBER($MAIN.F26),$MAIN.F26,IF(OR(($MAIN.F26)="d",($MAIN.F26)="D"),"DRY HOPPED",IF(OR(($MAIN.F26)="w",($MAIN.F26)="W"),"WHIRLPOOL","")))

This may depend on what you are opening the file in.  

In excel the syntax for referring to a cell in another tab is TABNAME!    If you are using something else to open and use this file it might be different.  I did not get an error in Excel 2010.

If the change you made makes it work for you all good.

[Shamus O'Sean]

7 hours ago, Lab Rat said:

Does anyone use the Brews tab? This is a good place to list your brews which are saved in recipes, but can add things likes bottling date and tasting notes.

Can recipe be imported here, seems a chore to write them out again.

I had not used it before because I did not understand how it worked.  However, it looks like it might be quite useful.

You first need to create the recipe you want to brew in the MAIN tab.

Then you would think you just click on Make Recipe.  However, if you do click on Make Recipe, but you do not have all of the ingredients in your Inventory, the spreadsheet will not let you add the recipe to the BREWS tab

You first need to have all the ingredients for the recipe in your Inventory.  Assuming you have the ingredients, and are about to make the brew, click on Inventory and add the relevant ingredients.

Once that is done, click on MAKE RECIPE.  If you missed any ingredients, the spreadsheet will let you know and you will have to go back and add them.  If you have them all in your inventory, the spreadsheet will populate a row of the BREWS tab with the brew.  You can then manually add other data like temperatures and measured OG and FG.  Note that when you "MAKE RECIPE" it deducts the relevant ingredients from your Inventory.

[Journeyman]

10 hours ago, Shamus O'Sean said:

This may depend on what you are opening the file in.  

In excel the syntax for referring to a cell in another tab is TABNAME!    If you are using something else to open and use this file it might be different.  I did not get an error in Excel 2010.

If the change you made makes it work for you all good.

I was once an Excel formulas guy and so I didn't even see the ! as a possible error. But because I couldn't find the reason for the #Name error I went back to the original download and started comparing formulas and in the original, untouched (by me) version, it uses the '.' for sheet names.

Just curious - in your Excel, if you alter the ! to a . does the formula still run or give an error?

[Shamus O'Sean]

4 hours ago, Journeyman said:

I was once an Excel formulas guy and so I didn't even see the ! as a possible error. But because I couldn't find the reason for the #Name error I went back to the original download and started comparing formulas and in the original, untouched (by me) version, it uses the '.' for sheet names.

Just curious - in your Excel, if you alter the ! to a . does the formula still run or give an error?

I'll give it a try tonight 

[Shamus O'Sean]

Hey @Journeyman,

Gave this a try tonight.  When I made the !'s into .'s I got the #NAME error.  The other difference I see in your formula is a "$" in front of the MAIN tab name.  When I changed the reference to the tab name to be $MAIN. the sheet really cracked it, gave me options to fix, but when I clicked out of them, it just came back with the #NAME error.

I am no expert and could never reproduce the IanH spreadsheet from scratch.  However, there might be something in there that means my formula works for me and your formula works for you. I will never really know.  I hope my advice has not stuffed up anybodies IanH spreadsheet.

[Lab Cat]

I won't go near the formulas. I only did some simply stuff like change the chart and background colour, and renamed the spreadsheets file name, and the whole sheet was borked - wouldn't add or find saved recipes, nothing worked after that. Had to download again.

[Shamus O'Sean]

5 hours ago, Lab Rat said:

I won't go near the formulas. I only did some simply stuff like change the chart and background colour, and renamed the spreadsheets file name, and the whole sheet was borked - wouldn't add or find saved recipes, nothing worked after that. Had to download again.

Fair enough.  I cannot say that my adjusted version of the spreadsheet still does everything it is meant to do because I do not fully know everything the original spreadsheet is meant to do.

A shopping list function would have been good.  For example being able to plan for a brew on the weekend, and based on your Inventory, having a shopping list of the things you need to buy beforehand printed out.

[Journeyman]

Hmm... So I went and downloaded the original again, just to check. It has $MAIN.G20 in it... BUT, it is this one, 2016 Kit &Extract Beer Designer V4.2 from https://aussiehomebrewer.com/attachments/2016-kit-amp-extract-beer-designer-v4-2-xls.87958/

I wonder if it got changed from the earlier versions?

New project for Mark - see if I can back engineer the SS and see what it is doing. Then maybe I can play with it and even get a shopping list etc. I've got a VM set up for Windows and Office but it would be nice to get a version that works in Libre just as it does in Excel.

 Because I haven't got enough to do... 

I guess it will keep me off fakebook.

EDIT: I went and found v4.1 of the SS and it also has $MAIN.G20 in the formula. So...???

Edited November 12, 2019 by Journeyman

[Beerlust]

Hi guys.

Early on I found some glitches in the spreadsheet when trying to save recipes etc. so I don't bother with it & just use a separate word document with an endless table configuration to document my brews. Once you've put a few brews in, it's easy to copy & paste data from one recipe to the next in a lot of cases.

In regard to the whirlpool addition Shamus has shown how to add into the spreadsheet, it is harmless if simply using the word for your reference, but if you plan to physically follow the whirlpool process in your brewing, then your IBU calculations will also require adjustments to match the reality of what you are doing in a practical sense. This will not only include added IBU's from the whirlpool addition itself, but also increases in IBU's from those hops already used in the boil as you won't be cooling them as quickly after flameout as you normally would either.

Just some food for thought.

Lusty.

[Yuley]

2 hours ago, Journeyman said:

Hmm... So I went and downloaded the original again, just to check. It has $MAIN.G20 in it... BUT, it is this one, 2016 Kit &Extract Beer Designer V4.2 from https://aussiehomebrewer.com/attachments/2016-kit-amp-extract-beer-designer-v4-2-xls.87958/

I wonder if it got changed from the earlier versions?

This has me curious about what changed in version 4.2 now (I am using 4.1)

[Aussiekraut]

51 minutes ago, Yuley said:

This has me curious about what changed in version 4.2 now (I am using 4.1)

The bigger question is how I get my changes into the new version? I modified some of things like wrong IBU values for kits or missing kits like the Coopers Euro Lager. I adjusted some of the hops alpha acid values to reflect what I have, I adjusted attenuation of yeast, etc. I'd hate having to do this all over again. The recipes, I can copy and paste but I cannot recall all of the other changes I made. Guess I'll stick with the old version for now.

 

[Yuley]

24 minutes ago, Aussiekraut said:

The bigger question is how I get my changes into the new version? 

 

Agreed! By the sounds of things I am not as far editing the old version as you... but still a process to redo it all in the newer version of the spready. 

Initially I highlighted any cells that I changed, so they will be easy. However more recently I got a little slack.

[Journeyman]

If it's only data you changed, you could simply copy/paste all the data columns/rows.

There used to be a consolidate function in Excel - not sure if it works between files or only between sheets in the same file.

[RoyalDave]

Being fairly new to proper extract brewing I am still discovering how to use specialty grains.

Often when you read articles on how to brew certain beers you'll read something like, grain X should make up no more than 3% of the grain bill.

Idiot me has been scribbling away with pen and paper to work out how much I should add - and it only just clicked that this spreadsheet which I've been using for a year now should do the job for me.

So I've added this in.

One question I have though, and some googling has not really found the answer for me, is do adjuncts such as sugar and honey count towards the grain bill in this context?

I'm not interested in OG/FG calculations etc - I've always assumed that these discussions are focusing on flavour and colour etc - but how would you account for these in the total bill (if indeed you need to?)

As I did this for my personal use I'm quite happy to ignore as I rarely use the ADJ boxes - but I would have a stab at including them if someone can shed some more light - only posting here because I discovered this thread while googling - hence I've included the file if anyone is interested - its is a work in progress though so may amend.

BTW Formula takes into account an LME/DME ratio of 0.82 - again if anyone feels strongly about this number let me know.

I've also added in a lot of LME, as well as adding in the whirlpool option as offered by Shamus O'Sean.

 

2022 Kit & Extract Beer Designer V4.3 BETA.xls

[iBooz2]

Yes % of grain bill definitely has its place and I would have liked a column like that back in the day when I used that SS a lot for early K & K recipes. 

Have you cross-checked your calculations?  lets say compared to how it works out in the free version of Brew-father to see if you are close enough to use your % of grain bill formula in the SS?

[RoyalDave]

On 2/3/2022 at 7:23 PM, iBooz2 said:

Yes % of grain bill definitely has its place and I would have liked a column like that back in the day when I used that SS a lot for early K & K recipes. 

Have you cross-checked your calculations?  lets say compared to how it works out in the free version of Brew-father to see if you are close enough to use your % of grain bill formula in the SS?

I have now.

Brewfather simply adds the weight of all malts - whether liquid, dry or grain in considering % of grain bill - I think this is probably wrong.

If it is correct that you should consider 80% of the weight of LME in working this out then the spreadsheet is more accurate.

With only a brief look at recipes, it looks likes Brewfather also considers the adjuncts to be part of the the overall grain bill - something I have not done in the spreadsheet. I will need to research this a bit more for the best approach.

 

[RoyalDave]

Sod it, I've been overthinking it.

When people are talking % of grain bill they're talking about the dry ingredients that go into making a beer.

Therefore I'll just add them all up. I do however feel strongly about reducing the LME weight in considering this, so the ratio stays.

However for those who want their % to match up to brewfather this ratio is easily changed by adjusting the ratio which I've included in the MALT sheet - just change it to 1.

Final version.

2022 Kit & Extract Beer Designer V4.3.xls