Notes on User Input Cells of Excel File AwNEqnWB

User Input

The demonstration worksheet models a dry food mixture containing four individual food ingredients. They are named by the background fill color of their cells: Black, Blue, Green and Red. The worksheet has formulas in place to calculate mixtures that contain up to 15 ingredients. To facilitate identifying worksheet cells with their associated lines and markers on the graph, all 15 ingredients are color coded. To model mixtures containing up to 15 ingredients, 5 pieces of information need to be entered in the cells with colored backgrounds on Sheet1 for each ingredient contained in the mixture:
1) InitIngrAw (column D): enter a guess of the ingredient's initial water activity value.
2) IngredientName (column E): replace the cell fill color name with the actual name of the ingredient.
3) InitMoistWetB (column F): enter the initial wet-basis moisture of the ingredient.
4) InitWetWt (column G): enter the initial wet weight of the ingredient.
5) IngrEqMoist (column H): enter the moisture sorption isotherm equation of the ingredient as an Excel formula.

To calulate the new mixture run the FindAw macro by clicking the 'Find Aw*' button. If you wish to keep the results remember to save the modified worksheet file under a new name before you quit Excel.

AwStar (cell D2, colored light blue) initially contains a guess for AwStar, the equilibrium water activity value for the dry multicomponent food mixture. Before the FindAw macro is executed, D2 contains a user-entered guess of the final AwStar value. The guess is updated by the macro calculations to the correct final AwStar value. This is the main "answer" that the worksheet model is seeking. Its value is shown on the graph as a dashed light blue vertical line. The final (equilibrium) Aw value of each ingredient is marked on the graph by a solid square in that ingredient's color.

InitIngrAw (cells D4:D18, a different color for each ingredient) initially contain guesses of each ingredient's initial water activity value, Aw. Before the FindAw macro is executed each cell contains a user-entered guess of the initial Aw value for that ingredient. Each guess is updated by the macro calculations to the correct initial Aw value. The initial Aw value of each ingredient is marked on the graph by a solid triangle in that ingredient's color. Enter a value between 0 and 1 (0.5 is fine) as the initial Aw value of the ingredient to add that ingredient to the mixture. Enter a value less than zero (e.g., -1) to remove that ingredient from the mixture before the rest of the model is calculated.

IngredientName (cells E4:E18, a different color for each ingredient) contain the names of each ingredient in the dry food mixture. One ingredient is named per row. This name will appear on the chart legend and above the 100 worksheet cells used to plot the moisture sorption isotherm curve starting at worksheet column N on Sheet2.

InitMoistWetB (cells F4:F18, a different color for each ingredient) contain the initial moisture content on a wet basis (expressed as grams of water per 100 grams of total weight) of each ingredient in the mixture.

InitWetWt (cells G4:G18, a different color for each ingredient) contain the initial wet weight in grams (including the water) of each ingredient in the mixture.

IngrEqMoist (cells H4:H18, a different color for each ingredient) contain the final (equilibrium) moisture values for each ingredient in the mixture calculated from its moisture sorption isotherm equation. One equation is entered per column H cell as an Excel formula. The value of the independent variable for each equation is taken from the (guessed or calculated) AwStar value in cell $D$2. Use AwStar or $D$2 in the entered formula in place of the independent variable (Aw) in the equation. A $ precedes an absolute row or column reference (which does not change when copied and pasted).

Entering the Equations

The 4 moisture sorption isotherm equations used in the four-ingredient demonstration mixture are given below. The Excel formula representing each equation was entered into the cell of column H associated with that ingredient. 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).

Ingredient Black uses the BET model: Moisture=(8.1*30.2*Aw)/((1-Aw)*(1-Aw+30.2*Aw))
The BET equation is entered into cell H4 as the following Excel formula: =(8.1*30.2*AwStar)/((1-AwStar)*(1-AwStar+30.2*AwStar))

Ingredient Blue uses Henderson's model: Moisture=20(*ln(1/(1-Aw)))^0.5
The Henderson equation is entered into cell H7 as the following Excel formula: =20(*LN(1/(1-AwStar)))^0.5

Ingredient Green uses the GAB model: Moisture=(10.12*17.6*Aw)/((1-0.95*Aw)*(1-Aw+17.6*Aw))
The GAB equation is entered into cell H9 as the following Excel formula: =(10.12*17.6*AwStar)/((1-0.95*AwStar)*(1-AwStar+17.6*AwStar))

Ingredient Red uses Oswin's model: Moisture=10*(Aw/(1-Aw))^0.6
The Oswin equation is entered into cell H12 as the following Excel formula: =10*(AwStar/(1-AwStar))^0.6

Excel water activity worksheet download page