﻿ Untitled Document

Formula References & Explanations

Yeast:

Yeast Starter Size Table based off of Jamil's table in Brewing Classic Styles, p. 290. (Actually no longer used in calculations.)

Cell count of starter preparation are based upon the data presented by MB Raines here: http://www.maltosefalcons.com/tech/MB_Raines_Guide_to_Yeast_Culturing.php

Yeast starter size is the final starter size that is made, of course you can decant and just pitch the slurry on stirplate starters. However you need to step up to the final volume, so be it. If you just pitch packages, the starter size is the # of packages

Desired cell count is calculated based on .75 million cells per mL per ºP for ales, and 1.5 million cells per mL per ºP for lagers.

Malt:

Morey's Formula is used to calculate SRM: http://www.brewingtechniques.com/brewingtechniques/beerslaw/morey.html
SRM = 1.4922 [(MCU) ^ 0.6859] where MCU is calculated by MCU = WeightofGrain * LovibondofGrain / VolumePostBoil
the MCU values are hidden next to the Lovibond displayed in the worksheet, final value is shown at the bottom.

Current EBC to SRM, SRM to Current EBC - Designing Great Beers, Daniels, p. 44
EBC = SRM * 1.97 , SRM = EBC / 1.97

Old EBC to SRM, IOB to SRM - New Brewing Lager Beer, Noonan, p. 11
ASBC = (EBC+1.2)/2.65 , ASBC = ((IOB/0.8)+1.2)/2.65

Calculating Potential from Dry Yield:
Potential = ((46.31 * Dry Yield)+1000)/1000

Converting from points/kg/L to Dry Yeild:
Dry Yield = Points/kg/L / 386.5

References for Dry Yield formulas:
http://www.brewingtechniques.com/bmg/noonan.html
http://brewingtechniques.com/library/backissues/issue1.3/manning.html

If you cannot find the properties of your grains from the malster, defaults can be used from this page:
http://www.carolinabrewmasters.com/06Aprgrains.html

Malt information for different maltsters can be found here: http://www.brewingtechniques.com/bmg/adm.html

Briess, Castle, Crisp, Durst, Gambrinus, Gilbertson & Page (OIO), Global, Rahr, Simpsons & Weyermann data:

Cargill, Dingemans, Gambrinus, Gilbertson & Page (OIO), Meussdoerffer, Pauls, Warminster data:
http://www.specialtymalts.com/tech_center/

Hops:

IBU formulae for Rager, Garetz and Tinseth can be found at: http://www.realbeer.com/hops/FAQ.html#units

Tinseth Hops Formulas:

IBUs = decimal alpha acid utilization * mg/l of added alpha acids

Metric Units
mg/l of added alpha acids = (decimal AA rating * grams hops * 1000) / liters of wort

Non-Metric Units
mg/l of added alpha acids = (decimal AA rating * ozs hops * 7490) / gallons of wort

The decimal alpha acid utilization is calculated using Tinseth's two empirical factors: the Bigness factor and the Boil Time factor.

Decimal Alpha Acid Utilization = Bigness Factor * Boil Time Factor

The Bigness Factor accounts for reduced utilization due to higher wort gravities.
Bigness factor = 1.65 * 0.000125^(wort gravity - 1)

The Boil Time Factor gives the varying utilization based on boil time:

Boil Time factor = (1 - e^(-0.04 * time in mins)) / 4.15

Rager Hops Formulas:

%UTILIZATION = 18.11 + 13.86 * hyptan[(MINUTES - 31.32) / 18.27]

According to Rager, if the gravity of the boil exceeds 1.050, there is a gravity adjustment (GA) to factor in:

GA = ((BOIL_GRAVITY - 1.050)) / 0.2

otherwise,
GA = 0

Metric Units
IBU = ((GRAMS OF HOPS) * %UTILIZATION * %ALPHA * 1000) / (VOLUME(litres) * (1 + GA))

Non-metric Units
IBU = ((OUNCES OF HOPS) * %UTILIZATION * %ALPHA * 7462) / (VOLUME(gallons) * (1 + GA))

Hops AA% Deterioration during storage formulas and tables: http://www.brewingtechniques.com/library/backissues/issue2.1/garetz.html

