A New Approach to BIAB Calculus using Conversion Efficiency


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

A New Approach to BIAB Calculus using Conversion Efficiency

Post by stux » 6 years ago

This is a BIAB calculator which uses a Novel method to calculate your grainbill, which is independant of "Into Boil Efficiency", and works well with large beers, with or without sparging

It's very accurate.
...

I've been working on a solution to a problem which affects all BIABers who are using BIAB calculators to determine their volumes and grain bills :)

I think I've cracked it ;)

The problem is this, as you increase the gravity of the wort you are creating the effciency goes down. Most of us have experienced this and we know that we get about 83% Into Boil effciency for "normal" beers, and maybe 75% for bigger beers.

With my experiments in Maxi-BIAB it really rammed this home when you start getting 70% Into Boil and perhaps just 60% into the fermenter.

Anyway, the solution was to look at the concept of Conversion Efficiency, and then to create a calculator which uses Conversion Effciency to predict BIAB results, rather than Into Boil efficiency.

The new calculator uses just three values to control/predict the calculations, and these values do not vary (that's the theory at least) between brews and batch sizes.

1) Evaporation
2) Real Grain Absorption
3) Conversion Efficiency

update: it has become apparent that we only really need to know "Evaporation"!

if we know these, then we can predict brew volumes and gravities with high accuracy.

Your evaporation can be guestimated by the current BIAB calculators, and is easy to refine over 1 or 2 brews.

Real Grain Absorption is generally about 1 to 1.2L/KG, depending on if you squeeze or not. This is not the same as Apparent Absorption, but again, doesn't vary much, and is easy to calculate over a brew or two.

update: Real Grain Absorption should be about 1.41L/KG if you don't squeeze the bajeezus out of your bag. It will be more like 1.3L/KG if you do

Conversion Efficiency. Your conversion efficiency is how effectively you converted the grain starches into grain sugars in the mash. With BIAB it is fairly easy to get 95-100% of the laboratory extract results. A value of 95% should be reguarded as a good goal for most BIABers and if you are getting less than that, then you have probably got a problem in your mash.

update: most BIABbrewers with good techniques should be able to manage 99% efficiency. If not it means your grain is probably not milled correctly, or some other problem with your mash. This is assuming that the calculator is using the correct malt specs for your grain

With those three values, which we have good estimates for, we can predict with a high reliability the BIAB process and grain bill.

...


That's the theory anyway :)

Now we need to test it ;)

update: it works ;)
...

Current Version: viewtopic.php?f=74&t=1066&p=15537#p15537
Last edited by stux on 31 Oct 2011, 14:53, edited 13 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


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

CE based BIABCalc 2011-10-31.xls
I've attached my current version of the CE BIABcalc.

I think its fairly easy to use.

1) Adjust the Evaporation value to your total expected evaporation. Leave Real Absorption and Conversion Efficiency at 1.0L and 95%, respectively.

2) Enter cooled End Of Boil Volume, so, if you want 23L in your fermenter, use 25L or so.

3) Enter your Recipe OG

That's It.

Mash Requirements section will tell you your grain bill, strike water and total mash volume.

Brewday Estimates and Targets will tell you your Start of Boil volumes, gravities and expected efficiencies

Brewday Results section can be used to enter your actual brew day results. If you enter your results, then the spreadsheet will calculate your actual efficiencies as well as the Calculated System Characteristics.

Calculated System Characteristics. This section includes the updated Real Absorption and Evaporation figures, which if you use as a basis for your System Characteristics the next time, you should get more accurate predictions.
You do not have the required permissions to view the files attached to this post.
Last edited by stux on 31 Oct 2011, 14:58, edited 7 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


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

In order to test this theory it would be good if others could give this calculator a whirl.

The best thing would be to actual use it during a brew day! But if you guys have notes of previous brews, then it would be good if you could run the numbers and see how it works.

In future, I should be able to add some sparging calculations as well, to help with Maxi-BIAB.

Have a look :)
Last edited by stux on 31 Oct 2011, 15:07, edited 1 time 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


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

The basic approach that this calculator uses to work out its numbers is that it calculates how many KG of Malt Extract you need in your Kettle at End Of Boil.

We then work out how much Extract you need in your Kettle during the mash, and from that we can work out the grain bill and water requirements.

