Maxi-BIAB Calculator 2.0 Draft

Post #1 made 8 years ago
AdminNote: This thread has redulted in two separate projects now. Great job, Stux!

I've been working on the Maxi-BIAB Calculator for quite a few months now in this thread
viewtopic.php?f=89&t=595

Eventually the Maxi-BIAB Calculator grew to the point where it contained an entire updated copy of the original BIAB Calculator "The Calculator"

Since then, I've updated the Calculator portion to fix most of the hop calculation bugs, grain bill issues and other issues as well as add full Metric/English/Imperial support. This calculator more or less matches the results produced by BeerSmith 2.0

The Maxi-BIAB Calculator is now more accurately described as the BIAB Calculator with Maxi-BIAB extensions :)

The plan is that we can simply delete the Maxi-BIAB tabs from any given Maxi-BIAB calculator version to get a matching standard BIAB Calculator.

Because the embedded BIAB Calculator has now progressed so far beyond the original calculator its time to update the original and start calling it "BIAB Calculator 2.0", but before we do that, this calculator needs some more testings and tweeking

To re-iterate, the Maxi-BIAB Calculator is an extended version of the future "The Calculator" that is the backbone of this site

So Please, Enjoy, Test, and feed back :)

July 09 Version
Fully Imperialized Brewing History
Lots of facelift changes and work in progress on the additional tabs
July 05 Version
Added UK Imperial mode
Corrected HopGrams label
Changed dark green on archived actuals to light green
Had an attempt at changing L/KG to fl oz/oz. It didn't work out.

July 04b Version
Now with auto-magic Macros for US/Metric conversion!!!

July 04 Version
Fixed efficiency calculations in US mode
added Kettle Trub Percentage
Fixed decimal places
Fixed a few other issues with the facelift
changed "US Imperial" to "English (US)"
renamed first page to "BIAB Calculator"
added some magic temperature labels
More Details...

July 03 'Facelift' Version
First three tabs have been given a facelift. Feedback required.
Uses Dr Smurto's Golden Ale as a sample recipe.

July 01 Version
Added Strike Water calculation

June 30 Version
Finished imperializing Hop Bill page. You can now switch the units of the original recipe boil volume.
Fixed bug in Maxi-Actuals Hop Bill IBUs
Last edited by stux on 30 Jun 2011, 18:05, edited 11 times in total.
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 #2 made 8 years ago
Well done Stux :clap: :clap: :clap: :clap: :clap: :clap: :clap: :clap: :clap: :clap: :clap:
I've just had a look and its obvious that a lot of work has gone into this, I'm planning a brew in the next couple of days so I'll use it then, I'm familiar with the old version but I'll try and look at it with newbie eyes and play devils advocate.

Well done again to all those involved top job :champ:

Yeasty
Why is everyone talking about "Cheese"
    • SVA Brewer With Over 50 Brews From Great Britain

Post #3 made 8 years ago
stux,

I wrote a bit of an essay here last night but decided to send it to you direct instead. I'll look over the spreadsheet in detail over the weekend and concentrate on layout etc. Can't believe what you have managed to do - it's been truly fascinating watching you develop this. Thanks a heap for all your hours and efforts.

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

Post #4 made 8 years ago
stux,

As we discussed here is my strike water calc.

I already told you, but I'll let everyone else know...

