Water Resources Applications Software
DESCRIPTION OF GENERAL FUNCTIONALITY OF SPREADSHEET
OVERALL STRUCTURE
DESCRIPTION OF SHEETS
INPUTS
CALCULATIONS
RESULTS AND INTERPRETATIONS
MODIFICATIONS BY USERS
EXAMPLE CASES (DRAWN FROM THE AJS ARTICLE)
TERMS AND DEFINITIONS USED IN THE MODEL CALCULATIONS
COMMUNICATION WITH AUTHORS
DOWNLOAD SPREADBAL-2002 Microsoft Excel file with Macros
This document accompanies a downloadable spreadsheet for mineral mass balance analysis. The documentation is being refined and elaborated for future update. Documentation included here is somewhat brief, and is focused on getting one started with the spreadsheet use. Reference to the original manuscript (Bowser, C.J. and Jones, B.F., 2002, MINERALOGIC CONTROLS ON THE COMPOSITION OF NATURAL WATERS DOMINATED BY SILICATE HYDROLYSIS, American Journal of Science, 302: 582-662.) provides examples.
To get started using the program refer to the INPUTS section below.
As included here the spreadsheet contains analytical data for Sierran spring waters (Garrels and Mackenzie, 1967) and the Loch Vale, CO watershed (Mast, et. al., 1990). It is suggested that you experiment with these sample waters to familiarize yourself with the spreadsheet operation.
Examine the input and output sheets before entering variables just to familiarize yourself with the look and feel of the program. Liberal use of the useful Excel TRACE DEPENDENTS and TRACE PRECEDENTS functions will allow the interested user to follow the calculations.
Features of spreadsheet
Mass balance solutions require the solution of a set of simultaneous equations. Microsoft Excel has the capability for the solution of simultaneous equations using matrix techniques. We have adapted an Excel spreadsheet for the mass balance work that features:
Potential for custom user modification include:
a- Variable size mineral/water matrix
b- Inclusion of isotopic constraints
c- Mixing of two waters, each of which has independent mineralogic control on
solute composition.
Limitations of spreadsheet
The spreadsheet model has evolved over a 12 year period, and the form presented herein is more a practical approach rather than a highly organized and systematically formatted sheet. More attention is paid to a working model from which analytical solutions can be derived and less to aesthetics of design. Colored input blocks are used to highlight data sources, data inputs, and special calculation engines. Cross checks are used to correct phase spelling, mineral ratios, and analytical charge balance to help minimize errors in input, and to prevent set calculation areas from being overwritten by user input.
The discussion that follows refers to a spreadsheet designed for Microsoft Excel ™and many of the terms and descriptions are necessarily drawn from the language that Microsoft has chosen for the spreadsheet and all its functions. The current version has been modified and tested on the latest version of Excel (Excel X for Macintosh and Office 2000 for PC). Where possible, Microsoft terms are highlighted in italic capitalized type.
The program consists of six basic sheets, plus a scratch sheet that serves to keep notes and comments. The sheet tab labels are as follows:
Color Coded areas of spreadsheet
Parts of each spreadsheet are colored as reminders of where data should be entered and areas which should not be altered. Yellow areas designate where the user may input or change data. These include modification of the mineral database, the water analyses, or the composition of minerals such as plagioclase and smectite. Blue areas are all calculated from inputs and should not be modified. Pink areas designate cross-checks on data input and are used to monitor satisfactory mass balance solutions. It is recommend that you make a copy of the input sheet and work with the copy, in case you inadvertently change a cell or group of cells that contain references to other cells or calculation formulae.
Input-Output Sheet
Mineral data is entered into this sheet as well as compositional data for plagioclase and dioctahedral smectite. Data are read from this sheet into the Calc. Sheet. Results are output on this sheet in tabular form. Results are shown with reactant minerals in one column (and color) and product minerals in an adjacent column (and color).
Water Data Sheet
Water compositions are entered on this sheet and read into the Calc.Sheet for calculation. The sheet is set up for up to ten input waters, but could be expanded with minor modifications of the definition of the water.database. By entering a simple water code into the input block the waters are automatically chosen from the database and incorporated into the mass balance calculations.
Calc Sheet
All data is input from the INPUT-OUTPUT sheet and the WATER DATA sheets into
cells on this sheet. Most of the calculations for mineral composition, plots,
and calculation results are contained on this sheet. Internal checks are included
to ensure that mineral names are spelled correctly for proper VLOOKUP functions
to work, and to allow evaluation of water ionic balances.
Database Sheet
This sheet includes a mineral database consisting of 54 minerals, mostly of
fixed composition, but some of variable composition depending on variables set
in the INPUT-OUTPUT sheet. These variable composition minerals include: plagioclase,
dioctahedral smectite, and fictive minerals as defined in Bowser and Jones (2002).
All mass balance calculations link to this table of mineral stiochiometric coefficients
through the VLOOKUP and HLOOKUP functions of Excel. Only minerals in this database
will link to the calculations, but minerals can be added by the user if desired.
(See section H below). The database is used in Excel's LOOKUP functions and,
thus, must be sorted both vertically and horizontally in order for the model
calculations to be done correctly.
Plot Sheets
Two sheets are used to show plots of mineral mass transfer coefficients as a function of mineral composition. Two mineral variables are used, plagioclase and dioctaahedral smectite (beidellite-montmorillonite). Data for plots are calculated on the CALC SHEET.
Plag plot sheet
Plots of mineral mass transfer coefficients versus plagioclase feldspar composition
are shown on this sheet. Generally, our mass balance analysis starts with the
generation of this plagioclase - MTC plot. It aids in understanding the limits
of composition that satisfy thermodynamic constraints on the stability of minerals
involved in the mass transfer models. To view the plot correctly the An.- Number
of the plagioclase must be set to zero in cell D9 on the INPUT-OUTPUT sheet.
Variables describing the beidellite/montmorillonite ratio of the smectite, the
interlayer-K content, and the interlayer charge can be set to best estimate
values.
Examination of the plot for crossover points (where specific mineral mass transfer
coefficients equal zero) and knowledge of the stability of the mineral in question
should help decide on the best range of plagioclase to use. (see Bowser and
Jones (2002) for further explanation.) Following this the smectite-MTC plot
should be examined if Smectite_ss is used as one of the 10 phases (see below).
Smectite Plot Sheet
Plots of mineral mass transfer coefficients versus dioctahedral smectite composition
are shown on this sheet.
Set the plagioclase composition to a reasonable value in cell D5 on the INPUT-OUTPUT
sheet, and set the % Montmorillonite to zero. A plot will be generated for mineral
MTCs versus smectite composition at the composition of the plagioclase designated
and at the value used for the interlayer-K content and interlayer charge. Examination
of the compositional limits imposed by the sign and value of mass transfers
should allow you to refine your estimate of the best smectite composition to
use, which in turn will help refine the plagioclase composition and/or interlayer-K
content of the smectite. Alternatively, switching back and forth between the
two plots with adjustment of the values should help refine the model.
NOTE: Each plot requires that the appropriate value be set to zero for the phase in question. This is merely a means to set the plots to their maximum range without any foldback of the curves. Plots may run off scale, but double clicking the MTC axis of the plot and setting the maximum and minimum values to automatic (check boxes) should allow you to see all of the data, fully scaled. Maximum and minimum bounds can also be set manually to focus on a specific phase or phase assemblage. We have found it best to set the maximum plagioclase An-number to 90 or less to keep the focus on the more interesting low An and middle composition region.
To analytically solve for precise crossover points on plots we have used the GOAL SEEK…function under the TOOLS window. We have also used this function to solve for defined mineral mass transfer coefficient ratios of phase combinations on the INPUT-OUTPUT page.
Water Compositions.
Up to ten separate water analyses (in molar concentrations or mass per unit area per unit time) are input into cells B7:Q16 on the WATER DATA sheet, along with an abbreviated site description and a two-letter code designation for each analysis. The list must be sorted alphabetically according to the codes in the first column (B7:B16). It's easiest to number the lines sequentially and rely on the descriptions in cells, C7:C16 for verification of samples chosen for analysis. Carbonate species are expressed as total carbon without regard to actual species distribution or pH. Additional cells for nitrate, pH and DIC data are included although they are not presently included in actual mass balance calculations. Provision for input of nitrate is made solely for the purpose of evaluating ion balances. The water input cells are sorted in alphabetical order by element and only the elements aluminum, iron, magnesium, calcium, sodium, potassium, chloride, total carbon, silica, and sulfate are used in the final mass balance analysis. By entering the appropriate two letter code from any of the cells in B7:B16 for the waters into cells B22:B23 the waters are automatically looked up from the appropriate water analyses and the difference calculated as inputs to the mass balanced analysis. Lacking a chemical analysis for an input water, a dummy water with zero concentrations can be added in the water database and designated as the input water in cell B22.
NOTE: Cells B7:Q16 are the ONLY place where water compositions are entered manually. The cells are colored yellow to remind you that these are user data entry areas. Any attempt to enter data directly into other cells will remove the formulae in each of these cells and result in incorrect calculations. As you gain experience with using the spreadsheet you may wish to save a file version specific to each analysis or site, thus eliminating to re-enter data specific for that particular locality of model calculation. Additionally sample waters, phase combinations, or mineral compositions can be put on a separate sheet for later use. Simple cut and paste operations can be used to put the appropriate data into these fields. Addition of a new worksheet to the file will provide scratch space for comments, model solution results, and notes.
Waters whose ion-balance are typically less than 10% are best for analysis.
Cells Q22:Q23 calculate the ion balance of the waters for user convenience.
Adjustment can be made using assumptions about the analytical certainty associated
with each ion. On the sheet Calc.Sheet we have used chloride as a balancing
ion and the small calculation correction is made by iputs in cell, B25. A similar
correction is possible for total carbon, and the correction value is entered
into cell, B24. Generally these are set to zero. As summarized in the text the
mass balance solutions are generally better if one uses flow adjusted, annualized
values rather than single analyses unless there is strong evidence that the
water varies little over time.
Minerals
Minerals are input ONLY into cells D12:D21 on the, INPUT-OUTPUT sheet. Mineral inputs are checked for correct spelling and the appropriate mineral mass transfer coefficients are looked up from the mineral database. Perhaps the most challenging part of successful mass balance analysis is in selecting which ten phases to use. Some combinations will yield impossible matrix solutions, generally an indication of too many ferromagnesian phases chosen, or phase combinations that are incompatible (e.g..: smectite, quartz, kaolinite, and gibbsite).
Use of the fictive phase function (see AJS text) can be exercised with biotite/chlorite
or cpx/hornblende, or cpx/actinolite fictive phases. In this case the fraction
of these two end members [0..1] is designated in cells F24, F25, or F26..
Cross Checks
The following items are included in this category:
Plots
No inputs are needed directly on these sheets. They merely present the results
of the calculations from the calc. sheet in graphical form for help in interpretation
of the models. The spreadsheet is set up to explore mineral mass transfers as
a result of variable composition of plagioclase feldspar and dioctahedral smectite
(e.g. beidellite-montmorillonite) Plots are generated automatically whenever
an analysis is run, but each needs special settings of mineral composition data
before it can be properly viewed.
Of course, these plots can be embellished and notated according to the usual
rules used by Excel for all spreadsheet plots (scales, labels, line colors,
symbols, and other graphic embellishments). Most of the illustrations used in
the published journal article were modified directly from Excel plots.
Details of the calculations are still in draft. For the purpose of making the spreadsheet available as soon as possible we have chosen to omit the details of this section until it is complete.
Variables defined for spreadsheet
To simplify design of the spreadsheet and to make clear what cells or cell ranges are used for calculations we have defined names for these cells. Most conveniently one can select the variable in question from the pull down list in the NAME BOX of the FORMULA BAR.
Decision among possible mass balance models rests strongly on good water analyses and mineral composition data. Ion balances help decide whether or not certain waters should be used in the analysis. Selection among satisfactory plausible phases is critical, as is the state of saturation of minerals. It is strongly suggested that you evaluate mineral saturation state and saturation indices with a solution speciation type program such as PHREEQEC, WATEQ, or equivalent. Where specific knowledge of mineral content or mineral compositions are not known then best estimates of plausible phases may serve as a springboard from which to explore possible mass balance solutions.
It has been our experience that a number of models will be required to explore
possible satisfactory solutions. The use of the plots in conjunction with various
phase combinations should help you focus on the end solutions. Paying critical
attention to crossover points on the mineral mass transfer plots and knowledge
of whether or not the mineral should precipitate or dissolve will help to find
mineral compositions that don't violate thermodynamic stabilities of the minerals
under the condition of mineral weathering. Reliance on appropriate mineralogic
compositions and geologic knowledge of the weathering situation cannot be overemphasized.
Clastic systems generally have more phases than the ten, the number to which
the spreadsheet model is limited. Use of the fictive phase and mean reactive
composition concepts should allow a greater number of phases to be considered.
In groundwater systems, having a greater number of water analyses that represent
stages in evolution down a flow path, and the assumption that all pairs of combinations
of waters should obey the same mineralogical constraints, will further enhance
confidence in the model results.
Tracing Errors
As you explore the spreadsheet for further understanding of how it works, it is helpful to refer to the formula or data input line in the Formula Bar. Use of the TRACE DEPENDENTS or TRACE PRECEDENTS functions under the TOOLS/AUDITING menu of the spreadsheet will visibly show the formula and reference linkages, and can be used to great advantage to follow the calculation structure of the sheet or sheets.
Water input data (two waters OR difference)
The program is designed to calculate differences between two input waters (the
initial water listed first and the final water listed second). Be sure all data
are converted consistently to either molar concentrations or mass per unit area
per unit time. Specific units chosen depend on the initial data, but, of course,
mixed unit data will give incorrect use. Analytical values for iron and/or aluminum
are routinely absent, or very small. It should not lever the results significantly
inasmuch as iron and aluminum typically act conservatively preserved in low
solubility, product phases such as goethite, gibbsite, or kaolinite.
Mineral phase designation
Mineral phases are entered ONLY in cells D12:D21 on the INPUT-OUTPUT sheet. Designated minerals are read into the mineral stiochiometric coefficient matrix.
Cross checks
Spelling of a mineral is checked against the database spelling to prevent entering the wrong name. The program uses a sorted database list and if the name is not spelled correctly in the input cell it will enter the wrong mineral for all calculations. Check cells E35:E44 for flagged values.
Plausible minerals
Care must be taken to use mineral phases that are likely or compatabile with
other phases in the weathering environment. For example, use of silica and kaolinite
as mineral reactants is unlikely, but kaolinite-gibbsite, kaolinite-smectite,
or smectite-silica combinations generally are OK. Similarly using highly calcic
plagioclase compositions in environments dominated by granitic/granodioritic
rocks or low to medium grade metasedimentary rocks is generally unsuitable for
satisfactory mineral mass balance analysis. Final models will be as much guided
by correct use of phase combinations as water analyses. Generally waters are
a given in that they are derived from independent chemical analysis where care
for accuracy of results is paramount. On the other hand, specific chemical analyses
of phases may be more difficult to come by, mineralogic studies of the aquifer
or watershed may be lacking, or phases may be present but not involved in the
mineral reactions that control the composition of the water.
Considerable geologic and mineralogic reasoning are required to properly interpret
or select the best model results.
Matrix dimensions
The program was written for a 10 by 10 matrix of phases and solute constraints. Dimensionality could be reduced, or increased, with re-designation of certain variables used in the calculation, although there is reason to question extending the model to larger than 10 by 10 matrices, given the extent of the data available.
If you wish to use lesser dimension matrices, it is suggested that you make all the necessary modifications and save it as a separate spreadsheet template. Redefinition of the matrices called: coeff.matrix, index, minverse, table, water, and water.database is required.
Entering new data
The database can be edited and expanded within limits, provided the user take care to ensure that new data is added correctly and that the new phases are correctly used by the Excel LOOKUP function.
Phases that should not be modified
Cells for phases that are designated as variable composition should not be
changed as they contain formulae connected to other parts of the database. They
are indicated in blue to emphasize the fact that these are formula based cells.
Only their index number can be changed (column B on the database sheet).
Replacing older data
Simply changing the mineral stoichIometric coefficients in appropriate cells for the phase can be done with ease. Of course, these changes should heed the rules of charge-balanced mineral formulae, and more complicated phases will require changes in several of the coefficients, not just one at a time. Failure to ensure that all the coefficients add up to a charge balanced mineral formula will seriously affect the model results.
Adding new phases
In principle this database can be expanded without limit. Simply highlighting a row (from within the phase database) and inserting a blank row or rows is easiest. Once this is done, two steps must be performed before the new database will work correctly. First the list needs to be re-sorted. Choose database in the style box on line in the formatting toolbar and sort alphabetically by mineral. Second, renumber the index column so that the numbers consecutively increase from one to the total number of phases in the newly expanded database. Unless all minerals are sorted alphabetically and their index numbers increase consecutively the lookup functions will choose the wrong mineral and yield incorrect results.
People using older versions of Microsoft Excel should be warned that Microsoft
used to use a different sorting algorithm for Macintosh and PC systems. If your
model produces errors in result cells this could be a problem and you would
need to resort all the lookup tables on the Database and Calc.Sheet parts of
the spreadsheet.
Numerous possibilities for extension of the speadsheet exist. Mixing of waters
derived from independently controlled composition of aquifer mineralogy, addition
of phases not in the existing database, provision for use of other phases of
variable composition, modification of matrix dimension to something other than
ten, and the use of isotopic constraint are but a few of the possible modification.
We welcome modifications of the existing program to extend its capability.
The authors would appreciate being informed of modifications, and would welcome
any suggestions for improvement. Credit for the original program should be included
in publications derived from the current version or in citing the root program
from which modified programs are obtained.
In line with the tradition of giving programs acronymic names we refer to the
spreadsheet mass balance program as SPREADBAL-2002.
Input of minerals and difference waters from the published manuscript should provide ample examples for study. Examples may be added in further revisions of this documentation.
Variables are defined for certain spreadsheet cells or ranges of cells. They
are defined as a convenience in programming and have no fundamental meaning
other than to assist in following the trail of calculations. The variables are
all seen in the pull-down window on the left side of the FORMULA BAR. By selecting
of any of these variables you are taken to the point in the spreadsheet where
that is defined.
As a favor to the authors to help keep track of who uses the program we would
appreciate an e-mail to bowser@geology.wisc.edu letting know that you've downloaded
the program, and some note about your use of the spreadsheet. We welcome dialog
with any users of SPREADBAL-2002, especially if you make modifications, find
any errors, or have suggestions for improvement.