Display posts from previous
Sort by

BIAB Beer Designer

Post #1 made 14 years ago
by ianh
Hi

Whilst others have been putting forward their efforts I thought it was about time I presented mine. So please find attached copy of my BIAB Beer Designer spreadsheet, obviously still in the development stage and probably has a number of things that need correcting. Would appreciate thoughts and comments.

cheers
Ian

Please read the notes first
BIAB Beer Designer1.2.xls
You do not have the required permissions to view the files attached to this post.
Last edited by ianh on 01 Jun 2011, 17:15, edited 7 times in total.

Post #2 made 14 years ago
by Pat
Ian, that is really impressive :o. I'd hate to think of the hours that have gone into it. I think old BIABrewer will be dusting off an Honarary Enthusiast Awards for you in the next few days. Top job!

I'm not sure if it's any help but I have plugged in Nev's Tettnang Pilsner recipe to compare the figures (subbed CaraVienna for Melanoidin). It was really very easy to put the figures in etc. Your IBUs came out to within 1 of BeerSmith2 - not sure how you managed to pull that one off :P. Volume required came out a few litres higher as did gravities. I was in a bit of a rush so don't take my figures as gospel. (Note I changed your grain absorption to 0.63.)

I'll attach the two files below. Can't wait to get some spare time to study this in detail along with Stux's latest efforts.

I am certainly really impressed ian.

:champ:
Pat

Edit: Have re-uploaded the file to correct the errors I originally made (see ian's post below)
You do not have the required permissions to view the files attached to this post.
Are you a "Goodwill Brewer?" Pay forward and Buy Some BIPs ;)

Post #4 made 14 years ago
by BobBrews
ianh,
Awesome! Thanks for the time you put into it. I have to play with this tonight!
tap 1 Raspberry wine
tap 2 Bourbon Barrel Porter
tap 3 Czech Pilsner
tap 4 Triple IPA 11% ABV
Pipeline: Mulled Cider 10% ABV

http://cheesestradamus.com/ Brewers challenge!
    • SVA Brewer With Over 100 Brews From United States of America

Post #5 made 14 years ago
by ianh
Thanks for the comments guys.

Pat thanks for the comparison, a couple of things. you have the pot diameter as 4000 instead of 400 and you are not allowed to enter values in the white cells (only blue ones) as with the hop %AA's.You will see formulas in the cells below E24, just copy these back. To change the hop %AA for say for Tettanger on the MAIN worksheet go down to cell T168 and change the value there, the formulas will then pick up this value.

It's designed to be flexible but as you will notice on the Brews worksheet I have only done 7 BIAB todate. Oh and you can enter a yeast from the dropdown list.

cheers
Ian

edit Pat change the evap rate per hour from 8.1 to 5.4
Last edited by ianh on 02 Jun 2011, 06:05, edited 1 time in total.

Post #6 made 14 years ago
by wizard78
I can't wait to have a look at this tonight, your kit and extract spreadsheet was great. I've been waiting for a biab spreadsheet from you since I seen that you joined.
If it is as good as the K&E sheet it will be a ripper.
Cheers
[center]"All right, brain. You don't like me and I don't like you, but let's just do this and I can get back to killing you with beer."
[/center]
[center]Homer Simpson[/center]
[center]K.I.S.S., B.I.A.B.[/center]

Post #7 made 14 years ago
by Pat
Have just corrected my errors ian and have re-uploaded it in my post above. All is looking good now. On my first attempt, I only had a short amount of time available to scan it and fill it out and towards the end, my depth of water was reading 2mm so the last thing I did was change the 400 to 4000 - lol. It's a real credit to you that I found the sheet so easy to use and didn't make many more errors on a first, totally rushed look :salute: .

Towards the end of the weekend, I'll have time to get three screens happening and open up BIAB Beer Designer, stux's Maxi-BIAB Calculator and BeerSmith2. stux sorted out the last IBU bitterness calc problem late last night so he is now matching BeerSmith2 - a mammoth puzzle. It's great having some software that actually agrees with other software. Now he just wants his spreadsheet to look like yours :P.

I hope I haven't made any other mistakes but I really need to get to work :evil:.

I always heard excellent reports on your K&K designer. Now I know why.

:clap:
Pat
Are you a "Goodwill Brewer?" Pay forward and Buy Some BIPs ;)

Post #8 made 14 years ago
by ianh
Pat - One difference between the Calculator and the spreadsheet is in the end of boil volume. The Calculator takes the fermenter volume of 23L and adds the kettle trub and buffer 3.8L to give 26.8L but then says this is the volume at 100C. The spreadsheet takes the fermenter volume 23L and the trub 3.8L and then multiplies by a factor to give the end of boil volume at 100C of 27.9L

