![[Return to ]](../../images/gif/redarrow.gif){kind=small}
Excel water activity worksheet download pageModel Assumptions: This Microsoft Excel demonstration worksheet file estimates the equilibrium water activity, AwStar, of a dry multicomponent food mixture stored in a hermetically sealed container. It is assumed that the ingredients do not interact chemically, that the temperature is constant and that the amount of moisture absorbed by or released to the atmosphere trapped in the container is negligible. The n ingredients' moisture sorption isotherms are described by n equations, each one of which may or may not be the same as the equation of another ingredient. Input moisture values should be entered on a wet basis and will be converted to a dry basis for calculation and plotting.
What to change: This worksheet has formulas in place to calculate mixtures that contain up to 15 ingredients. The user must first be sure that ALL UTILIZED CELLS WITH COLORED BACKGROUNDS contain the correct numeric values or equations. To remove an existing ingredient: enter a guess <0 (e.g., -1) in place of a positive value of an ingredient's initial Aw in column D. Its entered InitMoistWetB and InitWetWt values in columns F and G and all uses of its equation will be cleared, the ingredient name will be replaced by the cell's color name and its curve, point markers and legend entry will be removed from the chart when the FindAw macro is run. To add a new ingredient: enter a guess >=0 and <1 (e.g., 0.5) into an unused colored cell in column D. Replace the color name with the ingredient's name in the same row of column E. Enter the ingredient's initial wet-basis moisture in column F and its wet weight in column G. Enter the ingredient's moisture sorption isotherm equation into the same row of column H as described in the next note.
Entering equations: For the model to calculate ingredients correctly, from 1 to 15 moisture sorption isotherm equations must be entered in the 15 cells in rows 4 through 18 of column H. Use AwStar or $D$2 in place of Aw in the equation (a $ precedes an absolute row or column reference, which does not change when copied and pasted). The equations will be automatically copied into the corresponding cells in column A on Sheet2. In order for an isotherm curve to appear on the graph, the equation of each ingredient also will be copied into rows 4 through 103 starting with column O on Sheet2 and using one ingredient's equation per column. If a published moisture sorption isotherm equation for a particular ingredient is unavailable in the literature, you can always fit your own experimental sorption data with one of the commonly used equations (see References 3 & 4) or (much simpler) with a fourth-degree polynomial: Moisture = c1*Aw + c2*Aw^2 + c3*Aw^3 + c4*Aw^4 (where c1, c2, c3 & c4 are the 4 fitted coefficient values).
Finding & plotting Aw*: The graph uses Excel's XY (Scatter) chart type. Columns O, R, T and W on Sheet2 calculate the y-coordinates of the 100 points used to draw the 4 demo. isotherm curves on the graph. Additional Sheet2 worksheet columns are automatically filled with each ingredient's equation that references the corresponding Aw value in column N as its independent (x) variable. Other Sheet2 columns used by the graph are: H & I (triangles' x,y), J & K (squares' x,y) and L & M (Aw* line's x,y). Clicking the 'Find Aw*' button runs the FindAw macro which affects the chart as follows: 1) erases curves, markers and legend entries of removed ingredients, 2) adds them for any added ingredients and 3) finds and marks Aw* and the initial and final Aw values of all ingredients in the mixture.
The 15 ingredient limit: 15 of the 40 user-selectable cell fill colors available in Excel are used to associate each ingredient with a color that is clearly visible against the white background of the chart and that Excel does not automatically change when the order of ingredients changes. Gaps are allowed in the list of ingredients. In other words, the ingredients do not have to all be listed in a contiguous block. An ingredient is included in the mixture only if its guessed InitIngrAw value in column F is between 0 and 1 (e.g., 0.5) and is removed if it's guessed value is <0 (e.g., -1). The default chart height is set to allow the display of a legend containing all 15 ingredients.
Protected cells: Worksheet protection on Sheet1 is no longer used in order to allow refreshing of the background and font colors of the 5 colored user-input cells every time an ingredient is added to or removed from the mixture. Sheet2 is still unprotected also.
Model by: Micha Peleg
Professor
Tel: 413-545-5852
Fax: 413-545-1262
E-mail: see http://www-unix.oit.umass.edu/~aew2000/
Worksheet by: Mark D. Normand
EDP Programmer II
Tel: 413-545-2365
Fax: 413-545-1262
E-mail: see http://www-unix.oit.umass.edu/~adva000/
Postal address: Department of Food Science
Chenoweth Lab.
University of Massachusetts
Amherst, MA 01003-1410
Web site URL: http://www.umass.edu/foodsci/
1) Peleg, M. and Normand, M. D. l992. Estimation of the water activity of multicomponent dry mixtures. Trends Food Sci. & Technol., 3:l57-l60.
2) Peleg, M. l993. Assessment of a semi-empirical four parameter general model for sigmoid moisture sorption isotherms. J. Food Proc. Engng., l6:2l-37.
3) Wolf, W., Spiess, W.E.L. and Jung, G. 1985. Sorption isotherms and water activity of food materials. Elsevier, New York.
4) Iglesias, H.A. and Chirife, J. 1982. Handbook of food isotherms: water sorption parameters for food and food components. Academic Press, New York.
Last modified: This version replaces earlier versions that did not automatically add and delete curves, markers and legend entries from the graph when the number of ingredients changed or did not spread the calculations over 2 sheets. This version works only for mixtures containing up to 15 ingredients. Worksheet protection is no longer used on Sheet1 so that each time an ingredient is added or deleted, the background and font colors of its 5 user-input cells can also be refreshed. Sheet2 is still unprotected also. A bug in the 11/29/01 version that caused a runtime error when a new ingredient was added has been fixed. 3/22/04: Option Explicit added to require declaration of all variables & cell Sheet1!$D$2 may be referred to as AwStar. 4/16/04: A bug in the 3/22/04 version that did not correctly copy new equations referring to AwStar has been fixed. 4/23/04: Text of instructions in column C was modified.
Enter AwStar and 1-15 guesses of initial Aw values used to add (greater than or equal 0 and less than 1) or remove (less than 0) an ingredient from the mixture. Fill in the other four colored cells for each added ingredient then click 'Find Aw*' to compute AwStar and all initial Aw values and update the chart.
[The remaining comment notes are from title cells that are located in the cell just above worksheet cell(s) used in calculating the model.]
AwStar: The cell below initially contains a guess (0.5) of the final (equilibrium) water activity (AwStar) of the dry food mixture. It is changed to the calculated AwStar mixture equilibrium value by clicking the 'Find Aw*' button which runs the FindAw macro, which beeps twice when it is finished.
InitIngrAw: Each of the cells below initially contains a guess of one ingredient's initial water activity (Aw) value. Its value flags that ingredient as either: removed from the mixture (<0), unused (empty) or in the mixture (>=0 and <1). For an ingredient to be included in the mixture it's InitIngrAw value must be >=0 and <1 when the 'Find Aw*' button is clicked. When the macro runs it is assumed that any ingredient whose InitIngrAw value is less than 0 is being removed and so all its entered values and equations are deleted from the worksheet and its curve, markers and legend entry are removed from the chart. After the FindAw macro has run, each guess >0 has been replaced by its actual initial Aw value.
IngredientName: In the cells below the user should replace any n color names (where 1 <= n <= 15) with the names of the n ingredients of the dry food mixture to be calculated. The Fill Color button on the tool bar was used to match the background color of each user-modifiable cell referring to one ingredient to its isotherm curve and point marker's color on the chart.
InitMoistWetB: Each of the cells below contains the user-entered initial moisture (in percent on a wet basis, expressed as grams of water per 100 grams of total weight) of that ingredient of the dry food mixture.
InitWetWt: Each of the cells below contains the user-entered initial wet weight (in grams, including the water) of that ingredient of the dry food mixture.
IngrEqMoist: Each of the cells below contains one user-entered moisture sorption isotherm equation that calculates the final (equilibrium) moisture (in percent on a dry basis, expressed as grams of water per 100 grams of dry matter) of that ingredient. The result depends on the current value of AwStar. In the entered expression use $D$2 in place of the equation's independent (Aw) variable.
IngrInitMoist: Each of the cells below calculates the initial moisture (in percent on a dry basis, expressed as grams of water per 100 grams of dry matter) of one ingredient using its moisture sorption isotherm equation. The result depends on the current value of the corresponding InitIngrAw cell.
AwStarGoal: The cell below is a target of the FindAw macro's Goal Seek process. It attempts to make the difference between the equation-calculated and the measured total mixture moisture equal to zero. It does this by adjusting the value in the cell containing the guess for AwStar.
InitIngrAwGoal: Each of the cells below is a target of the FindAw macro's Goal Seek process. It attempts to make the difference between the equation-calculated and the measured initial ingredient moisture equal to zero. It does this by adjusting the value in the cell containing the guess for that ingredient's initial water activity (InitIngrAw).
MixInitMoistDryB: The cell below contains the total initial moisture (in percent on a dry basis, expressed as grams of water per 100 grams of dry matter) of the dry food mixture, which equals the total measured initial water divided by the total initial dry weight.
InitMoistDryB: Each of the cells below calculates the initial moisture (in percent on a dry basis, expressed as grams of water per 100 grams of dry matter) in one ingredient of the dry food mixture from the user-entered measurement.
SumInitDryWt: The cell below contains the total initial dry weight (in grams, dry matter only) of the dry food mixture, which equals the sum of all the following InitDryWt values.
InitDryWt: Each of the cells below calculates the initial dry weight (in grams, dry matter only) of one ingredient of the dry food mixture.
SumMeasIngrMoist: The cell below contains the total measured initial moisture (in grams) in the entire dry food mixture, which is the sum of all the following MeasIngrMoist values.
MeasIngrMoist: Each of the cells below calculates the measured initial moisture (in grams) in one ingredient of the dry food mixture.
SumIsoIngrMoist: The cell below contains the total equation-calculated moisture (in grams) in the entire dry food mixture, which equals the sum of all the following IsoIngrMoist values.
IsoIngrMoist: Each of the cells below calculates the amount of moisture (in grams) in one ingredient using its moisture sorption isotherm equation. The result depends on the current value of AwStar.
IsoMixMoisture: The cell below calculates the total moisture of the dry food mixture (in percent on a dry basis, expressed as grams of water per 100 grams of dry matter), which equals 100 times the total equation-calculated moisture divided by the measured total initial dry weight.
IngrChangeMoist: Each of the cells below calculates the change in its ingredient's dry basis moisture value (in percent on a dry basis, expressed as grams of water per 100 grams of dry matter), which is the difference between its final (equilibrium) and its initial moisture values.
InitAw: Each of the cells below is a copy of its ingredient's corresponding InitIngrAw cell. It is used as the x-coordinate of that ingredient's triangle marker on the graph.
InitMoist: Each of the cells below is a copy of its ingredient's corresponding InitMoistDryB cell. It is used as the y-coordinate of that ingredient's triangle marker on the graph.
EqAw: Each of the cells below is a copy of the AwStar cell. It is used as the x-coordinate of that ingredient's square marker on the graph.
EqMoist: Each of the cells below is a copy of its ingredient's corresponding IngrEqMoist cell. It is used as the y-coordinate of that ingredient's square marker on the graph.
AwStarX: Each of the two cells below is a copy of the AwStar cell. They are used as the x-coordinates of the vertical Aw* line on the graph.
Aw*: The two cells below contain the graph's minimum and maximum y-axis values. They are used to draw the vertical Aw* line on the graph.
Aw: The 100 cells below contain the Aw values that are the x-coordinates used to draw each ingredient's moisture sorption isotherm curve on the graph.
Black: This column name is a copy of the first possible ingredient's name and is used on the graph's legend. The 100 cells below contain the moisture values that are the y-coordinates used to draw on the chart the moisture sorption isotherm curve of the ingredient (if any) entered in worksheet row 4 and associated with the color black. They are calculated using the Aw values in column N in the equation for this ingredient. The same applies to the columns for the other 14 possible ingredients.
Excel water activity worksheet download page