I've been using this strike water calculator for some time now. I ran across a nice article in BYO that came out late last year. I was reluctant to post it because I wanted to avoid too many spreadsheets going around the site. The equations they give cover all aspects of calculating mash temperature including the heat capacity of water, grains, and mash tun and also the slaking heat of the mash (slaking heat is the heat produced when the grains are mixed with the water, that's about all I know). The equation works particularly well for BIABers because the thermal component of the mash tun is zero since everything is heated as the strike water is being brought to temperature (for those of us using some sort of insulation, make sure the insulation is wrapped around the kettle as you're heating your water (where allowable)).

I've used this for a number of brews now and have hit my mash temperature dead on. I'd be interested in having others try it out and see what they get.

I wish I could explain the equation in more detail, but I don't own this issue. I was borrowing issues from the library, including this one, before I was given a subscription this past Christmas. I went to the library today, but this issue is checked out. If anyone has this issue and wants to explain further, then feel free. I'll keep my eye out for the issue to come back into the library, but that might be a while.

stux, feel free to integrate it anyway you see fit into your calculator. It's not in metric as it stands right now, but could add some simple conversions.
You do not have the required permissions to view the files attached to this post.

Post #5 made 8 years ago
BrickBrewHaus wrote:stux,

As we discussed here is my strike water calc.

I already told you, but I'll let everyone else know...

I've been using this strike water calculator for some time now. I ran across a nice article in BYO that came out late last year. I was reluctant to post it because I wanted to avoid too many spreadsheets going around the site. The equations they give cover all aspects of calculating mash temperature including the heat capacity of water, grains, and mash tun and also the slaking heat of the mash (slaking heat is the heat produced when the grains are mixed with the water, that's about all I know). The equation works particularly well for BIABers because the thermal component of the mash tun is zero since everything is heated as the strike water is being brought to temperature (for those of us using some sort of insulation, make sure the insulation is wrapped around the kettle as you're heating your water (where allowable)).

I've used this for a number of brews now and have hit my mash temperature dead on. I'd be interested in having others try it out and see what they get.

I wish I could explain the equation in more detail, but I don't own this issue. I was borrowing issues from the library, including this one, before I was given a subscription this past Christmas. I went to the library today, but this issue is checked out. If anyone has this issue and wants to explain further, then feel free. I'll keep my eye out for the issue to come back into the library, but that might be a while.

stux, feel free to integrate it anyway you see fit into your calculator. It's not in metric as it stands right now, but could add some simple conversions.
Looked at the formula.

Its very similar to the one on this page (Metric version)

http://www.grainandgrape.com.au/article" onclick="window.open(this.href);return false; ... keTemp.htm

Except it looks like a factor of 8.1489 is used to convert the Gal/Lbs to L/KGs

The other difference is (0.5*0.0746*ST_Target_Mash_T_Fheit*ST_Grain_W_Lbs))

Which I suspect is the grain activity heat adjustment...

I suspect a lot of the complexity in the formula is actually derived from conversion from metric to imperial... as such, I'd like to find a metric version, or convert back to metric in order to simplify it. Also, as the calculator operates internally in metric this will prevent triple conversions in the imperial case!

Still looking into it :)
Last edited by stux on 01 Jul 2011, 13:48, edited 5 times in total.
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 #6 made 8 years ago
1 gal weighs 8.3454 lbs at 4C and 8 lbs at 100C, so 8.1489 is probably at about 155F/68C

BYO article from 1999
http://www.byo.com/stories/techniques/a" onclick="window.open(this.href);return false; ... -thickness

And I think this is the article you are referring to (but not the text of the article)

http://www.byo.com/component/resource/a" onclick="window.open(this.href);return false; ... lculations
http://www.byo.com/component/resource/a" onclick="window.open(this.href);return false; ... tions-pt-2
Mash Temperature Calculations (Pt. 2) ( 2010)
Mash Temperature Calculations (October 2010)
Last edited by stux on 01 Jul 2011, 13:52, edited 5 times in total.
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 #7 made 8 years ago
I've actually implemented both formulas with Metric and Imperial conversions at the moment.

They do produce different results.

BeerSmith2 produces results between the two formulas.

The formula that BrickBrewHaus supplied produced the lowest value, and its better to undershoot than overshoot :)
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 #8 made 8 years ago
I've added BrickBrewHaus' Strike Water Temp calculation to the Volumes Page.

I also added the Grain & Grape calculation.

You can choose the calculation used by clicking on "Strike Water Temp" label. If you choose "Strike Water Temp2" then you get the G&G calculation. I'm wondering which calculation is closer to the real result.

Basically, just enter your desired mash temp, and a grain/ambient temp and the Strike temp will be calculated.
Maxi-BIAB Calculator Jul01.xls
You do not have the required permissions to view the files attached to this post.
Last edited by stux on 01 Jul 2011, 17:39, edited 5 times in total.
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 8 years ago
stux and I have been wanting to change the layout of the calculator to make it easier to understand etc.

I have just played around with the first sheet to see if I could come up with something. I've become a bit 'blind' to how the spreadsheet would actually appear to a new user so I have no idea if the attached draft is better or worse.

Here is what I changed, broke or need stux to fix...

What I Changed

1. Deleted the Efficiency Calculator sheet.
2. Added efficiency figures to first sheet.
3. Changed the whole column structure and layout.
4. Comments deleted by the removal of old column C can now be found as 'hover' comments in column B.
5. Added comments.
6. Deleted a lot of stuff at the bottom of the Volumes page as these will be in a 'How to use the BIAB Calculator 2' post.
7. Changed the names and layout of the 'Straight-Sided' tools.
8. Changed the top of the form and tried to do a colour scheme to match that of BIABrewer.info.
9. Put in all the correct calcs for the actuals column (except the 'Average')
10. Added in Dr Smurto's recipe as an example as this will be the recipe we'll use in the new commentary.
11. Actual 1 shows an actual brew that miraculaously scored the same results as the estimates.
12. Actual 2 shows a more typical brew.
13. Wrote reverse calcs for a lot of stuff in Actuals e.g. 'Evaporation Correction Factor'

