Post #76 made 12 years ago
Which version of libreoffice are you using?
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 #77 made 12 years ago
3.4.4


BTW I played around with this today and found the sheet events in LibreOffice. Setting the 'Content changed' macro to Grain_Bill_Needs_Update for the calc and grains sheets seems to make it work again. Enough to get me going for now at least.

Post #78 made 12 years ago
quantegy wrote:3.4.4


BTW I played around with this today and found the sheet events in LibreOffice. Setting the 'Content changed' macro to Grain_Bill_Needs_Update for the calc and grains sheets seems to make it work again. Enough to get me going for now at least.
So, I played with this a bit in 3.4.3 today.

If I save the .xlsm in LibreOffice it seems to remove the VBAProject macros. Which basically destroys the spreadsheet.

Of course, you only notice once you re-open the spreadsheet. Until then it works!

I tried saving in ODS and .xslx format too. LibreOffice doesn't seem to support .xslm which is the excel xml based macro sheet.

If I tried to save as .xls it crashed LO

If I saved as .xls in excel, then that did open in LO, and if I then resaved it did seem to preserve the macros, as can be witnessed by the prompt to enable macros (which I was not getting the other times)

The problem is the macros don't work because LO seems to have done something to the named ranges.

Sigh.
Last edited by stux on 11 Jan 2012, 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

Post #80 made 12 years ago
stux wrote: So, I played with this a bit in 3.4.3 today.

If I save the .xlsm in LibreOffice it seems to remove the VBAProject macros. Which basically destroys the spreadsheet.

Of course, you only notice once you re-open the spreadsheet. Until then it works!

I tried saving in ODS and .xslx format too. LibreOffice doesn't seem to support .xslm which is the excel xml based macro sheet.

If I tried to save as .xls it crashed LO

If I saved as .xls in excel, then that did open in LO, and if I then resaved it did seem to preserve the macros, as can be witnessed by the prompt to enable macros (which I was not getting the other times)

The problem is the macros don't work because LO seems to have done something to the named ranges.

Sigh.
Well, I have 3.4.4 so I don't know how it's different, but I saved it in ODS format and the vba macros are still there. As far as I could tell the only thing that stopped working was the worksheet_change event, but I know practically nothing about excel and spreadsheets. See my previous post for a workaround that seems to handle it decently.

Anyway, I did a brew today using this spreadsheet and it was great, everything was pretty much dead on. This was my third brew, the first two I did using the regular Calculator. I can post a more complete report tomorrow if you would like.
Last edited by quantegy on 11 Jan 2012, 16:42, edited 5 times in total.

Post #81 made 12 years ago
quantegy wrote:
stux wrote: So, I played with this a bit in 3.4.3 today.

If I save the .xlsm in LibreOffice it seems to remove the VBAProject macros. Which basically destroys the spreadsheet.

Of course, you only notice once you re-open the spreadsheet. Until then it works!

I tried saving in ODS and .xslx format too. LibreOffice doesn't seem to support .xslm which is the excel xml based macro sheet.

If I tried to save as .xls it crashed LO

If I saved as .xls in excel, then that did open in LO, and if I then resaved it did seem to preserve the macros, as can be witnessed by the prompt to enable macros (which I was not getting the other times)

The problem is the macros don't work because LO seems to have done something to the named ranges.

Sigh.
Well, I have 3.4.4 so I don't know how it's different, but I saved it in ODS format and the vba macros are still there. As far as I could tell the only thing that stopped working was the worksheet_change event, but I know practically nothing about excel and spreadsheets. See my previous post for a workaround that seems to handle it decently.

Anyway, I did a brew today using this spreadsheet and it was great, everything was pretty much dead on. This was my third brew, the first two I did using the regular Calculator. I can post a more complete report tomorrow if you would like.
I would like that, thanks :)
Last edited by stux on 12 Jan 2012, 07:51, 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 #82 made 12 years ago
A little backstory about my limited brewing history:

The first two batches I did were the same, since I do around 9.5 liter batches. I split a full size kit in half and did it twice, using the Calculator. Both times I pretty much hit my preboil gravity but my volume was a lot lower than expected. I figured it was somehow absorption related, but I wasn't sure what to set it as for my next batch because at the time I think I had some confusion with the 20C-100C expansion. I also didn't understand why increasing my strike water for the same amount of grain would fix my absorption problem without screwing up the preboil gravity. After finding this thread and reading about true/apparent absorption here and on braukaiser.com it made a lot more sense so I decided to use this calculator for this batch.

