Biab Beer Designer Spreadsheet

[ianh]

Thanks Ian....much appreciated. I've just cut and paste across recipes from my v1.2. seems to work fine. Something that other users should note is that you have tweaked the settings in the BIAB set-up sheet. If users are using these as a default or have tweaked for their own circumstances then they will need to copy their old settings from v1.2. In a future version maybe you could add a return to default settings button. I wonder if any other users would like to share recipes that they have entered. In particular I am thinking of some of the well-known and loved recipes from the AHB database?

Again, thanks for your work.

Cheers,
Oakers.

Hi Oakers

Thanks. Yes I changed the strike factor from 0.30 to 0.32 and the absorption factor from 0.50 to 0.48 to adjust things for my own set up. People will need to determine the factors on the BIAB worksheet for their own set ups. All the blue cells are for inputs.

Best way is to average the results across a number of brews (minimum 5) to determine the factors.

cheers

Ian

 

[hellbent]

A work of art Ianh and congratulations on your effort.

I have just one problem here, you say adjustments can be made with anything on the blue lines, well it happens that I cant make any adjustments to the fermenter volume, grain weight, or for that matter anything on the blue lines. I have tried with Open Office and trial MS Excell for no result, any help or ideas?

 

[AndrewQLD]

After a few requests I've pinned this topic at the top of the all grain and Partials forum, as always thanks Ian for all your work.

Cheers
Andrew

 

[keifer33]

A work of art Ianh and congratulations on your effort.

I have just one problem here, you say adjustments can be made with anything on the blue lines, well it happens that I cant make any adjustments to the fermenter volume, grain weight, or for that matter anything on the blue lines. I have tried with Open Office and trial MS Excell for no result, any help or ideas?

You need to enable the macros for it to run. Might be a yellow line with 'Enable Macros'. If not you can go into the trust centre and enable permanently.

 

[ianh]