What I Broke or Need Stux to Fix

1. Cells I2, I3, I6, J2, J3 and J6 seem to have some funny formatting copied down from the Metric tab.
2. Cell I6 should be in date format I suppose - a date format that all will understand.
3. Can the Metric button be changed so it is a single cell instead of two merged ones? This way it will be right-justified with everthing else.
4. Couldn't work out how to do the Average calcs in Actuals.
5. Couldn't work out how to use some of your co-efficients so some of my formulas in Actuals aren't matching the Estimates e.g. Hot versus cold volumes.
6. Be good if the Volumes tab name could be changed to 'Home' or something.
7. Be good if 'Bilbo Baggins' etc could be removed from comments or at least set permanently as BIABrewer.info.
8. Just noticed that to match the DrSmurto recipe I am working on, Kettle Trub Percentag figure needs changing to 17.95% and Original Gravity to 1.047.
9. Also just noticed that when you change this percentage, the 'Efficiency into Fermenter' figure suddenly increases in the number of decimal places displayed???

Cheers,
Pat

Go to this post for my latest attempt at layout changes.
You do not have the required permissions to view the files attached to this post.
Last edited by Pat on 01 Jul 2011, 23:34, edited 5 times in total.
Are you a "Goodwill Brewer?" Pay forward and Buy Some BIPs ;)

Post #11 made 8 years ago
I've been downloading each update and plugging in the recipe that I've been playing around with to convert to mini-BIAB, just to compare the successive versions. This new calculator is looking good!

If you guys don't mind a few comments from the peanut gallery...

The easiest thing to miss is that I have to separately change the units on the Hops sheet for the Original Recipe -- End of Boil Volume to either L or gal. I keep expecting this to change with the Metric/US Imperial switch on the Volumes page. If it is too hard to make this automagically change, as well, then I suggest outlining that spreadsheet cell ('Hop Bill'!F6) in read to flag that it needs to be changed. I miss this every time and always wonder why my IBUs come out so far off.

Regarding the change "6. Deleted a lot of stuff at the bottom of the Volumes page as these will be in a 'How to use the BIAB Calculator 2' post." I would suggest keeping these notes somewhere in the workbook so that it is a stand-alone document. I don't like the idea of needing to connect to the Internet and find the right post just to look up these notes.

In the original the color scheme, I liked that the cells that I might want to change, but don't need to, were flagged in light green. I think that these are the quantities with an asterisk. If so, I'd suggest a footnote to go along with the asterisks explaining that they can be changed if need be, but that the defaults are typically OK.

Some of the pop-up comments need an extra blank line or two at the bottom. For example, on the Volumes sheet, cell B34 (the quantity for Evaporation Per Hour), the last line of the pop-up comment gets cut off and can't be read.

Good catch in the water depth section (formerly Section B) of the Volumes sheet, making the Start of Boil Volume and End of Boil Volume point to the 100C cells and not the 20C cells!

Post #12 made 8 years ago
smyrnas (lol on the peanut gallery :P ) and sig (can't believe I couldn't see the average problem as I have noticed it before :roll:), thanks a heap for taking the time to give such detailed feedback. This is exactly what is needed now.

All your suggestions seem to be excellent and I can do most of these without stux's advanced knowledge so I'll keep working on the sheet today to add your suggestions. I don't want to do too much though until more feedback is given on the new colour scheme and layout.

Here, also, are a few explanations on some of your comments...

BIAB Calculator 2.0 will only be three tabs - the first three. (All the other tabs will only be found in the 'Maxi-BIAB Calculator 2.0'). So only the first three tabs need to be focussed on here.

The three sheets will include a link to an online 'How to Use' thread - Having all but the most basic instructions on the spreadsheet soon messes it up. Unless someone can work out a way of writing neat instructions within the sheet I am preferring the online option??? (Easier for us too ;)). I also don't like the idea of the sheet floating around with errors or out of date instructions???

Original Recipe 'End of Boil Volume' - This is really hard to work out how to layout. Stux explained the problem well to me... "You might work in metric but your original recipe might be in US." If anyone can see a good way around this, stux would appreciate it.

Original light green thing - I have put aterixes in column J to emphasise the figures that should be changed after you have done five brews. I see I have made at least one error where I put an asterix though. Will take a fresh look at that for sure.

