Biab Beer Designer Spreadsheet

[Shifter]

Just checked my Crown Urn which has a concealed element against the spreadsheet numbers. My Urn diameter is exactly the same as the number quoted in the spreadsheet.

The numbers I get are: 10ltrs = 115mm, 20ltrs = 225mm, 30ltrs = 335mm. Measurements taken with tap water and a steel tape against the Urn seam.

This corresponds with 1ltr giving an average of 11.3mm. The spreadsheet gives 11m with an empty initial volume cell.

Spreadsheet values are 10 ltrs = 123mm, 20ltrs = 235mm, 30ltrs = 347mm

Question is does your Urn have a concealed element and is there a difference in volume between the two which could account for the disparity? Or, maybe I cocked up my volume measurements.

I downloaded Open Office or at least Libre Office which is apparently the same thing re-visited.

 

[ianh]

Just checked my Crown Urn which has a concealed element against the spreadsheet numbers. My Urn diameter is exactly the same as the number quoted in the spreadsheet.

The numbers I get are: 10ltrs = 115mm, 20ltrs = 225mm, 30ltrs = 335mm. Measurements taken with tap water and a steel tape against the Urn seam.

This corresponds with 1ltr giving an average of 11.3mm. The spreadsheet gives 11m with an empty initial volume cell.

Spreadsheet values are 10 ltrs = 123mm, 20ltrs = 235mm, 30ltrs = 347mm

Question is does your Urn have a concealed element and is there a difference in volume between the two which could account for the disparity? Or, maybe I cocked up my volume measurements.

I downloaded Open Office or at least Libre Office which is apparently the same thing re-visited.

Hi Shifter

My urn is not perfectly round, diameter varies from 334mm to 340mm. I got the numbers by adding 6 litres of water by weight to cover the concealed element and measured this using a steel rule against the side of the urn at 78mm. Using the formula the concealed element accounts for about 10-11mm, I used 11mm in the calculation. Then each additional litre is based on the formula =1/(PI()*0.1675^2) where the .1675 is half the 337mm diameter.

I would suggest how you are measuring the 10 litres in volume is where the error occurs. If you take 10 litres based on 11.3mm per litre thats 113mm which only leaves 2mm for the effect of the concealed element, if you go to 20 litres thats about 225mm nothing for the volume of the concealed element.

Yes OO does not handle the macros, I tried and saved as an .ods file, this wiped the macros and the dropdown lists.

cheers

Ian

 

[Shifter]

Ian,

Cause you are right. The concealed element is slightly domed and not parallel with the bottom of the Urn. Having just checked my Urn is not round either, so your measurements would appear to be correct. I'll have to re mark my urn.

Forgive me I'm only an engineer.

Thanks for your feedback and the spreadsheet, which although the macros don't work, is still worthwhile.

Cheers,

Graham.

 

[BobtheBrewer]

Hi Shifter

My urn is not perfectly round, diameter varies from 334mm to 340mm. I got the numbers by adding 6 litres of water by weight to cover the concealed element and measured this using a steel rule against the side of the urn at 78mm. Using the formula the concealed element accounts for about 10-11mm, I used 11mm in the calculation. Then each additional litre is based on the formula =1/(PI()*0.1675^2) where the .1675 is half the 337mm diameter.

I would suggest how you are measuring the 10 litres in volume is where the error occurs. If you take 10 litres based on 11.3mm per litre thats 113mm which only leaves 2mm for the effect of the concealed element, if you go to 20 litres thats about 225mm nothing for the volume of the concealed element.

Yes OO does not handle the macros, I tried and saved as an .ods file, this wiped the macros and the dropdown lists.

cheers

Ian

+1
At last I seem to have gotten something right!

 

[TroyNZ]

Sorry for delay in replying just back from 4 days bushwalking.

Some people have got a project libary error when trying to save recipes, not sure what error you get. Not sure about the Mac but this may help for the work computer.


If you open the spreadsheet. 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

I am using Excel 2007 so if using a different version will get a number other than 12.0, hope that fixes the problem.

Hopefully will have updated vesion shortly, time is the problem.

cheers

Ian

Ian,
Firstly, thanks for the effort you have put into this spread sheet, I used your Kit spreadsheet and am now moving to BIAB.
I have opened the visual basic library and found that the MS Word 12.0 Object Library is not ticked but, when I ticked it and clicked "OK", I was told that it was not loaded (or something like that) I tried to load it but couldn't. now when I go back to the visual basic library there is now a line that says "MISSING: MS Word 12.0 Object Library"
I have tried copying the sheet but I just get "ERROR IN LOADING DLL".
Any Ideas??
Troy

 

[Shed101]

Hi Ian. Using your excellent tool for the second time today (my second ever BIAB).

Can you tell me what the difference between cells K16 and I25 in the BIAB sheet are?

They are both labelled as 'End of boil wort SG' and one even specifies to measure before adding adjuncts, yet I have quite different figures ... there's surely a simple explanation, but I must be a lighter shade of simple!

 

[ianh]

Hi Ian. Using your excellent tool for the second time today (my second ever BIAB).

Can you tell me what the difference between cells K16 and I25 in the BIAB sheet are?

They are both labelled as 'End of boil wort SG' and one even specifies to measure before adding adjuncts, yet I have quite different figures ... there's surely a simple explanation, but I must be a lighter shade of simple!

Hi Shed101

