Republished from BrewingTechniques' September/October 1993.
Spreadsheet for Recipe Design Revisited
Karl King's spreadsheet for recipe design is a pleasure to use. BrewingTechniques 1.1 However, somewhere between the text and the formulas an important point got mislaid. Mr. King says, in the text, that the percent alpha acid utilization, which is required to compute IBU, "depends on . . . the gravity of the wort." This is quite true, but it did not make it to the spreadsheet.
The percent utilization is calculated in cell B37. Nowhere does the formula shown in Table I use the adjusted specific gravity of the boil, which was developed in cell B36. In Figure 1, the spreadsheet itself, the percent utilization is calculated with this formula. The resulting value of 35.78% is much higher than the 20% shown in Papazian under the same conditions. This in turn leads to an overestimate of the bitterness.
The higher the gravity of the boil, the lower the utilization of alpha acids. The formula lacks a specific gravity term with a negative coefficient.
I'm pleased to hear from readers that the spreadsheet has made recipe design easier and more enjoyable.
The correction for adjusted gravity appears in cell B38. The equation there references cell B36, the adjusted gravity. The IBU value is decreased with increasing gravity because B36 is in the denominator.
That means that cell B37 is the uncorrected percent utilization. If you want to see the corrected percent utilization, remove B36 from the formula in B38 and put it into B37 as a divisor for each of the linear expressions containing B33. I put it into B38 because it made the formula simpler. The final IBU value will be the same regardless.
If you divide the 35.78 "%utilization" figure in B37 by the 1.51 "specific gravity adjusted" from B36, the value will be 23.7%. This is higher than the 20% given by Papazian. In researching utilization data, I found considerable variation among sources -- not surprising considering the fact that utilization can vary with wort pH, activity of the boil, and the form of hops used. So, the spreadsheet will not agree precisely with everybody's data.
When I did the curve fit for the "%utilization," I followed Table 2 given by Rager (reference 4 in my article). There the percentage is listed as a single figure over a range of minutes. I took the midrange value of the minutes for the curve fit. If I had taken the highest value of the minute range, then my curve fit would agree very closely with the values given by Papazian.
As it stands, my curve fit gives a slightly higher percent value. That suits my brewing style because I use pelletized hops and boil vigorously. Those who use whole hops may wish to take the higher percent value into account when using the spreadsheet.
--Karl King Brown Deer, Wisconsin
[BrewingTechniques Library] [Contact