'Brew Length' - Will change this for sure as there should not be any terms in the calculator that are ambiguous.

US US/Imperial - Stux will have to change that but I agree. Once again, no ambiguity.

Grain Bill in Kilos - Stux mentioned something about that cell being very difficult to deal with. He would have to explain this and maybe someone can find a solution?

Grain Bill and Hop Bill Presentation - I'll explore this once main page is done and it is confirmed that users here and stux want the new colour scheme / layout. (On the hop/oz question ozs over-rides grams.)

Feedback like you have given makes a real difference. (Don't know how stux managed to do all the extremely hard 'unseen' side of things with hardly any feedback - amazing!) Please keep it coming even if it is a simple comment that says, "Looks awful."

:lol:
Pat
Last edited by Pat on 02 Jul 2011, 05:13, edited 5 times in total.
Are you a "Goodwill Brewer?" Pay forward and Buy Some BIPs ;)

Post #13 made 8 years ago
Quick reply

IBUs override ounces override grams

The hop bill original recipe volume cell needs to not change units when you switch units to allow the calculator to be used to store/transmit recipes
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 #15 made 8 years ago
Attached is another crack at changing the layout etc...

stux, if you like the changes, I will leave it to you to copy this into Post#1.

I will also not do any more playing unless I contact you first. So, back to you :lol:

What I Changed or Fixed

1. Re-sized hover comments and removed the 'owner' part of the comment. (Some would not let me edit them though).
2. More layout and colour scheme changes including on the Grain Bill and Hop Bill page.
3. Added some extra fields for US users e.g. cell B44 of volumes, column F in Grain Bill and column J in Hop Bill.
4. Asterixes on Grain Bill corrected. Asterix description added on line 52.
5. Numbers on grain bill page changed to match Dr Smurto's Golden Ale.
6. Changed 'Brw Length' to 'Volume into Kegs/Bottles'.
7. Corrected average formulas.
8. Tried to make the L/Kg question a bit clearer on 'Hop Bill'

What I Broke or Need Stux to Fix (Replaces previous list.)

1. Cells I2, I3, I6, J2, J3 and J6 seem to have some funny formatting copied down from the Metric tab.
2. Cell I6 should be in date format I suppose - a date format that all will understand.
3. Can the Metric button be changed so it is a single cell instead of two merged ones? This way it will be right-justified with everthing else.
4. Couldn't work out how to use some of your co-efficients so some of my formulas in Actuals aren't matching the Estimates e.g. Hot versus cold volumes.
5. Be good if the Volumes tab name could be changed to 'Home' or something.
6. When you change the 'Kettle Trub Percentage' the 'Efficiency into Fermenter' figure suddenly increases in the number of decimal places displayed.
7. I could not edit some comments on 'Hop Bill' - namely G5, G6, G7, G9 & N7.
8. There is a possibility that I might have lost one of your fields in 'Notes' tab after a save. (Will see if I can find it.)
9. Strike Water calc and the Straight-Sided Kettle calcs don't accommodate US.

Go to this post for latest draft.
Last edited by Pat on 02 Jul 2011, 08:03, edited 5 times in total.
Are you a "Goodwill Brewer?" Pay forward and Buy Some BIPs ;)

Post #16 made 8 years ago
I like the new colour for the headings and the new layout, looks more like a traditional brew checklist

BUT I really liked the old calculator how the user editable cells were green and the important ones were green/red

Perhaps reserve the dark green for the headings etc, and use a pale green for cell marking, or perhaps the dark green for important cells and light green for editable cells, or just bring back the red although I was never a fan of red in cells :)

Why is grain bill required listed in both pounds and kg? This value used to switch perfectly with the imperial setting?

"US Imperial" should be called "US" or "US Customary", or "US English"! I will fix this next time i work on the sheet, but in the meantime, which version of "US" should we use?
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 8 years ago
To put it more clearly

The three systems are

The Metric System (or SI)
The US Customary System (also know as "English" because it was based on a system used in England at the time)
The Imperial System (as legislated in UK after the American system was defined)

I believe it is important to refer to the imperial system as "UK Imperial" for the avoidance of doubt and the US system should be referred to as either "US Customary"' which is correct but perhaps confusing, or "US English" which is just plain weird :)
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 #18 made 8 years ago
Does anyone know of a way to get a formula to update another cell without using a script or a formula in the cell to be updated?
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 #19 made 8 years ago
Ha! So I did stuff something up :P