Thanks. Cell K16 is the predicted End of Boil SG (yellow cell is a calculation) from all the information that has been entered on the spreadsheet, I25 requires an input (light blue cell,changed by user), insert your actual End of Boil SG here and it calculates the actual End of Boil efficiency. As we are interested in the Mash efficiency of the grains, the sample should be taken before any adjuncts are added at the end of the boil as that's the way the calculation was made to work.

The efficiency used in the recipe design, cell C7 is set at 75% if after a few brews, your average efficiency is different then you should change this value. I found my first brew had the lowest efficiency, then it settled down around 75%. You record the efficiency for each brew and enter in column K on the BREWS worksheet

Hope that makes sense.

cheers

Ian

 

[Shifter]

Ian,

I have been having huge problems with water volumes. I have measured my Urn again and find the diameter as you do 337mm average. When putting 6 ltrs of water in the bottom of the urn I get 70mm of water. Measured with a steel rule against the seam of the urn with tap water. Should I change any of the formula to adjust for the 70 mm instead of 78mm??

 

[Shed101]

As we are interested in the Mash efficiency of the grains, the sample should be taken before any adjuncts are added at the end of the boil as that's the way the calculation was made to work.

The efficiency used in the recipe design, cell C7 is set at 75% if after a few brews, your average efficiency is different then you should change this value. I found my first brew had the lowest efficiency, then it settled down around 75%. You record the efficiency for each brew and enter in column K on the BREWS worksheet

Hope that makes sense.

cheers

Ian

Thanks Ian - definitely makes sense. As i'm starting out i've knocked efficiency back to 70% ... although post brew I see I actually hit 74%, I guess that's my benchmark for my next brew!

However because my (funky old fashoned) recipe required the adjuncts post-mash but in the boil my measured SG at end of boil throws things out and teh calc. says I got 83%. Never mind, now you've explained it .

Not that i would want to cause you any extra work ... but if on the MAIN sheet it was possible to include adjuncts in the boil or as 'proper' adjuncts ...

 

[ianh]

Ian,

I have been having huge problems with water volumes. I have measured my Urn again and find the diameter as you do 337mm average. When putting 6 ltrs of water in the bottom of the urn I get 70mm of water. Measured with a steel rule against the seam of the urn with tap water. Should I change any of the formula to adjust for the 70 mm instead of 78mm??

Hi Shifter

A formula like =IF($E$4="Crown Urn",K5/(PI()*($G$4/2000)^2)+11,K5/(PI()*($G$4/2000)^2)) is in cells K6 and K8 on the BIAB worksheet, change the 11 to 3 in both cells so the formula becomes =IF($E$4="Crown Urn",K5/(PI()*($G$4/2000)^2)+3,K5/(PI()*($G$4/2000)^2)). A similar but different formula is in cells M13, M15, E24 and E25 on the BIAB worksheet again change the number 11 to 3 and see how you go.

Hopefully may catch up at the Beer Festival next Saturday.

cheers

Ian

 

[Shifter]

Ian,
Thanks for that. I'll give it a try and revert. I am also going to put the spreadsheet on a Windows XP computer. I love Macs but they do have limitations for this type of thing.
Probably be at the Beer Feast on Friday as got plenty to do on Saturday.

Have a god one.

 

[Shifter]

Ian,

Spreadsheet loaded on to a Window XP laptop. Oh what joy to see it all work as designed. Excellent bit of software. A credit to you mate!

 

[ianh]

Ian,

Spreadsheet loaded on to a Window XP laptop. Oh what joy to see it all work as designed. Excellent bit of software. A credit to you mate!

Thanks Shifter, glad you can now see and access all the spreadsheet functions.

cheers

Ian

 

[Pim Muter]

Hi Ianh
Ive been using your speadsheet for a while, works great much easier to use than the real software.
I have two minor points you might consider if possible, can the BIAB page be saved with each recipe? as i use defferent mash temps and get different efficeincy for different recipies at the moment im manually changing them each time but it gets confusing.
And secondly drop down talbes on the inventory page would speed things up a lot. im no excel wizard so cant figure how to do myself.

Anyway love your work and may the beer flow freely.

Thanks

Pim Muter

 

[robbo5253]

Hey Pim,
to get the inventory to drop down, select Cell B12 in the main screen (or any blank grain option). Select copy from the top menu.
Then go to the inventory sheet and select the first cell below grains, select paste.
Then move your mouse to the bottom right hand corner of the cell to get the small cross, then simply drag down so you have enough fields.
Repeat for Hops(B22), Adjuncts (B16) & Yeast (J18).
With the yeast, once you have pasted it into the inventory sheet, select the blank one before dragging it down, this just saves doing each one manually.

Cheers

Robbo

 

[Pim Muter]

Thanks Robby
As easy as that ay
Any brilliant ideas on saving getting the sheet to save biab page, yeast used and carbonation levels???

Would be very helpful

Ciao

Pim

 

[ianh]

Hi Pim Muter

Sorry for not replying, been away for a few days.

The yeast and mash temperature are already stored as part of the Recipe. To add other things to the Recipe would require a bit of rewriting.

What you could do is where you have made and saved a recipe is to edit the comments on the BREWS worksheet associated that brew. That way you could retain and retrieve any information about the brew.

cheers

Ian

 

[Pim Muter]

Hi Ian
Ive got another question if you dont mind, would it be possible to write in a section for mash ratios ie. litres per kilo? would be real handy as i use different ratios for a lot of my brews at the moment im calculating mash separately then using your sheet.

Any ideas would be appreciated

Best regards

Pim Muter

 

[ianh]

Hi All

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

View attachment BIAB_Beer_Designer_v1.3.xls

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

 

[Oakers]

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.