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.
* Excel® is a registered trademark of Microsoft Corporation. You can get more information about Excel by visiting the Web site of Microsoft at: http://www.microsoft.com/
The demonstration worksheet is available in file formats for Windows Excel 97 (or newer) and Excel 5 and MacOS Excel 98 (or newer) and Excel 5. If you have a modern web browser you should be able to download a binary Excel file directly by clicking on one of the links below.
For Windows:Note: This worksheet (dated April 16, 2004) 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.
You should be able to open the AwNEqnWB worksheet file in Microsoft Excel by double-clicking on its icon. Alternatively, you can open the worksheet file in Excel by first starting Excel, choosing Open from the File menu and then selecting the Excel file that was decompressed and decoded by StuffIt or Aladdin Expander. If the file does not appear on the list of file names in Excel for Windows, set the 'Files of type:' pop-up menu to 'All Files (*.*)'. When you are shown a dialog box asking about enabling macros, click the 'Enable Macros' button.
The AwNEqnWB Excel file contains two worksheets: Sheet1 and Sheet2. Sheet1 contains instructions, an embedded chart, colored user-input cells and a button labeled 'Find Aw*'. Sheet2 is used only for calculations. Cells in either sheet that have a red triangle marker in their upper right corner have a comment note attached to them. Click here to see the text of all comment notes from both sheets. If a cell contains the name of one or more cells below it, the note explains how those cells are used. In Windows Excel 97, 2000 or 2003 or MacOS Excel 98, 2001 or X the note pops up automatically when you use the mouse to point to a marked cell. To edit or change the size of a note on Sheet1, you must first unprotect that sheet by choosing Protection> Unprotect Sheet... from the Tools menu while Sheet1 is being displayed. Next, click on a marked cell to select it, then choose Edit Comment from the Insert menu. In order to see the note in Excel 5 for Windows or MacOS, you must first unprotect the worksheet as just described, then click on a marked cell to select it and choose Note... from the Insert menu. Sheet2 must be left unprotected in order for the macros to work properly. Making modifications to Sheet2 or to any of the Visual Basic macro code may also cause the macros to stop working properly.
Locate the worksheet cells labeled AwStar (D2, colored light blue) and InitIngrAw (D4, D7, D9 & D12, colored black, blue, green and red). These cells are set to guesses (all 0.5) of the actual answers to be calculated. The four InitIngrAw values are the initial water activity values of each of the four ingredients in the mixture and AwStar is the final (equilibrium) water activity of the whole mixture. Next, take a look at the chart. It shows a vertical dashed light blue line positioned at x-coordinate value 0.5, the guessed value for AwStar. The initial Aw values of each ingredient are marked with solid triangles and the final (equilibrium) Aw values of each ingredient are marked with solid squares, each colored to match its associated ingredient. All the triangles and squares are initially also positioned at the guessed value of 0.5.
To run the model and calculate the actual values for AwStar and the four InitIngrAw values, you need to run a macro named FindAw. Do this by clicking on the 'Find Aw*' button. (The macro can also be run by selecting Macro> Macros... from Excel's Tools menu which brings up the Macro dialog box. Click on FindAw to highlight it in the list of defined macros then just click the Run button to run it.) Notice the changes in the values of the AwStar and InitIngrAw worksheet cells and in the positions of the dashed light blue vertical line and all the square and triangle markers on the chart.
To apply this worksheet to a different dry multicomponent food mixture, any needed deletions or additions to the ingredients in the mixture must be made by making appropriate text entries in some Sheet1 cells with colored backgrounds, then the FindAw macro must be executed again by clicking the 'Find Aw*' button. Details about all Sheet1 cells with colored backgrounds are given on the User Input page. Details about all other non-empty worksheet cells (on Sheet2) are given on the Model Calculations and Charting the Results pages.
![[Return to ]](../../images/gif/redarrow.gif){kind=small}
Mathematica or Excel selection page
Prof. Micha Peleg
Mark D. Normand
UMass Department of Food Science
University of Massachusetts at Amherst