Future Alpha Acid Percentage = A*1/e(k*TF*SF*Days)

Where A is the AA% rating when you bought the hops, k is the constant in that corresponds to the hops %Lost, TF is the temperature factor, SF is the storage factor and Days is the numbers of days since purchase.

Mashing:

Infusion equations can be found in John Palmers' How to Brew p.170

Initial Infusion Equation
Strike Water Temperature TW = (0.2/R)(TS-TG)+TS
where TW = temp of strike water, R = mash thickness in quarts per pound or liters per kg, TS = desired mash step temp, TG = temperature of the grains, temps can be C or F but use 0.41 for C
this does not account for heat lost to tun, increase the .2 if necessary for heat loss to the tun

Mash Infusion Equation
Wa = (T2-T1)(0.2G + Wm) / (Tw-T2)
where Wa = amount of water to add in quarts or liters, Wm = amount of water in mash, T1 = initial mash temp, T2 = target mash temp, Tw = temp of infusion water, G = amount of grains in pounds or kg, temps can be C or F

Predicted Mash Efficiency is used to adjust the gravity units from the grains for predicting boil gravity, extracts are not affected by mash efficiencies and they are tagged in the grains tab as sugars in the notes column. Extracts added late will be multiplied by a factor of .2 creating a weighted gravity unit value for predicting pre-boil gravity, not sure where I first picked up this factor. Pre-boil gravity of course affecting other calculations such as hops utilization and thus IBU.

The purpose of the number below the late addition dropdowns is a weighted adjusted gravity unit value for calculating pre-boil gravity and thus hops utilization values. Really just for approximating the better utilization that would come from adding extract late when doing an extract brew. Also, if you are going to use fermentables that won't be included in the boil, you can include them in recipe for predicting OG, but since they aren't in the boil (like cider) the boil gravity is more accurate. The skip is also used to remove "pounds" value for when your entering in non-fermentable ingredients like whirfloc, spices, etc. The "pounds" value there is used for calculating the prices, just use consistent units in the grains tab for that ingredient to accurately calculate the costs.

For the strike water temperature predictions adjust the Infusion Temp Equation Factor up or down in the same direction, meaning if you're undershooting your temps then increase the factor and if you're overshooting then decrease it.

For the boil off factor, run a water test in your system and use the boil off rate calculator to determine the rate.

Decoction Mashing info and formulas: http://braukaiser.com/wiki/index.php?title=Decoction_Mashing
decoction volume = total mash volume * (target temp - start temp) / (boil temp - start temp)
then add 15-20% depending upon the system (this is the decoction factor)

Alternate method for predicting Infusion & Decoction Volumes: http://brewingtechniques.com/library/backissues/issue4.5/miller.html

Understanding and Calculating Efficiencies: http://hbd.org/uchima/tech/efficiency.html

Beer Properties:

ABW - Principles of Brewing Science by Fix, p. 93
ABW = (OE-RE)/(2.0665-0.010665*OE) OE & RE in ºP

Attenuation, Gravity Conversions, Calories: http://www.realbeer.com/spencer/attenuation.html
http://hbd.org/ensmingr/

Sg -> Plato coversion based on DeClerk's work from A Textbook of Brewing first published in 1957.
°P = (-463.37) + (668.72 * SG) - (205.35 * SG^2)

Real Extract calculation based on Karl Balling's empirically derived formula provided by George Fix in HBD: http://hbd.org/hbd/archive/880.html#880-9
RE = (0.1808 × °Pi) + (0.8192 × °Pf)

From ASBC's 1992 Methods of Analysis:
cal per 12 oz beer = [(6.9 × ABW) + 4.0 × (RE - 0.1)] × FG × 3.55

BU/GU is calculated as descirbed by Ray Daniels in Designing Great Beers p. 126
BU:GU = IBU/(OG-1)*1000

Balance Value (BV) is calculated based on the formulas given here: http://beercolor.netfirms.com/balance.html
BV = 0.8 x BU / RTE
RTE = 0.82 x FG + 0.18 x OG
BV = 0.8 x BU / ((0.82*FG)+(0.18*OG))