OK Crusty has found an error, when saving a new recipe with No Chill it does not include the No Chill (I only use chill so didn't find it).

Two ways to fix, easy way is to go to the recipes and put an N in the cell above the yeast. Need to do that for each new No Chill Recipe, existing recipes are OK.

Permanent fix requires changing the saverecipe macro, to bring up macros hold down the ALT key and hit F11, then Tools Macros and select saverecipe macro

Near the end of the saverecipe macro should find these lines

'transfer recipe data
z = 22
Do
If z > 11 Then
ActiveCell.Offset(z, 0) = sname(z): ActiveCell.Offset(z, 1) = swt(z): ActiveCell.Offset(z, 2) = stime(z)
ElseIf z > 2 And z < 11 Then

the If z > 11 Then needs to be changed to If z > 9 Then

I think that should fix the problem

 

[Crusty]

OK Crusty has found an error, when saving a new recipe with No Chill it does not include the No Chill (I only use chill so didn't find it).

Two ways to fix, easy way is to go to the recipes and put an N in the cell above the yeast. Need to do that for each new No Chill Recipe, existing recipes are OK.

Permanent fix requires changing the saverecipe macro, to bring up macros hold down the ALT key and hit F11, then Tools Macros and select saverecipe macro

Near the end of the saverecipe macro should find these lines

'transfer recipe data
z = 22
Do
If z > 11 Then
ActiveCell.Offset(z, 0) = sname(z): ActiveCell.Offset(z, 1) = swt(z): ActiveCell.Offset(z, 2) = stime(z)
ElseIf z > 2 And z < 11 Then

the If z > 11 Then needs to be changed to If z > 9 Then

I think that should fix the problem

Followed the permanent fix guideline.
I changed the if z > 11 then to if z > 9 then
Problem solved & recipes are now saving in the option you choose, chill or no chill.
Great work Ian.

 

[stux]

Hi All

Been saying I'll upload the latest version for awhile, so here it is

View attachment 59643

Changes since V1.2

Fixed a calculation error on the BIAB worksheet.

Fixed the macro error for subtracting hops from the inventory.

Added FWH to hop boil times, can be used with chill or no chill.


Cheers
Ian

Hehe, the 2012 release

Happy New Year

 

[bigbanko]

How do you add inventory.

When selecting the inventory button and select Add Inventory I get runtime error 94 - Invalid use of null.

 

[parktho]

How did you come up with the factor 0.994837 to convert from points per pound per gallon into metric?

 

[ianh]

How did you come up with the factor 0.994837 to convert from points per pound per gallon into metric?

Hi parktho

Sorry I don't remember and I can't find a website, if I google the value of the factor it comes up with a website which requires a sign in. It was originally used in my Kit & Extract Spreadsheet for speciality grains.

 

[taztiger]

Hi Folks

Can someone tell me what i have to do when transferring recipes between versions. I have seen the big red warning telling me not to add anything straight into the recipes tab, so where do i cut and paste to?

 

[ianh]

Hi Folks

Can someone tell me what i have to do when transferring recipes between versions. I have seen the big red warning telling me not to add anything straight into the recipes tab, so where do i cut and paste to?

It should be Ok, just make sure the first lot of individual recipes go down to row 23. The warning was put there because extra lines were added to the recipes between some older versions.

 

[taztiger]

It should be Ok, just make sure the first lot of individual recipes go down to row 23. The warning was put there because extra lines were added to the recipes between some older versions.

Ok will try that.

Thanks again Ian.

 

[Drew]

I'm doing my first AG in a long time, and this spreadsheet looks really fun useful

Does "Approx Mash Volume" include the water added from Sparging?

 

[ianh]

I'm doing my first AG in a long time, and this spreadsheet looks really fun useful

Does "Approx Mash Volume" include the water added from Sparging?

The spreadsheet is intended for BIAB thus does not include sparging.

 

[joel_cabban]

hey guys sorry to dig up an old thread, but i'm getting my brew gear out of storage, and looking at getting into the BIAB this time, is this program (or perhaps an update) still available?

cheers
Joel

 

[Lord Raja Goomba I]

This is still available, and beermate (runs best on Windblows) is also available, free, does BIAB and no-chill. Both are great.

Goomba

 

[Mainiac]

Ianh

I love the KK sheet it tought me a lot about brewing kits.
I am just starting my BIAB build and look forward to using this sheet.


Thanks again Ianh

 

[troyedwards]

I am using Beersmith 2, and I find it ok for transferring recipoies from brewbuilder on the HB store I use.

I don't think I fully use BS2 to its full extent as I don't really give a toss about percentage efficiency and water loss and every other factor than can be recorded.

I do BIAB and so I guess what are the advantages of this spreadsheet over the BS2 program as Iam for all things simple and easy, and if it turns out the xls file is easier to use then I will make the switch?

 

[Cynic13]

Hi Ianh
How do I go about adding Pale 2-row to the Grains tab?

 

[ianh]

Hi Ianh
How do I go about adding Pale 2-row to the Grains tab?

Hi Cynic

Welcome to the forum.

You just need the EBC and potential values for Pale 2 Row grain then just insert a row in the Grains worksheet and the name and values..
Can do that for any ingredients.

 

[OzPaleAle]

A bit of BIAB on Heston tonight, blessed by a priest no less.

 

[jotaigna]

I saw that ozpale. When Heston popped the fermenter lid and just scooped with a glass to taste, I cringed hard!

 

[Barbarossa]

Has anyone had any luck with this spreadsheet on a mac or openoffice? I've tried on both and can't get it to work at all. For example, when changing the beer style in the drop down menu nothing changes in the grains cells, or when changing the fermenter volume nothing else changes.

 

[meathead]

Great software, thanks for providing it
Is it possible to scale recipes up with it?
Sorry if that's been answered

 

[ianh]

Not sure how you want to scale up but if you select pot rather than urn you can basically put in any size for your initial volume.

Sorry for delay replying but I'm O/S on holiday ATM.

 

[solipsist]

Hey Ianh. I really appreciate the work you've put into this spreadsheet. Is there any way to add more lines for hops? I'm fine with reading/editing macros if you give me the basic gist of where to look.

Cheers.

 

[ianh]

Thanks. To add more hop lines

On Main worksheet copy down the last hop row - hop columns A to H for the number you want to add.
You will then need to alter IBU formula in cell K14
You then need to add the Spin Buttons to the new D & F columns (Developer - Insert - Spin buttons), not sure whether you number each spin button or its done autmatically.
You then need to add macros for each spin button. Developer - Macros - sheet1 should find hop button macros for weight and time, just need to copy and change cell references and spin button number.
On the Brewday worksheet you will need to insert and some more lines for hops.

You need need to edit the PasteComment macro find line Sheets("brewday").Range("b1:i50").Copy and change the i50 value to incorporate the additional hop rows.

You need to edit the makerecipe macro. This where most changes need to be made.
You will need to add some more sname variable values for your extra hop rows and then you need to alter the section underneath after the comment
' because you can have the same hops in any of the 8 slots, need total for each one
you need to total the weight of each hop so the changes will depend on the number of rows you added, gets more complicated the more rows you add.
In the inventory bit of the macro there are two loops For x = 2 to 22 you will need to change the 22 value in each ( 22 + No of extra rows)
Then there is a line near the end of the macro ActiveCell.Offset(0, 3).Value = need to add the extra sname variables created. This just totals the hops to add to the Brew worksheet.

I think that's it, good luck.

 

[poggor]

Hi Ian - great spreadsheet- I am running it on a mac and it all seems to be fine except the "make recipe" function. when i click it says it's removing the ingredients from the inventory but then i get an error message "runtime error 9 subscript out of range" and nothing is added to the brews sheet.

seems to be this line it is objecting to:

sheets("brewday").Range("b1:i50").Copy

i know nothing about visual basic
any ideas?

cheers!

g

 

[ianh]

Hi poggor

Thanks. At that point in the macro it calls MS word in the background to copy the Brewday sheet info onto the Brew sheet as a comment. So you need both Excel and Word on the computer to get it to run properly.

That's the only way I could find of copying part of a worksheet to a comment.

If you go to near the end of the makerecipe macro and put a ' in front on the Call PasteComment line it should by pass that bit.

cheers

Ian