The important thing is the numbers are driven by the Conversion Efficiency, which should not vary wildly depending on how large your beer is.

...

I was inspired by this post on HBT
http://www.homebrewtalk.com/f36/maximum ... ost1560831

And much of my understanding is based on Kai's work on BrauKaiser
http://braukaiser.com/wiki/index.php?ti ... Efficiency
http://braukaiser.com/wiki/index.php?ti ... Efficiency
http://braukaiser.com/wiki/index.php?ti ... g_Analysis
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


Pat
Administration
Administration
Posts: 1466
Joined: 7 years ago
Region: Oceania
Preferred Brewing Method: Please select one from below...
City:

Post by Pat » 6 years ago

This thread will have a lot of readers confused. But, it's a great example of how one person's thinking can make life for the rest of us simple.

Stux copied his progress on this to his fellow BIABacus developers. I can count 16 emails and all have as much detail as his posts above. It's actually a very difficult subject to get your head around mainly due to the lack of quality information that exists on this subject.

What will Stux's Efforts Boil Down To?

He says it in the first few lines of his first post but I think it needs more emphasis...
No brewing software adjusts your efficiency according to your gravity.
This is one of several major flaws in existing software.

That's probably about all that most readers here need to see. If you want to read on to the complexities, feel free :). Otherwise, just post to this thread to thank Stux for his time and effort :clap:.

Close Enough is not Good Enough

It is very hard to get to a stage where a brewer can safely say a bit of existing software is faulted in its basic formulas. We naturally treat existing software as brewing gods. Going through a doubt and then testing period is something most brewers would not have the time to do or contemplate.

Most brewers, even when they start to doubt their software, think "Close enough is good enough." This basic failure of existing software to use solid and exact basic formulas leads to a recipe game of "Chinese Whispers." The majority of recipes published on the net at time of writing either lack critical information, are inaccurate or are very often non-nonsensical.

BIABrewer.info maintains that the basic formulas and terminology in a software program must be exact.

Collecting the Figures

BIABrewer.info was able to give Stux a collection of figures. A combination of this and the rare science available enabled him to come up with his spreadsheet.

I think his spreadsheet is as good as it will get for now. There are too many variables such as bag porosity, draining times and/or squeezing, let alone gravity that need to be measured.

One thing BIABrewer.info will try and develop in the future, is a method where you can record your brew results so as we build better averages. It's a very long-term project though.

In the meantime, great work Stux :champ:

Hopefully we'll be able to get it into the BIABacus sooner rather than later. In fact, my main hope is that you will work out a way of incorporating it :lol:

Pat
Last edited by Pat on 31 Oct 2011, 22:43, edited 5 times in total.
Are you a "Goodwill Brewer?" Pay forward and Buy Some BIPs ;)

User avatar

thughes
Gold
Gold
United States of America
Posts: 1206
Joined: 6 years ago
Location: Western NY
Region: USA & Canada
Preferred Brewing Method: Single-Vessel All-Grain (SVA)
City: Rochester

Post by thughes » 6 years ago

A hearty thanks to Stux and all the others working on this project. :clap:


---Todd
WWBBD?


datamichael
Draft
Draft
Posts: 29
Joined: 6 years ago
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by datamichael » 6 years ago

Looks interesting!

Kudos to your for the time and effort!

As to "No brewing software adjusts your efficiency according to your gravity. ", I completely agree. It's extremely difficult to model. Every equation/spreadsheet I've seen doesn't pan out in the real world. My real world experience is much different than Kai's conversion efficiency table. I question anything that says you can get > 1.085 with first runnings, which is essentially what BIAB is.

But as Pat mentions, a method of recording real results could prove very informative going forward.

As always with these things, I would suggest using OpenOffice and not Excel. I realize that many of you probably have more experience with Excel and have access to it at your work. But using Excel limits the potential users to those willing to pay the exorbitant price AND are using Windows/Mac. OpenOffice is available to everyone, on every platform, and is completely free, meaning that everyone viewing the forums could use it, not just Microsoft Office owners.

Michael


nik
Gold
Gold
Greece
Posts: 151
Joined: 7 years ago
Location: Greece
Region: Europe
Preferred Brewing Method: Single-Vessel All-Grain (SVA)
City: Athens

Post by nik » 6 years ago

datamichael wrote:Looks interesting!

Kudos to your for the time and effort!

