Seeing as I have done the most posts in the 'How to convert recipes thread' here's what I reckon...

The Mathematics

This is excellent. Amazing job stux! Having both imperial and metric is going to certainly make my life easier.

The Layout

This is heaps clearer to me and more visually pleasing - the old calculator is now actually hurting my eyes . So I personally really like the way this is heading.

The calculator now contains more fields which always has pros and cons but any sort of software takes some learning. Users will also have on-site instructions so that seems fine. Descriptions also seem quite clear.

Only worry for me is that the inclusion of the hop/gravity chart bumps the sheet up from 140KB to 540KB which makes uploading etc much longer.

I've just done a recipe conversion for Ralphus so it will be interesting to see how someone new finds it.

Don't think I'll be using the old version from now on


PP

guys, i can't keep up!.
I just downloaded three MAxi-BIAB files and i'm trying to figure out which is the most updated one.
i will try to post any comments, although it would seem you guys are covering everything. and fast.
BTW, you do realize this is all useless to me as my brew kettle is square

Pat,

I changed the hop trub percentage so that the calculator will match some guides that have already been written up so and this is what is resulting in most of the changes you noticed - the whole brew has become bigger.

No. As I mentioned, I reset the trub percentage to the old value so I could compare the results. However, I just realized that the "End of Boil Efficiency" was changed from a default of 83 to a default of 81.6. That is what threw off all my earlier calculations.

My apologies. Now that I made the default Kettle Trub Percentage and default End of Boil Efficiency match the old values, all calculation differences I mentioned last time have now been resolved. Whew!

New comments:

• Volumes sheet "Efficiency into Fermenter" is not calculated correctly in English units (comes out as 8.95%). However, it does not appear that this number is used in the calculations, so it doesn't throw anything off
• Volumes sheet "Efficiency into Kettle" is not calculated correctly in English units (comes out as 9.9%). However, it does not appear that this number is used in the calculations, so it doesn't throw anything off

In the Volumes sheet, I finally realized that I need to enter a "Target Mash Temp" and "Grain/Ambient Temp" in degrees F! Two questions:

• I'm curious about it, but I can't find where the "Strike Water Temp" is calculated
• Are the these temperatures used anywhere? I don't see any effect elsewhere in the spreadsheet if I change them. Shouldn't they affect the efficiencies (Volumes sheet) or the extracted sugars (Grain Bill sheet)?

Answering some other points raised:

• At first, I expected the figures to convert when I switched from Metric to English. Now I understand that they do not. This is not really a recipe converter, but a recipe scaler. For that reason, I still think that on the Hops sheet, the Original Recipe End of Boil Volume should just track with the Metric/English switch instead of being set separately. If there is a feature that would convert Metric to English for the Grain Bill and Hop Bill, then it would make sense to set the Original Recipe End of Boil Volume's unit's separately.
• I like seeing the "Gravity/Hops Ratio" graphic on the Hop Bill page. Is the download size an issue? If so, then I guess the chart could be put in the BIABrewer.info pages and a link to it given in the spreadsheet.

smyrnaquince wrote: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.

I'm going through the recent posts and updates, I plan to make my changes in a post once I get there...

BUT, I know this is confusing, the problem I have been having is that I'm not using scripts (yet) because I haven't worked out a way to get them to work cross-platform, which is stopping me from converting values when metric/us is switched.

The Grain Bill page scales correctly regardless of Unit selection, and with that one field manually switched, so does the hop bill page. This means, with the exception of mash temp, the biab calculator 2.0 can be used to distribute recipes which are ready to scale to any equipment by simply changing hte diameter and batch size (efficiency, etc)

which is neat.

The alternative at this time is to have the Hop Original Volume go out by a factor of 2.54 when you switch to to US units, which would then affect the IBUs.

I think the ideal solution will be to have all the relevant fields switch to US units AND recalculte their values in this new units, but I can't do that until I work out a cross-platform way to do it

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.

I'm liking the new layout, looks a lot more like a work-sheet, and is simpler. The original information is still present in hover comments and there will most likely eventually be a tutorial posted to the forum.

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.

I notice that Pat has made a change to incorporate this behaviour, must admit, I think the green chosen is muddy, and I will perhaps take a look at that

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!

I notice that this has been changed to Ambient and Hot in later versions. Of two minds about that, an alternative might be to put 100C/212F (i can use a calculation for this) in the HOT ones and 20C/68F for the Ambient ones... or we can just leave it as Hot and Ambient...

What say we all?

I like the new layout on the Volumes page.

I think we should add "Fermenter Trub Percentage" just like we have added the Kettle Trub Percentage. This will make it so users can change their trubs without breaking the formulas.

I find with no chill, the 6% i take off from the kettle (18 -> 12%) ends up in the fermenter

Which is why, personally, I like to change the kettle and fermenter losses. One of the philisophies with this rewrite is I don't want people to have to erase a formula or edit a formula to modify the calculator to match their systems

sigurdur wrote:Unfortunately, LibreOffice is a bit different than MS Office.