Hydrometers & Refractometers:

This appears to be the popular formula for correcting hydrometer samples: http://hbd.org/brewery/library/HydromCorr0992.html
Formulas are also explained in this BYO article: http://www.byo.com/component/resource/article/1343-refractometers

Refractometer Formulas can be found on the bottom of this page: http://www.primetab.com/formulas
Formulas are also explained in this BYO article: http://www.byo.com/component/resource/article/1343-refractometers

Final Specific Gravity from original and final Brix
SG = 1.001843 - 0.002318474*OB - 0.000007775*OB*OB - 0.000000034*OB*OB*OB + 0.00574*FB + 0.00003344*FB*FB + 0.000000086*FB*FB*FB
where: SG = estimated specific gravity of the sample, OB = Original Brix, FB = Final Brix

Brix to Index of Refraction
RI = 1.33302 + 0.001427193*B + 0.000005791157*B*B
where: B = measured refractivity in Brix, RI = calculated Refractive Index

Alcohol by weight content from Final SG and Final Brix
RI = 1.33302 + 0.001427193*FB + 0.000005791157*FB*FB
ABW = 1017.5596 - (277.4*SG) + RI*((937.8135*RI) - 1805.1228)
where: FB = Final Brix, RI = calculated Refractive Index, SG = Final SG

ABV from Final SG & Final Brix (BYO)
ABV = [277.8851 - 277.4(SG) + 0.9956(Brix) + 0.00523(Brix2) + 0.000013(Brix3)] x (SG/0.79)

Real Extract from Final Brix (BYO)
RI = 1.33302 + 0.1427193(Brix) + 0.000005791157(Brix2)
RE = 194.5935 + 129.8(SG) + RI[410.8815(RI) - 790.8732]

Refractometer calibration is done by measuring the same sample with a calibrated hydrometer & your refractometer. The refractometer's reading is divided by the hydrometer's reading (in Plato) for the correction factor. If you have a high end refractometer, don't bother with using this functionality.

Miscellaneous:

Pressure formula is below the chart, I pulled it off of some other chart I had printed out long ago that wasn't wide enough or incremental enough for me. Scroll left or right, range is 0.5 to 5 CO2 volumes.
Pressure conversion factors: http://wiki.xtronics.com/index.php/Pressure_Conversion_Table

The area and volume calculators are done dimensionless, so be sure you're consistent and use the same units in all fields. Areas will of course be the unit squared and volumes the unit cubed.

Water profiles collected from: http://www.beersmith.com/Water/Waters.htm
All the water chemistry calculations referenced from John Palmer's spreadsheet available at: http://www.howtobrew.com/section3/chapter15-3.html (last update used was Oct 2008 v2.4)

Electric Heat calculations are from the spreadsheet found here: http://suburb.semo.net/jet1024/Electric%20Heat.xls
Time= ((Gallons*8.33*453.59237)*(((5/9)*(TargetTemp-32))-((5/9)*(StartingTemp-32)))/(Watts*0.238845896628*Efficiency))/60
ActualWattage= (ActualVoltage^2)/((ElementRatedVoltage^2)/ElementRatedWatts)
Minimum Breaker = ROUNDUP(((Watts/Volts)*1.2)/5,0)*5

Wort Contraction & Grain Absorption Factors are derived from Designing Great Beers by Ray Daniels, pp. 64-65
4% contraction from boiling to 68ºF (20ºC), adjust this to be in line with your system
0.2 factor for grains absorbing water (lbs * factor = gallons), again adapt this to your system

Priming Formulas are explained here: http://hbd.org/ddraper/priming.html

Priming Rate for Glucose/Dextrose:
Rate in g/L = (v - v0) / 0.27027

Priming Rate for Sucrose:
Rate in g/L = (v - v0) / 0.286

Parti-gyle brewing adjustments are described by Randy Mosher here: http://brewingtechniques.com/library/backissues/issue2.2/mosher.html
http://www.strangebrew.ca/swig/
http://www.hbd.org/carboy/parti.htm

Calendar Formulas:

DATEVALUE of EOM = (EDATE(DATEVALUE(CONCATENATE(\$D\$1," 1, ",\$F\$1)),1)-1)
DATEVALUE of FOM = (DATEVALUE(CONCATENATE(\$D\$1, " 1, ",\$F\$1)))
DAYS in MONTH = ((EDATE(DATEVALUE(CONCATENATE(\$D\$1," 1, ",\$F\$1)),1))-(DATEVALUE(CONCATENATE(\$D\$1, " 1, ",\$F\$1))))

First row is set by the weekday value of the FOM, subsequent rows are set by subtracting the weekday value of the FOM from an index value. Later cells are data validated by checking the index against the days in the month first.

Cell Value =IF(B3="","",IF(B3<\$E\$47,"",IF(B3=\$E\$47,"Brew Day",IF(B3<=\$E\$47+\$E\$52,"Primary Fermentation",IF(B3<=\$E\$47+\$E\$52+\$E\$53,"Diacetyl Rest",IF(B3<=\$E\$47+\$E\$52+\$E\$53+\$E\$54,"Cold Crash",IF(B3<=\$E\$47+\$E\$52+\$E\$53+\$E\$54+\$E\$55,"Secondary Conditioning",IF(B3<=\$E\$47+\$E\$52+\$E\$53+\$E\$54+\$E\$55+\$E\$56,"Aging (including carbing)",IF(B3=\$E\$49,"Tap Date","")))))))))

Water Chemistry Formulas (mostly based on Palmer's Spreadsheet, need to update formulas here to reflect NaCl):

Palmer's How To Brew section on reading a water report: http://www.howtobrew.com/section3/chapter15-1.html
Palmer's How To Brew section on residual alkalinity & mash pH: http://www.howtobrew.com/section3/chapter15-3.html

Estimated Low RA = 12.2*SRM-122.4
Estimated High RA = (SRM-5.2)*12.2
Alkalinity = (50*Bicarbonateppm/61)
Bicarbonateppm = 61*Alkalinity/50

Effective Hardness = (Calciumppm/1.4)+(Magnesiumppm/1.7)

Residual Alkalinity as CaCO3 =
IF(Bicarbonate (ppm),
(50*E11/61)-K11,
E11-K11)
Else is Alkalinity as CaCO3

Estimated Low SRM =
IF(Residual Alkalinity<-69,
0,
Residual Alkalinity*0.082+5.2)

Estimated High SRM =
IF(Residual Alkalinity<-128,
0,
(Residual Alkalinity+122.4)/12.2)

Chloride to Sulfate Balance =
=VLOOKUP(Chlorideppm/Sulfateppm,BalanceTable,2,TRUE)

Dilution Results = (1-Dilution%)*Originalppm/Alkalinity

Volume Source Water =(1-Dilution%)*Mash Water Volume

Volume Distilled Water =Dilution%*Mash Water Volume

IF(ResidualAlkalinityPostDilution>ResidualAlkalinityTarget,
IF("Bicarbonate (ppm)",(50*Bicarbonateppm/61),AlkalinityPostDilution)-ResidualAlkalinityTarget-EffectiveHardness,
0)

IF(ResidualAlkalinityPostDilution<ResidualAlkalinityTarget,ResidualAlkalinityTarget-ResidualAlkalinityPostDilution,0)

(Chalk*105.89+Gypsum*60+CalciumChloride*72)/Mash Water Volume

Epsom Salt*24.6/Mash Water Volume

(Chalk*158+BakingSoda*191.88)/Mash Water Volume

BakingSOda*72.3/Mash Volume Water

CalciumChloride*127.47/Mash Water Volume

(Gypsum*147.4+EpsomSalt*103)/Mash Water Volume

Contributed Hardness =

Contributed Alkalinity =

IF(ResidualAlkalinityPostDilution>ResidualAlkalinityTarget,
,0)

IF(ResidualAlkalinitySourceWater>ResidualAlkalinityTarget,0.88/Bottle%*3.785*Bottle%*MashWaterVolume/50/11.8,0)

FinalAlkalinity as CaCO3 = IF("Bicarbonate (ppm)", 50/61*AlkalinityPostDilution+AlkalinityContributed,AlkalinityPostDilution+AlkalinityContributed)

FinalChloride =