As to "No brewing software adjusts your efficiency according to your gravity. ", I completely agree. It's extremely difficult to model. Every equation/spreadsheet I've seen doesn't pan out in the real world. My real world experience is much different than Kai's conversion efficiency table. I question anything that says you can get > 1.085 with first runnings, which is essentially what BIAB is.

But as Pat mentions, a method of recording real results could prove very informative going forward.

As always with these things, I would suggest using OpenOffice and not Excel. I realize that many of you probably have more experience with Excel and have access to it at your work. But using Excel limits the potential users to those willing to pay the exorbitant price AND are using Windows/Mac. OpenOffice is available to everyone, on every platform, and is completely free, meaning that everyone viewing the forums could use it, not just Microsoft Office owners.

Michael
+1 for the Openoffice / Libre office compatibility also might lead some users subjects to software piracy .
Even better is after the initial release to have it as standalone program .
Last edited by nik on 01 Nov 2011, 01:17, edited 5 times in total.


Lylo
Gold
Gold
Posts: 1218
Joined: 6 years ago
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by Lylo » 6 years ago

+4 on the Openoffice
AWOL


McCue
Draft
Draft
Posts: 5
Joined: 6 years ago
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by McCue » 6 years ago

Thanks for the great work, this is very interesting. Would it be possible to post an unlocked version of the spreadsheet so that I can convert it to US units?

User avatar

hashie
Gold
Gold
Posts: 1451
Joined: 7 years ago
Location: Bendigo, Victoria
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by hashie » 6 years ago

We have some amazing people working behind the scenes on this site, Stux is just one of them. Thank you all :)
"It's beer Jim, but not as we know it."


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

Thankyou

The spreadsheet has no password, you can just unprotect the first sheet

It should work in OpenOffice etc except for one macro. The macro whIch calculates the grainbill

If you can make that work in OpenOffice then be my guest ;)

To manually work out the grain bill go to the details tab, then change the grainbill value until the recipe extract difference field is as close to zero grams as possible
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


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

How to write a goal seek macro in open office
http://www.oooforum.org/forum/viewtopic ... k+seekgoal

Then it would be nice to have a cell changed macro too
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

User avatar

thughes
Gold
Gold
United States of America
Posts: 1206
Joined: 6 years ago
Location: Western NY
Region: USA & Canada
Preferred Brewing Method: Single-Vessel All-Grain (SVA)
City: Rochester

Post by thughes » 6 years ago

stux wrote:Thankyou

It should work in OpenOffice etc except for one macro. The macro whIch calculates the grainbill
That's a bummer, I'd be willing to bet that a majority of us are using Open/Libre Office, not MS Office. Hope someone can help with writing a macro.
Last edited by thughes on 01 Nov 2011, 08:52, edited 5 times in total.
WWBBD?


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

datamichael wrote:Every equation/spreadsheet I've seen doesn't pan out in the real world. My real world experience is much different than Kai's conversion efficiency table. I question anything that says you can get > 1.085 with first runnings, which is essentially what BIAB is.
I just tested the CE calc with a 1.100 OG bill.

Which is think is a good high-end gravity? right?

Numbers seem about right, with a 90min boil, I could expect about 10.5L evap, which gets me Start of Boil (ie First Runnings) gravity of 1.072 and a 12KG grain bill for 25L of 1.100 at EOB

I did do a very similar grainbill (12KG) in my pot...

viewtopic.php?f=89&t=628

...and I had 1.078 first runnings... so I think 1.072 is totally within the right ball-park. The experiment would be... IS it correct? :)

Interestingly, if you bump the boil up to 120 mins, then evaporation becomes 14L and now the SOB gravity is only 1.065, which is VERY reasonable

So,

with

System Characteristics:
Evaporation = 14L
Absorption = 1.0L/KG
Conversion Efficiency = 95%

Brew Reqs:
EOB Vol = 25L
Recipe OG = 1.100

then

Grain Bill = 11.631KG
Strike = 44.75
SOB Volume = 40L @ 1.065

And expected Into Boil Efficiency is 73%

Which seems right based on my experience...

If I lower the evap to 10.5L by dropping to 90mins

then the grain bill needs to be bumped up to 11.975KG

total strike is then 41.57L

and the SOB Vol and Gravity at 36.5L @ 1.072, for a total into boil efficiency of just 71%