Most of the formulas seem to be broken :s

I'd really appreciate stux's input on what direction should be taken with this matter.

My experience is that Open/LibreOffice doesn't work, and I think we should just target Excel

I have tried using the modern Excel file format xlsx in the past, and we get even more complaints about it not working with older excels, so I think the best thing to do is use Excel 97-2004 XLS format. This is the most compatible with Excel's out there.

The interesting thing is the spreadsheet is not using very complicate formulas! If you want to see some complicated formulas take a look at Ianh's BIAB Designer... WOW.

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

So, I'll change the "US Imperial" label to something else...

I have always left room to support true UK Imperial units, but have not added that complexity yet

So, I'll make the units

"
Metric
English (US)
Imperial (UK)
"

?

Okay?

But will only display the first two options at the moment

ianh wrote: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.

Just the latest versions for the mac

(Actually to tell the truth, I'm using 2008 on Mac, and its possible they've rectified this in the latest version)

smyrnaquince wrote: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.

Thankyou,

I will go with this wording

I do plan to add "Imperial (UK)" support in the future, but I would like to wait until we finish this update, as it complicates things a little bit

So, Imperial UK basically means using a different gallon... what about pounds and ounces?

sigurdur wrote:A thought occurred to me in regards to converting between US / Metric:
1. Is it possible to add both applescript and vbscript to excel documents? (would it be smart?)

I don't know, but I suspect you can, and if you can, then this is a good solution, and is what we need to do, as the fixed values should switch based on this.

There are only a few values which need to be modified when you switch units

Mash Temp & Grain Temp
Packaging Volume
Diameter of Kettle
Hop Bill: Original Boil Volume