Post #9 made 14 years ago
by stux
We spotted this last night :)

The maxi-calculator uses end of boil volume at 100C as well
Fermenting: -
Cubed: -
Stirplate: -
On Tap: NS Summer Ale III (WY1272), Landlord III (WY1469), Fighter's 70/- II (WY1272), Roast Porter (WY1028), Cider, Soda
Next: Munich Helles III

5/7/12

Post #10 made 14 years ago
by Pat
Yep, that bit of The Calculator threw stux as well. It can get a bit confusing as it works in cold volumes though the cms at bottom right show what you should see on your ruler at the appropriate temperature. We'll make that easier to understand 'see' down the track and there's a calc error there as well I noticed last night.

There's a few calc errors in other parts of the current Calculator (nothing too serious for new users) but stux has fixed these. It's a major project to put a new version of The Calculator up in 'Essentials' as a lot of other things refer to it. In other words, much of Essentials has to be re-written. I think we are close to a stage where we can do this thanks to everyone's hard work. Once all the calcs are agreeing, it will be a simple matter to change The Calculator on the main board whenever anyone comes up with easier to use versions or, down the track, we can make several versions available of varying complexity available should the authors agree of course!

I'll leave you in peace for a few days as I need to finalise and double-check some BIAB things for BeerSmith2 for its final release. It'd be a bit embarrassing if I had any errors in the recipes there :lol:

Cheers,
Pat
Are you a "Goodwill Brewer?" Pay forward and Buy Some BIPs ;)

Post #11 made 14 years ago
by shibolet
oh boy. this looks great.
maybe i won't be buying beersmith 2.0 just yet...

problem #1:
i get a VB compile error when clicking on the INVENTORY, MAKE RECIPE or DELETE RECIPE buttons. any clues?

edit: has anyone tried this on Open Office?
Cube:
fermenter: Sourdough Spelt Ale, Classic Lambic, Oud Brune, Barrel Aged Belgian Dubbel
Kegs: Bob's Black IPA, Blanc Blond, Soda...
to be brewed:

Post #12 made 14 years ago
by ianh
shibolet wrote:oh boy. this looks great.
maybe i won't be buying beersmith 2.0 just yet...

problem #1:
i get a VB compile error when clicking on the INVENTORY, MAKE RECIPE or DELETE RECIPE buttons. any clues?

edit: has anyone tried this on Open Office?
Hi shibolet

Open Office I am told does not run the macros.

The error is probably due to missing a libary. Press Alt and F11 to bring up the Visual Basic screen, from the Menu select Tools and then References. I have the following ticked.

Visual Basic for Applications
MS Excel 12.0 Object Library
OLE Automation
MS Office 12.0 Obj Lib
MS Forms 2.0 Obj Lib
MS Word 12.0 Obj Lib

Hope that fixes the error.

Note I am using Excel 2007 the numbers may be different in your version. You also need to have MS Word on your computer for the Make Recipe macro work. Part of the macro takes the Brewday worksheet, copies it into Word (which is not dislayed) then copies it as a Comment on the Brews worksheet.

cheers
Ian
Last edited by ianh on 03 Jun 2011, 05:15, edited 5 times in total.

Post #13 made 14 years ago
by wizard78
Just as I'd hoped for Ian, well done! :thumbs:
Two things, I can't get the up/down adjustment arrows to work and also is there a chance you could add a total mash volume field (volume of water and grain) to the biab worksheet. It makes it a bit easier to formulate recipes to maximize kettle volume.
Cheers wiz :salute:
[center]"All right, brain. You don't like me and I don't like you, but let's just do this and I can get back to killing you with beer."
[/center]
[center]Homer Simpson[/center]
[center]K.I.S.S., B.I.A.B.[/center]

Post #14 made 14 years ago
by ianh
wizard78 wrote:Just as I'd hoped for Ian, well done! :thumbs:
Two things, I can't get the up/down adjustment arrows to work and also is there a chance you could add a total mash volume field (volume of water and grain) to the biab worksheet. It makes it a bit easier to formulate recipes to maximize kettle volume.
Cheers wiz :salute:
Hi Wiz

Thanks, do the other macro buttons on the Main Worksheet work. Also did the up/down buttons work on the Kit & Extract spreadsheet. If you keep having problems send us a PM.

Included a Total Mash Volume for next version, if you need it before then just add the formula

=K7+SUM(MAIN!C6:C13)*0.66

to a blank cell on the BIAB worksheet.

cheers
Ian
Last edited by ianh on 03 Jun 2011, 13:52, edited 5 times in total.

Post #15 made 14 years ago
by nala
ianh wrote:Hi