I achieved an into boil efficiency of 75% for my 12KG grainbill, even though my mash thickness was lower. Probably because I had more than 95% Conversion Efficiency


....

Now, with a 90 min boil, I need to specify an OG of 1.120 to get a SOB gravity requirement of 1.086! Is that really practical? anyway, 66.2% into boil efficiency.

BUT with a 120 min boil you need to target 1.130 to get an SOB SG of 1.085, and again 66.5% efficiency into boil.

Not so sure these numbers aren't right... they sound about right to me ;)

And you're talking 16.63KG into 47.45L, which is 3:1 l:g ratio... which is not so bad
Last edited by stux on 01 Nov 2011, 15:15, 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


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

thughes wrote:
stux wrote:Thankyou

It should work in OpenOffice etc except for one macro. The macro whIch calculates the grainbill
That's a bummer, I'd be willing to bet that a majority of us are using Open/Libre Office, not MS Office. Hope someone can help with writing a macro.
There is also a Worksheet_change hook on both sheets. These hooks call the Grain_Bill_Needs_Update macro whenever a source field is changed. That then triggers the goal-seek.

I have specifically kept all functionality out of the macros that I could. And I'm not sure how the OpenOffice scenario has developed, but it looks like there is a fairly advanced effort to get basic VBA macro support working.

This spreadsheet requires only basic VBA support, and it would probably work with a few tweaks.
Last edited by stux on 01 Nov 2011, 16:24, 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


Pat
Administration
Administration
Posts: 1466
Joined: 7 years ago
Region: Oceania
Preferred Brewing Method: Please select one from below...
City:

Post by Pat » 6 years ago

Stux, are you going to have a bash at getting it to work in the BIABacus? It would be take a lot of thinking I reckon but you like that :P
I'm also just re-reading my last post that was written at the end of a 16 hour day. I see that I wrote...
BIABrewer.info was able to give Stux a collection of figures. A combination of this and the rare science available enabled him to come up with his spreadsheet.
BIABrewer.info only gave stux the figures after he had done three-quarters of his work. The way I wrote that sentence makes it look as though BIABrewer.info instigated the work :lol:. Not so. It is an area that BIABrewer.info always intended to investigate but without stux, this wouldn't have happened for a very long time.

Thanks also to those of you who have said thanks to him (and the BIABacus team). The first BIABacus will not have a heap of the fancy tools seen in other software (mainly because we are currently limited to basic spreadhseet form) but it does have an incredibly solid foundation thanks to the forethought of many brewers and the studiousness/tenacity of people like stux.

For those of you who are worried about the BIABacus being only available in Excel, most of the work and thinking in the BIABacus has been on the layout, design, functionality and future. In other words, hundreds of hours have been spent on turning the grey areas into black and white and thinking everything through in advance. That is very hard. Changing the macros later to suit OpenOffice is all black and white stuff. It will take someone 5, 20 or 40 hours to do but this is just a drop in the ocean as they will have a template to work towards.

If there are any OpenOffice gurus out there, please raise your hand now :P

Pat
Last edited by Pat on 01 Nov 2011, 19:48, edited 5 times in total.
Are you a "Goodwill Brewer?" Pay forward and Buy Some BIPs ;)


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

It should not be a problem to integrate the ideas in the ce calc, but they are experimental and I think they should be proven with at least *one* brew before getting used ;)

So far I have only used the biabrewer data to determine a baseline for biab real absorption

We really do need to put this to the test and get some real data :)
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


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

CE based BIABCalc 2011-11-04.xls
New version of the CE BIABcalc

First thing I did was to do a meta-analysis of 30 brews by 10 brewers to determine a good Real Absorption and Conversion Efficiency figure.

The results are: 1.41 L/KG Real Absorption and Conversion Efficiency of 99%!

(which is approximately the Coarse Grind vs Fine Grind Laboratory efficiency, so there you go)

If you squeeze the hell out of your bag, you can expect about 1.26L/KG Real Absorption.

Next, I found a more accurate figure for Specific Volume of Extract and re-wrote the calculator so it is now 100% based on Weight of Extract.

Everything seems to be jiving with reality now :)

I then added a Sparge parameter. If you perform a dunk sparge, set this to the volume of your dunk and the calculator will work it all out for you.