Sorry stux, I didn't realise that changing from metric to Us changed the weight to kgs. For some reason I thought it was a problem cell and didn't even check. Same goes for the extra column I added on the Hop Bill page. Oops! Hope I haven't fone antyhing else to drive you mad :P.

Not too sure on how to do the colour thing. At the moment there are three types of cells that need some sort of emphasis...
- The five dark green cells are the criticals.
- Light green require input during a brew.
- Asterixed fields can be modified by experienced brewers and/or by the new user after 5 brews. I tried italicising these but it doesn't look very good.
So, is it only the italicised fields that aren't been given enough attention?

What about just US for the name? This will match up with other work done so far.

Not sure on how to update a cell without a formula. There's the 'copy cell values, not formulas' in excel help but I suspect this is not what you are chasing.

Hopefully we'll get some more feedback throughout the day?

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

Post #20 made 8 years ago
stux wrote:Does anyone know of a way to get a formula to update another cell without using a script or a formula in the cell to be updated?
I use a macro to do this in my spreadsheet it's called keycellschanged it updates the beer colour when the EBC values change.

cheers

Ian
Last edited by ianh on 02 Jul 2011, 12:09, edited 5 times in total.

Post #21 made 8 years ago
Once upon a time vb macros worked on mac and and windows. And then in excel 2008 MS decided that mac excel would use AppleScript and Win excel would use VB script. So now, as far as I know it's impossible to use scripts which work on both mac and windows. clever way for ms to guarantee the pc market for corporate excel use
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 #22 made 8 years ago
Like the next bloke I hate MS with a passion. Does that mean even if the files are saved in Excel 2000-2003 format then the macros do not run on the latest versions for PC and Mac.

Post #24 made 8 years ago
I'm glad that I've been helpful. I'll have time to look at the spreadsheet later this weekend, but for now...

I'm in the US and am used to referring to "metric", "English", and "Imperial" measurements

However, if you want to be clearer, I'd suggest:
  • Metric
  • English (US)
  • Imperial (UK)
I think that "English (US)" should be pretty clear. I believe that, right now, the spreadsheet does not handle "Imperial (UK)" and I do not know if the folks there still us Imperial gallons (which are larger than the English gallons used in the US). Maybe someone in the UK could say.
Last edited by smyrnaquince on 03 Jul 2011, 06:08, edited 5 times in total.

Post #25 made 8 years ago
OK, I looked at the newest version. Unfortunately, several things broke with the latest update:

The following comments are from using the "Jul02 - Pat Changes" spreadsheet in US Imperial mode and comparing it to the ""Jul01 - Pat Changes" version.

Volumes
  • Default quantity for "Kettle Trub Percentage" changed from 12 to 17.95. (Comments below are based on changing it back to 12 to match the previous version)
  • Efficiency into Fermenter result has gone down slightly
  • Efficiency into Kettle result has gone down slightly
  • Grain Bill Required (Kg) is actually showing a quantity that is in pounds. The units shown in C43 correctly show "lbs". Also, the result is now slightly different from what it was on the Jul01 version
  • Grain Bill Required (Pounds) converts off of the Grain Bill Required (Kg) quantity and is therefore wrong
  • Strike Water Temp has changed from 68.5 to 68.6 degrees F.
  • "Water Required at ambient is..." (B50 and B61) has gone up slightly
  • A51 is missing its entry, which was"Approximate Mash Volume". Also, this quantity (B51) has gone up slightly
  • "Water Required at mash temp is..." (B62) has gone up slightly
  • "Water Required at 20C is..." inches (B70) and gallons (D70) have gone up slightly
  • "Water Required at 65C is..." in gallons (D71) has gone up slightly
Grain Bill
  • Scaled Grain Bill is now wrong. Having entered pounds in Column D, the results in Column G, which should be Kilos, are pounds.
  • Scaled Grain Bill Column G (labeled as Kilos, but coming out as pounds), values are slightly higher than previous version
  • Scaled Grain Bill Column G values get multiplied by 2.20462262 to get pounds for Column F, but the results (of course) are completely wrong.
  • Grain Dry Volume has gone up slightly
  • Grain Dry Displacement has gone up slightly
  • Grain Wet Displacement has gone up slightly
  • Grain Absorbtion has gone up slightly
  • Estimated Available Sugars has gone up
Hop Bill
  • J9 should read "Grams, not "Ozs". I would also suggest swapping columns J and K so the order matches the order of the columns in the Original Recipe.
Last edited by smyrnaquince on 03 Jul 2011, 08:10, edited 5 times in total.

Return to “Measurement, Mathematics and Records”

Brewers Online

Brewers browsing this forum: No members and 0 guests

cron