Quantcast

Pre-Boil Gravity Calculations

HomeBrewTalk.com - Beer, Wine, Mead, & Cider Brewing Discussion Community.

Help Support Homebrew Talk:

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
I am currently putting the finishing touches on my AG brewing spreadsheet in Excel and i've hit a snag.

Currently I am calculating PBG in my pre-brewday estimation section by using the following calculation:

((Grain points total*efficiency)+(points of sugar))/Pre-boil volume

This equation is yielding expected results given the other equation in my sheet. It's when I attempt to calculate efficiency in my actuals (brew day values) section that I hit a snag: I had calculated the points contribution of the sugars separate from the grains because thier contribution is 100% efficient, yet actual efficiency is the quotient of measured PBG and grain potential. I am effectively getting a lower efficiency in my actuals section that the estimation section.

I know its a simple fix but if anyone can chime in on this it would be much appreciated.
 

balrog

Supporting Member
HBT Supporter
Joined
Nov 26, 2013
Messages
3,764
Reaction score
3,432
I guess I need an example.
Estimated section has x lbs grain, y lbs sugar, you multiply by "efficiency" only the grains, add 100% efficiency for sugars and divide by volume.

Now you're saying for actuals, you're trying to....what? Unwind/reverse the equation to take what you've measured and back-calculate only the grain's "efficiency"?
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
I guess i'm looking for the proper equation for calculating mash efficiency. I would like to, as a confirmation of my calcs, to verify the efficiency in both sections match with the same numbers inputted.

My calculation I have saved in my documents and burned in my brain is:

measured pre-boil gravity points/maximum gravity points
 

McKnuckle

Supporting Member
HBT Supporter
Joined
Feb 10, 2014
Messages
3,231
Reaction score
2,656
Location
Anywhere But Here
Since the sugars are 100% utilized, subtract their points from both sides of the efficiency division equation (dividend and divisor).

Simple example; if your potential is 50 gravity points with grain only and you achieve 40 gravity points, your efficiency is 40/50 = 80%.

If your potential is 50 gravity points but 5 of those are from sugar, and you achieve 40 gravity points, then you actually got 35/45 = 77.8%.
 

balrog

Supporting Member
HBT Supporter
Joined
Nov 26, 2013
Messages
3,764
Reaction score
3,432
Since the sugars are 100% utilized, subtract their points from both sides of the efficiency division equation (dividend and divisor).

Simple example; if your potential is 50 gravity points with grain only and you achieve 40 gravity points, your efficiency is 40/50 = 80%.

If your potential is 50 gravity points but 5 of those are from sugar, and you achieve 40 gravity points, then you actually got 35/45 = 77.8%.
Thank you. Was trying to show reversal with taking sugar out. Good simple example.
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
That makes perfect sense. Thank you.

If anyone is interested, i'll post the sheet when I get home.

I'm sure people need another calculation tool like they need a hole in their head, but what the hell.

:mug:
 

balrog

Supporting Member
HBT Supporter
Joined
Nov 26, 2013
Messages
3,764
Reaction score
3,432
That makes perfect sense. Thank you.

If anyone is interested, i'll post the sheet when I get home.

I'm sure people need another calculation tool like they need a hole in their head, but what the hell.

:mug:
PFSH! I *ALWAYS* look at other people's and pick up tips here and there from any version. I would have never thought of everything, so more input is better, and thanks for sharing.
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
i'm still off even after those suggestions. I'll post the sheet and take a look a little later.
 

pricelessbrewing

Brewer's Friend QA Tester
Joined
Jan 6, 2014
Messages
2,251
Reaction score
489
Location
Chicago Subs
Let me know when you get the sheet up, I'll take a look too.

The above should of fixed it, alternatively you could separate the points into gravity from grains, and gravity from sugar. Estimated pre boil is the sum of these.

mash eff = Estimated Efficiency * ((measured OG * actual PreBoil volume / estimated preboil volume) -(points from sugar * estimatedvolume/Actualvolume )) / Estimated Preboil OG from grains

Quick sanity check example
Estimated:
OG grains 70
OG sugar 5
volume 6.5
efficiency 72

Measured:
OG (total) 75
Volume 7

adjusted OG (total): 77.885 (higher volume with same OG, should be higher)
Adjusted sugar: 4.815 (higher volume, so should be less, yay)
Adjusted OG from grains=Total-Sugar=73.07 (still good)
Effeciency multiplier (1.044854)
1.044854*72=75.16%. Higher as expected
 

McKnuckle

Supporting Member
HBT Supporter
Joined
Feb 10, 2014
Messages
3,231
Reaction score
2,656
Location
Anywhere But Here
In my personal spreadsheet, I have separate lists for grain and sugar-based fermentables. This way, I can isolate my pre-boil gravity - the concept of which only applies to grain - and see the points that are variable (grain) vs. the part that's fixed (sugar). I guess this works for me because I add sugars near flameout or even in primary; in any case, it's long after the pre-boil phase.

The formula I use to calculate how many pre-boil points I'll get from a grain is:

((Weight in Pounds x PPG) / Boil Volume in Gallons) x Mash Efficiency

And for OG it's the same thing with the post-boil volume in the equation (before ANY losses):

((Weight in Pounds x PPG) / Ending Volume in Gallons) x Mash Efficiency

For sugars, it's the latter equation but delete the Mash Efficiency multiplication step at the end:

(Weight in Pounds x PPG) / Ending Volume in Gallons
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
Ok here it is everybody.

There is quite a bit going on and i am pretty confident everything is correctwith the exception of the actual measured efficiency portion.

:mug:
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
In my personal spreadsheet, I have separate lists for grain and sugar-based fermentables. This way, I can isolate my pre-boil gravity - the concept of which only applies to grain - and see the points that are variable (grain) vs. the part that's fixed (sugar). I guess this works for me because I add sugars near flameout or even in primary; in any case, it's long after the pre-boil phase.

The formula I use to calculate how many pre-boil points I'll get from a grain is:

((Weight in Pounds x PPG) / Boil Volume in Gallons) x Mash Efficiency

And for OG it's the same thing with the post-boil volume in the equation (before ANY losses):

((Weight in Pounds x PPG) / Ending Volume in Gallons) x Mash Efficiency

For sugars, it's the latter equation but delete the Mash Efficiency multiplication step at the end:

(Weight in Pounds x PPG) / Ending Volume in Gallons
This is what I'm trying to do. You can see from the sheet that I separated the two sections for this reason.
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
Let me know when you get the sheet up, I'll take a look too.

The above should of fixed it, alternatively you could separate the points into gravity from grains, and gravity from sugar. Estimated pre boil is the sum of these.

mash eff = Estimated Efficiency * ((measured OG * actual PreBoil volume / estimated preboil volume) -(points from sugar * estimatedvolume/Actualvolume )) / Estimated Preboil OG from grains

Quick sanity check example
Estimated:
OG grains 70
OG sugar 5
volume 6.5
efficiency 72

Measured:
OG (total) 75
Volume 7

adjusted OG (total): 77.885 (higher volume with same OG, should be higher)
Adjusted sugar: 4.815 (higher volume, so should be less, yay)
Adjusted OG from grains=Total-Sugar=73.07 (still good)
Effeciency multiplier (1.044854)
1.044854*72=75.16%. Higher as expected
This may be even closer to what I was after, as I plan on adding sugars pre-boil.
 

pricelessbrewing

Brewer's Friend QA Tester
Joined
Jan 6, 2014
Messages
2,251
Reaction score
489
Location
Chicago Subs
That's what I gathered from your post, as long as you don't put it in with the grains you'll be okay although I would probably do it near flame out (maybe 10-5min).
 

balrog

Supporting Member
HBT Supporter
Joined
Nov 26, 2013
Messages
3,764
Reaction score
3,432
I was able to view it but will not be able to go through it until tomorrow. Thanks.
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
Here is what I am using for my estimation section. I am pretty sure they are accurate but I've been wrong before and welcome constructive criticism.

For Pre-boil Gravity:

(((Points(Grain))*Efficiency(Estimated))+Points(Sugar))/Volume(Pre-boil)

For Original Gravity:

(Volume(Pre-boil)*Gravity(Pre-boil))/Volume(Recipe)

For Final Gravity:

Gravity(Original)*(100%-Apparent Attenuation%)
 

stpug

Well-Known Member
Joined
Nov 5, 2012
Messages
3,821
Reaction score
765
It looks to me like your equations in the spreadsheet are correct for this form of gravity calculation.

67.7 points x 1.35 preboil gallons = 91.395 points @ 1 gallon (i.e. 1.091)
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
I have been able to match the two sets of calculations so that with the same values I get equal efficiency. Small win.

In doing so, I have discovered that there is some definite undesirable interplay between my calculations. I'll have to carefully recalculate each section to eliminate it.

After doing so I need to add the color calculation. Ultimately it will be a pretty useful tool for me to use during AG brew days and the days of prep leading up to them.
 

jbaysurfer

Former future HOF Brewer
Lifetime Supporter
Joined
Nov 7, 2011
Messages
2,916
Reaction score
588
Location
Santa Barbara

stpug

Well-Known Member
Joined
Nov 5, 2012
Messages
3,821
Reaction score
765
I have been able to match the two sets of calculations so that with the same values I get equal efficiency. Small win.

In doing so, I have discovered that there is some definite undesirable interplay between my calculations. I'll have to carefully recalculate each section to eliminate it.

After doing so I need to add the color calculation. Ultimately it will be a pretty useful tool for me to use during AG brew days and the days of prep leading up to them.
If you're looking for utmost accuracy in your calculations then you may want to look at some of AJ Delange's ( @ajdelange ) recent posting on calculating extract potential from grains. He uses a much more fine tuned method (and calculations) that take into account many more variables, specifically moisture of grain.

Edit:
Have a look through this thread for some starters:
https://www.homebrewtalk.com/showthread.php?t=527449 (page 4, posts 36 and 37 get detailed)
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
Try this:

(Rempved Dead Link)