This time I did an Anchor Steam clone, converting it from a 19 liter recipe I found. I also ran it through the regular Calculator, and Beersmith, just as sanity checks. They were pretty similar.

My settings in the CE calculator:

I tried to fill in the specs for the actual grain I got but couldn't find one and didn't know the other. So I used what I thought was closest. The recipe was 91.72% Pale Ale (FGDB 80 and moisture 4) and 8.28% Crystal 60L (FGDB 73 and moisture 5.5).

Target fermenter volume was 11 L at 1.051 OG, it told me 2.731 KG total for grain.

I used Real Absorption at 1.33 (almost went to 1.41 based on my previous experience but resisted after reading the thread again)

Strike water was 21.14 L with 6 L evaporation and 2 L kettle loss.

Mashed at 152*, with a mashout to 170* after (not sure how much this helps, I'll probably try skipping it next time)

Pulled up the bag, twisted it up until the main flow slowed, then put it in a colander and pressed a plate onto it for a few seconds (not too much, it was just too hot to squeeze).

Poured that runoff into the kettle, brought it to a boil and measured (cool sample):

Preboil volume 19.58 L at 100C (expected 19.52)
Preboil gravity 1.034 (expected 1.035)
Preboil Efficiency 79.9% (expected 82.9%)

Conversion efficiency was 95%, which I am fine with at this point.

I was also doing the efficiency analysis from braukaiser.com alongside the brew so at this point I combined the grain with 5.44 L of cool water and took the SG of that, which it uses to check the lauter efficiency. The SG of this was 1.013. All the efficiencies in this spreadsheet matched the CE calculator pretty closely.

End of boil was 13.6 L at 100C (expected 13.52 L) at 1.051 for an efficiency of 83.4%. Just slightly over 13 L at 20C so it was right on.

I attribute the mismatch to measuring errors, perhaps the preboil SG was 1.035 in which case conversion efficiency would be 97.9% and preboil would be 82.3%. Those hydrometers are tough to read.

Actual real absorption was pretty close at 1.31 L/KG, so my missing preboil volume seems solved - the default value in the Calculator was just way low for me. I find the true absorption to be a better metric even though it is not immediately measurable, especially if you are using the actual potential of your grains.

I ended up with double the expected amount of loss to kettle trub (maybe time to get a hop sock), but instead of losing that extra 2 L of wort I ended up straining it and gaining 0.3 L instead for a final total of 11.3 L at 72.1% into fermenter. A huge improvement since my previous two batches were 57% and 60%.


I will definitely be using this again, so thanks for all the work.

Post #84 made 12 years ago
quantegy wrote:A little backstory about my limited brewing history:

The first two batches I did were the same, since I do around 9.5 liter batches. I split a full size kit in half and did it twice, using the Calculator. Both times I pretty much hit my preboil gravity but my volume was a lot lower than expected. I figured it was somehow absorption related, but I wasn't sure what to set it as for my next batch because at the time I think I had some confusion with the 20C-100C expansion. I also didn't understand why increasing my strike water for the same amount of grain would fix my absorption problem without screwing up the preboil gravity. After finding this thread and reading about true/apparent absorption here and on braukaiser.com it made a lot more sense so I decided to use this calculator for this batch.

This time I did an Anchor Steam clone, converting it from a 19 liter recipe I found. I also ran it through the regular Calculator, and Beersmith, just as sanity checks. They were pretty similar.

My settings in the CE calculator:

I tried to fill in the specs for the actual grain I got but couldn't find one and didn't know the other. So I used what I thought was closest. The recipe was 91.72% Pale Ale (FGDB 80 and moisture 4) and 8.28% Crystal 60L (FGDB 73 and moisture 5.5).

Target fermenter volume was 11 L at 1.051 OG, it told me 2.731 KG total for grain.

I used Real Absorption at 1.33 (almost went to 1.41 based on my previous experience but resisted after reading the thread again)

Strike water was 21.14 L with 6 L evaporation and 2 L kettle loss.

Mashed at 152*, with a mashout to 170* after (not sure how much this helps, I'll probably try skipping it next time)

Pulled up the bag, twisted it up until the main flow slowed, then put it in a colander and pressed a plate onto it for a few seconds (not too much, it was just too hot to squeeze).

Poured that runoff into the kettle, brought it to a boil and measured (cool sample):

Preboil volume 19.58 L at 100C (expected 19.52)
Preboil gravity 1.034 (expected 1.035)
Preboil Efficiency 79.9% (expected 82.9%)

Conversion efficiency was 95%, which I am fine with at this point.

I was also doing the efficiency analysis from braukaiser.com alongside the brew so at this point I combined the grain with 5.44 L of cool water and took the SG of that, which it uses to check the lauter efficiency. The SG of this was 1.013. All the efficiencies in this spreadsheet matched the CE calculator pretty closely.

End of boil was 13.6 L at 100C (expected 13.52 L) at 1.051 for an efficiency of 83.4%. Just slightly over 13 L at 20C so it was right on.

I attribute the mismatch to measuring errors, perhaps the preboil SG was 1.035 in which case conversion efficiency would be 97.9% and preboil would be 82.3%. Those hydrometers are tough to read.

Actual real absorption was pretty close at 1.31 L/KG, so my missing preboil volume seems solved - the default value in the Calculator was just way low for me. I find the true absorption to be a better metric even though it is not immediately measurable, especially if you are using the actual potential of your grains.

I ended up with double the expected amount of loss to kettle trub (maybe time to get a hop sock), but instead of losing that extra 2 L of wort I ended up straining it and gaining 0.3 L instead for a final total of 11.3 L at 72.1% into fermenter. A huge improvement since my previous two batches were 57% and 60%.


I will definitely be using this again, so thanks for all the work.
Thanks for documenting such a good work out :)

I've been testing sparge water gravity as well, and I'm finding the results are good too :), on my last brew the gravity of the sparge water was exactly as predicted, previous brew it was 1 point out, which is easily within the margin of error on a hydrometer reading