A dunk sparge is performed by squeezing your bag into your kettle, then dunking it into another pot/bucket of hot sparge water. Stir up the grains and let the dissolved goodness get diluted by the sparge liquor. Then squeeze your bag into the pot, or your kettle and dump the remaining sparge liquor back into your kettle.

You might want to use a dunk sparge if you are brewing a high gravity beer, or if your mash won't otherwise fit into your kettle.

I also added a pretty graph which will visualize your losses ;)
Screen Shot 2011-11-04 at 7.05.16 PM.png
...

What does this mean?

This is a calculator which will accurately* predict your brew as long as you know your Total Boil Off.

No guestimating absorption or efficiency is required.

--
*accuracy is yet to be tested and is currently dependant on your grain having the same potential extract as all BIAB calculators assume. You still need to have good mashing practise in order to achieve the expected Conversion Efficiency, but BIABbrewer results indicate that this is the norm.
You do not have the required permissions to view the files attached to this post.
Last edited by stux on 04 Nov 2011, 16:14, 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


PistolPatch
Gold
Gold
Australia
Posts: 5295
Joined: 7 years ago
Location: Perth, Western Australia
Region: Oceania
Preferred Brewing Method: Single-Vessel All-Grain (SVA)
City:

Post by PistolPatch » 6 years ago

I can see you have had to do a heap of research and thinking on this stux. Having a tool that adjusts grain bill and estimates efficiency depending on strike water volume and evaporation rate is brilliant.

:clap: :thumbs: :champ:
PP
If you have found the above or anything else of value on BIABrewer.info, consider supporting us by getting some BIPs!


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

I did a brew according to the CE Calc today.

Hit my numbers spot on!

(I calculated a potential extract %ge for the exact grain bill, and did everything as accurately as I could because I was testing the calculators core assumptions.

More later.
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


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

I just tested the CE Calc in LibreOffice 3.4.3 on Mac OS X Lion and all you need to do is enable Macros in the Security settings and everyhting appears to work fine.

In settinges -> LibreOffice -> Security, click Macro Security..., then set Macro Security to Medium.

This will mean that you will be prompted to activate Macros when you open the sheet.

The grain calculation Macro and the Sparge sheet change macro all appear to work fine.

And that's pretty much all the macros which are essential :)

The Extract chart doesn't have any labels... not sure how to fix that.
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


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

Just tested it in LibreOffice 3.3.4 and the Macros don't work. If the macros are working, then when you change the End of Boil Volume the grain bill should update, and when you change the sparge volume from zero to 10 or back again, the Runoff Fields should appear/disappear

In fact, it appears that LO 3.3.4 can't even *see* the VBA macros.

BUT the funny thing is the chart looks much better. If you save the document then when I open it in excel the chart is screwed up a bit.

So, if you want to use this sheet with LibreOffice, then use it with 3.4.3
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


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

Tried OpenOffice 3.3.0

For all intents and purposes it seems to be the same as LibreOffice 3.3.4

Ie, no VBA macro support.

Perhaps there is a plugin for it? I wouldn't know.

Anyway, LibreOffice 3.4.3 is free, the macros work in it, so no excuse not to use it instead of Excel ;)
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


Topic author
stux
Gold
Gold
Posts: 1284
Joined: 7 years ago
Location: Lower Blue Mountains, NSW, Australia
Region: Please select one...
Preferred Brewing Method: Please select one from below...
City:

Post by stux » 6 years ago

I tested the CE based BIABcalc by doing an NS Ale brew on Sunday :)

The quick answer is that I've never had a brew be so spot on!

The only real problem I had was that I didn't wait for my hydrometer samples to cool down, and instead used a converter, and the calculations apparently weren't accurate.


....


Okay, So I decided to brew a Nelson Sauvin Summer Ale. The plan was to use the defaults I had established of 99% and 1.41L/KG for Conversion Efficiency and Absorption.

The only system specific parameter I needed was my evaporation rate, which is about 7L/hr, so 10.5L for a 90 min boil. I have actually had 11L for a 90min brew before, depends on atmospheric conditions I think.


....

Because I was going to be testing the accuracy of the calculator's predictions with this I decided to work out the actual potential from the grain, as per malt specs.

My base recipe is 4.5KG of Thomas Fawcett Golden Promise and 400g of Weyermann's Pale Wheat Malt made up to an OG of 1.050