Should be able to open it in excel and preserve everything.
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
Yup! Worked! Thanks. I'll look it over when I get the chance. Always appreciate someone sharing their work!
Made some major corrections to the calculations and the formatting. Seems to be working fine and outputting as expected.

There are separate sections for grain and sugar inputs, separate sections for estimated and actual gravity and efficiency calcs, a step infusion section with temperature and boiling water infusion calculators, IBU section with utilization table and interpolation calculator, a bottling calculator (Volume to bottle yield), mash tun sizing, batch sparging "equal runnings" calculator, losses section and ABV/ABW calculator.

I revised the step mashing section to reflect the fact that I have converted all the quarts measurements to gallons for water inputs.

Try this link to view and edit the file:

https://drive.google.com/open?id=0B8F3C1boHghNb29qZHo1R2RjcVk&authuser=0

Enjoy:mug:
 

pricelessbrewing

Brewer's Friend QA Tester
Joined
Jan 6, 2014
Messages
2,251
Reaction score
489
Location
Chicago Subs
Glad to see you got it worked out, looks accurate. Only thing I would add would be to separate some stuff onto other sheets instead of scrolling down so much.

Maybe do a vlookup or index-match for a malt database, I have NO idea what the malt gravity potentials are for all the malts I have to use, and don't really want to have to look them up to use this.

What is "Mash Topoff" never seen the term, formula listed = sparge volume - (strike water - grain absorption). On a side note, that parenthesis is pointless...

Seems pretty functional, although the process of using it seems to require lots of looking up malt potentials, yeast AA% etc, and going all over the place on the spreadsheet.
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
Mash topoff is the amount of water you add before your first runnings in a batch sparge to ensure equal runnings.

Interesting that you bring the lookup option to light. I am trying to figure out a way to not only do that but also tie in the hop utilization table to user inputs. As of now, you have to manually input your utilization value for the IBU equation.

Taking your input and running with it, it may be a good idea to, on a separate sheet, have a list with general values for types of malts that can be pulled into the main sheet when user input matches a certain value. As far as apparent attenuation is concerned, that is merely there for estimation purposes. Knowing an exact value may not be that useful in the grand scheme of things.

It is definitely spread out, although auto filling the malt potentials would really streamline it. Ultimately, you don't really have to enter much. A glossary or legend for the actual user inputs may be useful as well.

Thanks for the input!
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
Tried out the Index and Match commands and they work great! Now it's just about integrating it across the sheet.

I also think that I may do as suggested and breakup the sheet into sections. Using the sheet referencing command I can split up the calcs into pertinent sections and reference them throughout. Now that the calculations themselves seem to be in order I can concentrate on the referencing.
 

pricelessbrewing

Brewer's Friend QA Tester
Joined
Jan 6, 2014
Messages
2,251
Reaction score
489
Location
Chicago Subs
Sounds like a good plan to me. I love me some index match stuff, way more efficient of processing than vlookup. The main downside that I've encountered is if you have multiple entries for the match criteria it gets a little complicated, but you can get around it with some array formulas.
 

stpug

Well-Known Member
Joined
Nov 5, 2012
Messages
3,821
Reaction score
765
Tried out the Index and Match commands and they work great! Now it's just about integrating it across the sheet.

I also think that I may do as suggested and breakup the sheet into sections. Using the sheet referencing command I can split up the calcs into pertinent sections and reference them throughout. Now that the calculations themselves seem to be in order I can concentrate on the referencing.
Are you keeping the updates posted somewhere on HBT or elsewhere? Just wondering how the updates are going. :mug:
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
I will get an updated copy on the site. I have since incorporated a water software into it but will post just my portion of the sheet for copyright reasons.

I have taken all the considerations suggested though and now all recipe and gravity and IBU Calcs self populated by using a standard naming convention for grain and hops inputs.
 

stpug

Well-Known Member
Joined
Nov 5, 2012
Messages
3,821
Reaction score
765
I will get an updated copy on the site. I have since incorporated a water software into it but will post just my portion of the sheet for copyright reasons.

I have taken all the considerations suggested though and now all recipe and gravity and IBU Calcs self populated by using a standard naming convention for grain and hops inputs.
Sounds very cool and like you've come a long way. That's too bad you have to remove the water portion. Have you looked into maybe using Q-water as an open-source alternative to your copyrighted version. Maybe send the author a note and see what he says. I'll track down the post on here with his spreadsheet and username.

Here we are: https://www.homebrewtalk.com/showthread.php?t=515431
 

stpug

Well-Known Member
Joined
Nov 5, 2012
Messages
3,821
Reaction score
765
Oh, before I forget:

You should start up a thread in the brewing software forum with your calculator/spreadsheet and good/useful title. I had the hardest time tracking this thread down yesterday when I was looking. It would probably get better "air time" if it was in that forum with a good, clear title.
 
OP
Derek1985

Derek1985

Well-Known Member
Joined
May 20, 2015
Messages
256
Reaction score
30
Location
Syracuse
Will do. Trying to get a blogger page up and running so I can have a place to link for info and pictures
 
Top