I think one one of hte more interesting things is how much of a difference hop matter makes to kettle loss. For example I did a triple batch with just 15 IBUs from a 60 min additions, so not much hops... thus not much kettle loss...

and I did an APA, with 35 IBUs, 15g, 30g, 60g, 90g additions, and that one had almost double the kettle loss! perhaps a hop bag would be a great solution, but I just don't seem to get around to it ;)
Last edited by stux on 11 Mar 2012, 16:20, 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 #85 made 12 years ago
Stux,

Really like your calculator. I've been using it since I began all-grain 8 batches ago. I've dialed in my measuring methods in and it's pretty amazing how your calculations match up to what I measure.

I've been using it backward at the end of brew day because I'm not sure of kettle loss and evaporation yet so I plug in my real losses for kettle/evaporation and it ends up so close on volumes and mass of grain it's scary! The lautering loss compared to liquor to grain ratio you discussed here is about dead on with Taddy and pretty close with the Mild.

Here are my last two batch results plugged into your calculator if interested, the reason my liter measurements are so exact are from converting from gallons and pounds. For reference, I perform measurements in the following manner:

Starting water: Weigh pot
Start of boil water: Ruler
End of boil water: Ruler
Into fermenter: Weigh fermenter, taking into account the reduced weight of wort as it now contains sugar
All gravity measurements: Calibrated refractometer

Only thing I can't get to match is the initial water required.

Code: Select all

              Used     CE Calc
Mild (1.041)  29.49L    30.04L
Taddy(1.054)  30.20L    31.32L
Larger initial water requirements also seems to happen with Beersmith and the standard BIAB calculator. Know why that would be? Looks like it may increase with the OG of the wort. Also, I have a low evaporation rate due to using a pan floating on top to reduce energy required, but that looks like it's accounted for in your calculations.
You do not have the required permissions to view the files attached to this post.
Last edited by tolonen on 22 May 2012, 23:05, edited 5 times in total.
    • SVA Brewer With Over 50 Brews From United States of America

Post #86 made 12 years ago
Awesome :)

I think I have a partial theory. The grain has moisture in it and that is about 4-5% which means a 5Kg grain bill adds an 250ml or so of water that's not accounted for.
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 #87 made 9 years ago
I still use my calculator, unmodified to calculate the water and grainbill requirements for all my batches.

The thing that came up is that the grain specifications became the critical factor in achieving more reliable predictions, and I found as grain got older the measured conversion efficiency went down. My theory is that the grain was actually getting more moist over time.

At the end of the day, I don't really see much point in attempting to further refine the maths, when its impractical to have the grain analysis entered to the level of precision required for the more advanced math to actually make a point.

End of the day, the best thing to do is just fiddle with the conversion efficiency percentage, and run with the numbers, they're so close to being right that any deviation is no more than batch to batch deviation would be.

These days, I'm focussing on going to a 3V system (purely because of my +60L batch size has become impractical with BIAB+Dunk sparges), and after that I'll be going to a recirculating 1V system, similar to a braumeister.

That's the plan anyway :)

Who knows, I may apply some of this CE based calculations to the 3V problem... but the really fascinating thing is that I haven't worked out how to model a fly sparge ;)
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 13 guests

cron