Pre-Boil Gravity Calculations

Brewing Processes
1

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.

2

Without seeing your actual equation, it’s hard to find your error.

I’d imagine it’s a cell referencing issue.  I guess it could also be an issue with how you’re calculating the grain potential and the sugar potential.

Again, without seeing the actual calculation formula, I’m shooting in the dark.

Edit: After rereading your post, I see you’re actual is estimating less than the estimated, so are you including sugar in your estimated calculation and not in your actual brewday formula?  Otherwise, I would consider the following conditions leading to an underestimation of actual preboil gravity:  too large preboil volume, lower grain efficiency, or lower grain/extract potential.  You should verify your actual values are matching the estimated values for an apples-to-apples comparison.

3

https://onedrive.live.com/redir?resid=B484D5820D3C3D11!811&authkey=!AKnMKX27mIMqR7c&ithint=file%2Cxlsx

This links to my OneDrive folder. You should be able to save it to a desktop and view and edit.

4

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:

(((PointsGrain)*EfficiencyEstimated)+PointsSugar)/VolumePre-boil

For Original Gravity:

(VolumePre-boil*GravityPre-boil)/VolumeRecipe

For Final Gravity:

GravityOriginal*(100%-Apparent Attenuation%)

5

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.

6

I do not see any problems with your equations other than a possible loss of precision by basing your original gravity calculation on your pre-boil calculation.

If you are adding sugar, then you may need to perform real-world tests to calibrate your sugar potential values.

Are your final values calculated along the lines?

gravity_points(gravity) = gravity - 1 * 1,000

gravity_points_contributed_by_sugars = for each sugar in sugar_additions, sum(sugar.weight * sugar.gravity_points)

total_grain_potential_in_points = for each grain in grist, sum(grain.weight * grain.gravity_points)

efficiency_percentage = (volume_in_gallons * gravity_points(measured_gravity) - gravity_points_contributed_by_sugars) / total_grain_potential_in_points * 100

With that said, it is not uncommon for actual values to be off because the predicated efficiency was optimistic or one’s hypothetical potential values are not accurate.  Additionally, it is possible that one’s actual extraction efficiency is less than the calculated value due to using hypothetical grain potential values that are too low for the actual grain in use.  Extraction efficiency should not vary with grist because extraction efficiency is a unit of measure with respect to process and plant.  If one obtains 77% efficiency with Briess 2-row and 80% efficiency with Avangard Pils, it should raise a red flag that the hypothetical grain extract potential values used in the calculation may not be accurate.  For this very reason, I stick with points per pound per gallon (PPG) as my extraction unit of measure.

points_per_pound_per_gallon = gravity_points(measured_gravity) * volume_in_gallons / grist_weight_in_pounds

The beauty PPG is that it is an easily scaled metric.

For example, here’s the grist that I used last weekend

10lbs Avangard Pils
0.5lb Briess CaraPils
0.5lb Torrified Wheat

My O.G. was 1.064, and my total final liquid volume minus the hops and break was 5.5 gallons (it’s important to separate the liquid from the hops and break material after casting out, so that one can measure its volume).  My cast-out volume was 5.25 gallons.  I left 0.25 gallons with the break and hops. What was my PPG extraction rate?

ppg_extraction_rate = gravity_points(1.064) * 5.5 / 11 = 32 points per pound per gallon, mixed grist

I have used Avangard Pils enough to know ahead of time that I was more than likely going to obtain an extraction rate of 32 points per pound because the high percentage of pils malt swamps out the contributions by the carapils and torrified wheat.

Now, with a PPG value for a grist that is roughly 90% +/- 1% Avangard Pils, I can calculate the amount of grist needed for any gravity and any volume.

For example, let’s figure out how many pounds of a 90% +/- 1% Avangard Pils grist is needed to hit 1.053 with a final kettle volume of 11 gallons.

pounds_of_grist = gravity_points(1.053) * 11 / 32 = ~18.22 pounds

pounds_of_avangard_pils = 18.22 * 0.91 = 16.58 pounds
pounds_of_carapils = 18.22 * 0.045 = 0.82 pounds
pounds_of_torrified_wheat = 18.22 * 45 = 0.82 pounds

One way to look at PPG versus extraction efficiency percentage is that working with PPG is like working with a transfer function versus working with its differential equation equivalent.

7

Here is a link to my sheet:

https://drive.google.com/file/d/0B8F3C1boHghNODlPbmRTMFJvajRSYUM5WERFN2hTWTZfMTdJ/view?usp=sharing

I’ll be working on it this week.