(I think that's most of them )

Everything else is then derived from those volumes, so all we need is a script (possibly both VBScript and AppleScript) which would twiddle those values to keep everything else up to date

2. If not, why not just use vbscript if majority of users are using Windows, or applescript if majority of users are using Macs?

One minor problem with that

I'm sure the majority use Windows, but the person doing most of this work uses/tests on a Mac

If someone wants to work out how to make the packaging volume scale by X_Conv_Gal_L when the units get changed... preferably on both platforms, then that would be a neat thing

So, I looked into the VB Macro thing again

Excel for Mac 2004 support VBA5 macros
Excel for Mac 2008 dropped the support, but I think if you write the macros on a PC to be VBA5 compatible, they might work
Excel for Mac 2011 seems to bring support back...

http://www.macworld.com/article/154785/ ... basic.html

So, I think I've finally got the excuse I need to upgrade to '11

Sigh.

smyrnaquince wrote:Pat,

I changed the hop trub percentage so that the calculator will match some guides that have already been written up so and this is what is resulting in most of the changes you noticed - the whole brew has become bigger.

No. As I mentioned, I reset the trub percentage to the old value so I could compare the results. However, I just realized that the "End of Boil Efficiency" was changed from a default of 83 to a default of 81.6. That is what threw off all my earlier calculations.

My apologies. Now that I made the default Kettle Trub Percentage and default End of Boil Efficiency match the old values, all calculation differences I mentioned last time have now been resolved. Whew!

New comments:

• Volumes sheet "Efficiency into Fermenter" is not calculated correctly in English units (comes out as 8.95%). However, it does not appear that this number is used in the calculations, so it doesn't throw anything off
• Volumes sheet "Efficiency into Kettle" is not calculated correctly in English units (comes out as 9.9%). However, it does not appear that this number is used in the calculations, so it doesn't throw anything off

I'll fix this

In the Volumes sheet, I finally realized that I need to enter a "Target Mash Temp" and "Grain/Ambient Temp" in degrees F! Two questions:

• I'm curious about it, but I can't find where the "Strike Water Temp" is calculated
• Are the these temperatures used anywhere? I don't see any effect elsewhere in the spreadsheet if I change them. Shouldn't they affect the efficiencies (Volumes sheet) or the extracted sugars (Grain Bill sheet)?

You will have to look into the Name Manager (I think that's what its called on Windows) to see the formula


This is the formula to calculate Strike Water Temp in F

=(((Vs_Target_Mash_Temp_F*((8.1489*Vs_Total_Water_X_Gal)+(X_Malt_Specific_Heat*VS_Grain_Bill_X_Lbs)))-(Vs_Grain_Temp_F*X_Malt_Specific_Heat*VS_Grain_Bill_X_Lbs)-(0.5*0.0746*Vs_Target_Mash_Temp_F*VS_Grain_Bill_X_Lbs))/(8.1489*Vs_Total_Water_X_Gal))

This uses a bunch of named formulas which normalize the inputs into Gal/Lbs and F

Then the Cell converts back to C/F etc.

I really hate having this formula in Gal/Lbs etc, but I don't have the original article from BYO Oct 2010 and I'm hoping there is a metric formula in there

I considered trying to metricate it, but in a few minutes I couldn't work it out

Strike Water Temp is purely for the users information, it does not get factored into any other calculations

Beer Smith 2 does seem to have an "Adjust FG based on Mash Temp" calculations, but I don't know those formulas, so the mash temp is ignored.

Answering some other points raised:

• At first, I expected the figures to convert when I switched from Metric to English. Now I understand that they do not. This is not really a recipe converter, but a recipe scaler. For that reason, I still think that on the Hops sheet, the Original Recipe End of Boil Volume should just track with the Metric/English switch instead of being set separately.

That is precisely the reason why it doesn't track. Once I work out how to have the figures change automatically, then it will track

If there is a feature that would convert Metric to English for the Grain Bill

The grain bill stays correct no matter what as the original recipes grain bill is merely a way of representing a ratio

Hop Bill, then it would make sense to set the Original Recipe End of Boil Volume's unit's separately.

The Hop Bill needs to have a correct end of boil volume in order for the bitterness to stay the same when you switch units

[*]I like seeing the "Gravity/Hops Ratio" graphic on the Hop Bill page. Is the download size an issue? If so, then I guess the chart could be put in the BIABrewer.info pages and a link to it given in the spreadsheet.[/list]

I agree, 100K, 500K, doesn't matter , Perhaps the graphic could be reduced in resolution or compressed some more... i might take a look at it.

I would love to be able to plot the BU/GU on the graphic like ianh does... but that requires some pretty serious scripting magic I assume, and at the moment, the calculator is script free

Okay,

I'm going to try and get those changes into the calculator now

Pat wrote:Sig, I have no idea on this sort of stuff and seeing that Stux originally started improving on the original calculator as a way of understanding and exploring brewing calcs maybe this is new territory for him as well? (Little did he know that it would end up here ). Just in case it is new territory...

Excel allows you to save the spreadsheet as an Open Document Spreadsheet. Would publishing it on the site in this format satisfy everyone?

I'll attach the latest here as an .ods just in case it helps.

This doesn't seem to work with my Excel

(Figure that knocks the ODS format on its head )

Right, new version
My changes....

---

Fixed number of decimal places,

SGs have to be 3
Volumes have to be 2
lengths have to be 2
Grain Absorbtion has to be 3
Temperature have to be at least 1
Weights have to be 3
Time can be 0

Btw, the reason some of the places were probably going spastic when you switch to US units was because the field didn't have a number of decimal places specified

I know this means you end up with a little bit more precision than is required, but when you have units which are 2.5 to 3.5x greater than the metric units adequate precision is a bit higher


Fixed miscalculated efficiencies in US

Fixed spelling of my name

Added Fermenter Trub Percentage (necessary for me)

Pat: Do you still want me to unmerge the Units field?

Changed "US Imperial" to "English (US)", added "Imperial (UK)" but its not used yet. (I know a US Gallon is different to an Imperial one... what about a Pound or an Ounce?)

Added 100C/212F and 20C/68F magic labels to Hot and Ambient. The formula back bone is there... to Pat, you can decide if you want to keep the numeric accurate versions, or the imprecise decscription versions... or both or whatever

Removed (Kg) from Grain Bill Required. The units column is accurate

Switched minor editable cells to light green from dull green. I thought it looked better, but we can go with the other green if you want, another colour which looks good is light grey

Added a magic label for 65C/149F, and used that in the Water volumes at the bottom

Added the C/F magic label to Cells A64..A67. This sortof makes the descriptions a bit meaningless

Again, if we want, we can get rid of those magic labels... but I figured, I've added the formulas, now you decide

Grain Bill

Changed # or Kg to "Lbs or KGs. I don't personally like the shorthand of "#" is "Pound" because in some places its "Hash" where as "Lbs" is what is used *everywhere* else in the spreadsheet

Bumped precision in column D to 3 decimal places
Bumped precision in column E to 2 decomal places

...

BTW, I'm thinking of incorporating grain selection into the grain bill, so that you can choose your grain type from a popup menu... this would then get us the LDK for that specific grain, giving us a more accurate OG prediction... etc... I think Ianh has this working in his BIAB Designer, and I believe its just a clever LOOKUP() call

Hop Bill
Call I5... "!!!", is that to draw attention to it? Okay, we'll work out the macro stuff...

Column N

I don't like the "Estim."

Cell E17 "Grams & Ozs" looks a bit funny...

Colum H, @@@@@@@, I'm not a huge fan, I think "at" works better...

Hop Utilization Factor, thinking of changing this to a popup now that I know how to do that , then we can pick "Pellets" or "Whole Flowers" etc

So, if I'm going to do that... which options should be there? Just pellets and flowers?

And I think that will be it for the moment

(Oh, I just changed the name of the first tab to "BIAB Calculator" of course, we can call it anything we want, note for those editing formulas the all the "Vs_" names will continue being "Vs_" instead of "BC_"

Pat wrote:stux,

Uploading should all be good now. Somehow we had reached the board limit so have doubled it. Now I just need to fix my computer so I can read everything from today .

Cool

I edited my previous post with the new version, and will edit the first post too.

Also, I've just upgraded to Office 11, and the good news is macro support is back!