I used the grains tab in the CE Calculator to work out the actual laboratory potential for the grains. Normally you'd just use a preset, but I wanted exact :)
Screen Shot 2011-11-07 at 3.05.37 PM.png
The Fine Grind Dry Basis % for Wey Wheat is 84.2% with Moisture content of 4.4%

The FGDB% for Golden Promise was 79.4% with Moisture content of 3.3%

The grist calculator calcuated 77.083% extract potential. Closer enough to 77.1%

The current BIABacus defaults would've been 79.43%, so this was worth doing for accuracy, because I would be undershooting my grain if I was expecting 79.43% extract potential from grain with only a 77.1% extract potential.

When I asked the CEcalc to target 25.5L of 1.050 wort it spat out a 5.25KG grain bill, you can see the grist tab has 4.820KG of Golden Promise and 430g of Wey Wheat. So that is what I used.

Total Strike was 40.5L, Total grain bill was 5.25KG and we were aiming for 25.5L of 1.050 wort.

....

So commence the brew :)

I did the mash, and measured the mash gravity at the end of mashout, before pulling the bag.

1.036, which matched my Start of Boil gravity, 1.036, thus confirming that Mash gravity should be the same as Start of Boil gravity, which nicely confirms one of my assumptions in this calculator's design.

...

I actually only achieved 1.034 for Start of Boil/Mash Gravity, but that reading was taken at 50C and I believe was out by about 2 points, as I will show later ;)

...

So, pulled the bag, squeezed and squeezed... till hardly anything was coming out, and dumped it.

Start of Boil volume was 37.6L and gravity was 1.034 (actually higher, but I didn't wait to cool the sample because of all the samples I was taking! and not wanting to get involved with ice baths... I really should start using my refractometer more)

Target was actually 37L, so I had an extra 600ml.

Which means my absorption was 1.3L/KG not 1.41L/KG, but as I said in an earlier post, if you squeeze the bajeezus out of your grain, you should get about 1.26L/KG, so I was close to that.

(more on the non-difference that absorption makes later)

According to the calculator, with original readings, my achieved Conversion Efficiency was 93.8% and my Into Boil Efficiency was 78.8%

I was fairly disheartened by this... as I was expecting about 83% into boil efficiency, damn. Oh well, we'll just use 95% CE in future... but my research had led me to be fairly certain that 99% was the right efficeincy.

Anyway, boil continues... end of boil readings...

26.9L, when I wanted 26.5L, not too bad...

Actual evaporation was 10.7L not 10.5L and the absorpiton was 110ml/KG less.

OG was 1.048, and my EOB efficiency matched my SOB efficiency, which was all making sense and is how things are supposed to be................



..................Except I waited for the sample to cool properly, and at 20C, it actually read 1.050! Oh happy days ;)

So, I hit my OG perfectly, and my End of Boil volume was just +400ml... which means MORE BEER than planned for ;)

Which means I hit my 83% Into Boil efficiency. And if I bumped the Start of Boil gravity up to 1.036 so I can get the same Into Boil efficiency as End of Boil efficiency (which is correct), then my conversion effciency was actually 99%!

Which is exactly what it was supposed to be!

So, I'm fairly certain, that if I had properly cooled the samples, everything would read as it should, and I'm pretty much bang on my gravity and my volume is a mere 400mls out... which is just an extra glass of beer, as its full strength wort :)

Now, my absorption was out by 110ml/KG. This is not a problem because all this means is I get more first runnings that I was expecting, and as I have shown, first runnings are at your Start of Boil gravity, which means you just end up with slighty more wort than you were expecting. If you're evaporation is slightly stronger than you were expecting and your absorption is slightly less, then it all balances out nicely, so its best to overestimate absorption (1.41L) and underestimate boil off, as this will lead to, at worst, as stronger wort at End of Boil, which can be diluted into the fermenter if necessary.



So, I cubed 24L of hot wort, and had about 2.5L left over. After sieving I have 2.25L of kettle dregs which I'll use the non-break portion to make a starter.

So, here is a pretty chart
Screen Shot 2011-11-07 at 3.23.56 PM.png
You do not have the required permissions to view the files attached to this post.
Last edited by stux on 07 Nov 2011, 12:43, 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 Reply

Return to “Measurement, Mathematics and Records”

Brewers Online

Brewers browsing this forum: No members and 5 guests