Whilst others have been putting forward their efforts I thought it was about time I presented mine. So please find attached copy of my BIAB Beer Designer spreadsheet, obviously still in the development stage and probably has a number of things that need correcting. Would appreciate thoughts and comments.

cheers
Ian

Please read the notes first
Good start Ian, been using your Kit & Extract recipe designer since you launched it.
I like the format and it is very easy to use.
The grain list I find at this stage lacking in both the number of grains listed and
their descriptions,we need to have consistant descriptions so that we all sing from the same hymn sheet.
Last edited by nala on 04 Jun 2011, 11:43, edited 5 times in total.

Post #16 made 14 years ago
by stux
Hi Ian,

I just took a peak at your hop IBU formula (MAIN!G21)

"/$C$3"

I think you're trying to scale the IBUs to the final ferment volume here...

But I don't think you're taking account of the kettle trub.

As I understand it you need to work out the IBUs for the kettle, then subtract the kettle trub, then scale to the ferment volume

Alternatively, just work out the IBUs against the kettle volume
Fermenting: -
Cubed: -
Stirplate: -
On Tap: NS Summer Ale III (WY1272), Landlord III (WY1469), Fighter's 70/- II (WY1272), Roast Porter (WY1028), Cider, Soda
Next: Munich Helles III

5/7/12

Post #17 made 14 years ago
by ianh
stux wrote:Hi Ian,

I just took a peak at your hop IBU formula (MAIN!G21)

"/$C$3"

I think you're trying to scale the IBUs to the final ferment volume here...
Thanks stux already noticed and changed that, given it's history as a Kits & Extract spreadsheet.

Plus adding some more grains and adjuncts and need to sort out FG with adjuncts.

The calculated efficiency on the BIAB worksheet and the one using the Main worksheet don't agree.

I'm sure I will come across more.

cheers
Ian
Last edited by ianh on 04 Jun 2011, 13:14, edited 5 times in total.

Post #18 made 14 years ago
by ianh
Hi

I Have updated the file to version 1.1

I have updated the IBU calculations so they are now based on fermenter volume plus trub.

Changed the dropdown list so the ingredients are on the individual worksheets

Changed how the FG is calculated based on the fermentability of adjuncts.

Changed how the brewhouse efficiency is calculated.

Given an option for those who use hot water into their urn or pot.

Still a work in progress.

cheers

Ian

Post #19 made 14 years ago
by Pat
Hope you have been having fun with this Ian. I have downloaded it and will be looking forward to having a thorough look through.

Thanks again for all your efforts on this.

:salute:
Pat
Are you a "Goodwill Brewer?" Pay forward and Buy Some BIPs ;)

Post #20 made 14 years ago
by Squared
This is a really great tool! I will definitely be playing around with this. Very easy format to use!
in 15 mins of playing around I already have a mock recipe.

hat is off!

Post #21 made 14 years ago
by Lylo
Ian,I am really liking your Designer!It may end up being the only software I use.I am having trouble though with the macros.Where do I change my security levels.I have already gone to tools/security/macro security/ and set the level to low.However I can't seem to use any of the buttons without the "macros" error message coming up.Also,the help you gave shibolet a while ago re:the references section using alt-f11.My list shows ms-word as"missing"but I know I have it.
I am running an older version of exel
Thanks
AWOL

Post #22 made 14 years ago
by ianh
Found an error on the BIAB worksheet the Start of Boil Volume calc is incorrect.

On the BIAB worksheet Copy cell M15 to cell M13 should fix the problem.

cheers

Ian

Post #23 made 14 years ago
by ianh
Lylo wrote:Ian,I am really liking your Designer!It may end up being the only software I use.I am having trouble though with the macros.Where do I change my security levels.I have already gone to tools/security/macro security/ and set the level to low.However I can't seem to use any of the buttons without the "macros" error message coming up.Also,the help you gave shibolet a while ago re:the references section using alt-f11.My list shows ms-word as"missing"but I know I have it.
I am running an older version of exel
Thanks
Lylo

Can you send me a PM or an email, my email address is at the bottom of the notes. What version of Excel are you running and what is the error message.

cheers

Ian
Last edited by ianh on 30 Jun 2011, 10:02, edited 5 times in total.

Post #24 made 14 years ago
by ianh
Have edited the first post and included the latest version of the spreadsheet. Fixed the calculation problem. Added a 15 minutes factor for ibu calculations when No Chilling. Included a factor to adjust the FG depending on the Mash temperature. Put all the hop Info into comments on the Hop worksheet.

cheers

Ian
Post Reply

Return to “Measurement, Mathematics and Records”

Brewers Online

Brewers browsing this forum: No members and 20 guests