CXTFIT/Excel Tutorial

Guoping Tang*a, Melanie A. Mayes a, Jack C. Parker b, and Philip M. Jardine a

a
Environmental Sciences Division, Oak Ridge National Laboratory, P.O. Box 2008, MS-6038, Oak Ridge, TN
37831
b
Department of Civil and Environmental Engineering, University of Tennessee, Knoxville, TN 37996.

1. Introduction
This is a step-by-step guide for CXTFIT/Excel. The column experimental data in example
Fig.7.9 in Toride et al. (1995) are used for demonstration. Instruction is given to
1. Fit 3H2O breakthrough curve,
2. Fit 3H2O and B breakthrough curves simultaneously,
3. Conduct sensitivity analysis to assess the impact of velocity, pulse and dispersivity for the
convection dispersion model equation (CDE) model prediction for 3H2O,
4. Propagate parameter uncertainty to prediction uncertainty for 3H2O breakthrough,
5. Run Monte Carlo analysis for the nonequilibrium convection dispersion equation (MIM)
model for 3H2O to investigate the global sensitivity of dispersivity, mobile water fraction
and mass transfer coefficient,
6. Calculate response surface for 3H2O breakthrough curve simulation with MIM.
7. Use weighted nonlinear least squares method to incorporate prior information for
parameter estimates and analytical error for observations,
8. Use numerical integration options for nonequilibrium model with example Fig. 5.1,
9. Import code and configure Excel file from scratch,
10. Use CXTFIT/Excel in Excel 2007.
The user is recommended to start from the blank CXTFIT/Excel file, in which the CXTFIT menu
is added, code is incorporated, and Solver is configured. Besides basic Excel operations to input
data and formula, the key in using CXTFIT/Excel is to use the two functions (CDE and MIM) to
solve the transport equations, and run the macros through menu CXTFIT. Use of Excel names
can simplify the operation but is not required. In the end, we describe the distribution file list,
and how to import the code and configure Excel file from scratch. Most of the tutorial is based
on Excel 2003. Issues are addressed in the end for the use of CXTFIT/Excel in Excel 2007.

1
2. Simple Parameter Estimation
We start from the blank CXTFIT/Excel file [Link].
Step 1 open [Link], and save it as [Link]. If macro security is not set low, enable
macros.
Step 2 input experiment conditions, parameters and observations with locations and times
As shown in Fig. 2.1, fill in B2:B4 for fixed parameters, B6 for calibrating parameters with
C6:D6 for lower and upper bound for constraint, and A11:B46 for observation times and
concentrations.
Note that organization of the inputs is flexible as long as the calibrating parameters are in a range
continuous in a column, and the lower and upper bound (constraint) are next to the parameter
range.
Step 3 Input formula for model prediction corresponding to observation
Use either CDE or MIM function to calculate predictions corresponding to the observations
using the location, time, and other fixed and/or estimated parameters.
As shown in Fig. 2.2,
type in formula =CDE(1.0, A11, B$4, 1.0, B$5/B$2) in C11;
double click on the right bottom corner of cell C11 to extend the formula to the rest of rows.
The arguments in this formula are: 1.0 for relative distance from the inlet, A11 for dimensionless
time, B$4 for pulse, 1.0 for unit velocity in dimensionless form, and B$6/B$2 for the inverse of
Peclet number (See Eq. 1 in the text for more information). Default setting is used for optional
arguments for this example.
The observations can be plotted against the predictions (Fig. 2.3). When any of the parameters is
changed, the prediction in the graph changes accordingly. In this way, users can have a good idea
of the relative influence of parameters, and get a good initial guess by trial and error.
Step 4 Set up the objective function
As shown in Fig. 2.3, type in formula =B11-C11 in D11, and double click on the right bottom
corner of cell D11 to extend the formula; type in formula = SUMSQ( D11:D46) in cell F1 for
objective function.
Step 5 Solve the parameter estimation problem
Select menu CXTFIT->Solve… to open CXTFIT/Excel Solve Dialog (Fig. 2.4)
Select objective function cell, parameter range and prediction range as shown in Fig. 2.4, click
button Solve. When the optimization is finished, the Solver Results Dialog (Fig. 2.5) pops up.
Click Ok button with Keep Solver Solution, CXTFIT/Excel outputs the parameter estimate in
B6, the standard deviation in E6 and the correlation in E7 (Fig. 2.6).
Step 6 Add More Statistics
As shown in Fig. 2.7,

2
type in formula =1 – F1/(VAR(B11:B46)*(COUNT(B11:B46)-1) to calculate R square in cell F2;
type in formula =TINV(1-0.95, COUNT(B11:B46)-COUNT(B6)) to calculate t value in cell F3;
type in formula = B6-F3*E6 to calculate the low confidence limit in cell G6.
The upper confidence limit is calculated similarly.
Step 7 Options and Alternative Operations for Step 5
So far, the simple parameter estimation problem is solved. For interested users, we discuss an
alternative operation procedure and other options for Step 5.
An alternative way to open CXTFIT/Excel dialog is to select menu Tools->Macro->Macros… or
press Alt + F8 to open Macro Dialog (Fig. 2.8), select SolveDialog and click Run to open it.
Jacobian matrix is calculated when the macro Solve is invoked. Output of the sensitivity
(Jacobian) of predictions to parameters can be set by clicking on the Options button in the Solve
Dialog (Fig. 2.4 in Step 5) to open the Solve Option Dialog (Fig. 2.9). Check the last option and
make the offset 2, click button OK, then click on button Solve, then the Jacobian matrix will be
output in E11:E46, which has an offset of 2 from the prediction range (C11:C46). The scaled
sensitivity and composite scaled sensitivity can be calculated. More details for sensitivity
analysis are given in Section 5. More options in the Solve Option Dialog are described in Section
7.
In Step 5, Excel local (sheet level) names ObjFuncCell = Sheet1!$F$1, ParameterRange =
Sheet1!$B$6, and PredictionRange = Sheet1!$C$11:$C$46 are implicitly defined to specify
input for macro Solve. These names will show up when Step 5 is repeated to open Solve Dialog
(Fig. 2.10). User can reselect these cell/ranges if necessary.
These names can be defined or changed by selecting menu Insert->Name->Define… to open
Define Name dialog (Fig. 2.11). Type in Sheet1!PredictionRange, and select the prediction
range, click Add to add the local name PredictionRange. Names ObjFuncCell and
ParameterRange can be added similarly. With the names defined, macro Solve can be executed
to solve the problem similar to Step 5 without using the Solve Dialog (Fig. 2.4).
Execution of macro Solve can be accelerated by adding a button in the sheet. Select menu View-
>Toolbars->Forms, select Button in the Forms toolbar (Fig. 2.12), click in the sheet to create a
button, assign the button to Solve in the Assign Macro dialog (Fig. 2.13), and click OK to close
the dialog.
The text on the button can be changed to “Solve”. Now, click the Solve button invoke the Solve
macro, which adjusts the parameters in the parameter range to minimize the objective function,
analyze and output parameter uncertainty with one operation. This can be a convenient
alternative to Step 5 when the names are already defined.
Step 8 Simplify formula input using Excel names
Input of formulae using function CDE and MIM can be complex and prone to error. For this
example, we can define cell B4 by name T0 similar to Fig. 2.11, input formula =B6/B2 in cell B5
and define cell B5 by name IP, then the formula in C11 is =CDE(1, A11, T0, 1.0, IP).

3
Fig. 2.1 Input parameters and observations in Excel sheet

Fig. 2.2 Input formula for model prediction:

4
Fig. 2.3 Set up the objective function

Fig. 2.4 CXTFIT/Excel Solve dialog

5
 Fig. 2.5 Solver Results Dialog

Fig. 2.6 Parameter estimate results

6
 Fig. 2.7 More statistics

Fig. 2.8 Excel Macro dialog and CXTFIT/Excel macros: macro SolveDialog, JacobianDialog,
PropagateDialog, ResponseDialog and MonteCarloDialog opens the interfaces to specify input
and output and execute the macro Solve, GetJacobianMatrix, Propagate, CalculateResponse and
MonteCarloAnlyze to perform related functions; macro AddMenu and DeleteMenu
adds/removes CXTFIT menu; macro Optimize and Analyze can be used for optimization and
uncertainty analysis.

7
 Fig. 2.9 Solve Option Dialog

Fig. 2.10 Reopen Solve Dialog in a sheet where local names are defined.

8
Fig. 2.11 Define Excel range names. The sheet name “Sheet1!” is added before PredictionRange
to make the name local to the sheet.

Fig. 2.12 Add and assign a button to macro Solve in the sheet

9
Fig. 2.13 Assign macro to a button.

10
3 Simultaneous Fit of Two Tracers
We build on Section 2, add B data and fit the two tracers simultaneously.
Step 1 make a copy of Sheet1 to add B data
Select menu Edit->Move or Copy sheet… to open Move or Copy dialog (Fig. 3.1), select Sheet1,
check Create a copy, and click OK to copy Sheet1 to Sheet1 (2).
Step 2 input parameters, observations, formula for model prediction and residual (Fig. 3.2) in
the new sheet (Sheet1 (2)) like Step 2 and Step 3 in Section 2,.
Fill in C3:C5 with experiment condition, B7:B8 for the additional parameters to be estimated,
and E11:F40 for B observation times and concentrations.
Type in formula =MIM(1.0, E11, C$4, B$2/B$6, B$7, B$8, C$5) in cell G11 and double click
the right bottom corner of cell G11 to extend the formula for all the B prediction. The arguments
are 1.0 for relative distance from inlet, E11 for dimensionless time, C$4 for dimensionless pulse
volume, B$2/B$6 is the Peclet number (= L/λ), B$7 is the fraction, B$8 is the mass transfer
coefficient, and C$5 is the retardation factor.
Type in =F11-G11 in cell H11 and double click the right bottom corner of the cell to extend the
formula for residual calculation.
Step 3 arrange the prediction range and update the objective function
To create a continuous range including predictions for both 3H2O and B, type in formula = C11
in cell J11, and extend the formula to J46; type in formula =G11in cell J47 and extend the
formula to J76 (Fig. 3.3).
Similarly, put observations for 3H2O and B in I11 to I76.
In cell F1, add +SUMSQ(H11:H40) to the existing formula to include residuals for B.
Step 4 Solve the problem
Select menu CXTFIT->Solve to open Solve Dialog will open a dialog similar to Fig. 2.10.
Because the three local names are defined in Sheet1 and kept in the new sheet when it is copied
from Sheet1. Select the new parameter range and prediction range (Fig. 3.4), click button Solve
to solve the problem. The results are shown in Fig. 3.5 with update of parameter estimate in
B6:B8, standard deviation and correlation in E6:H8. Note that the standard deviations and
correlations are relatively small while the model describes the breakthrough curves reasonably
well.
Step 5 Update Statistics
Because the parameter and prediction ranges changed from the case in Section 2, we need to
update the formulae for statistics (e.g., R square, confidence intervals).
To update the formula in cell F2 for R square calculation, change the formula to
=1-F1/(VAR(I11:I76)*(COUNT(I11:I76)-1)).

11
 Fig. 3.1 Move or Copy Dialog.

Fig. 3.2 Add parameters, observations, and formula for predictions and residual for B

12
Fig. 3.3 Update the objective function and arrange the prediction range

Fig. 3.4 Solve dialog

13
Fig. 3.5 Parameter estimation results.

14
4 Sensitivity Analysis
Suppose that we are interested in the sensitivity for dispersion coefficient, velocity and pulse
volume, we will copy sheet1 we obtained in Section 2 and conduct sensitivity analysis.
Step 1 Make a copy of Sheet1 similar to Step 1 in Section 3.
Step 2 Change parameter range
Since we are interested in dispersion coefficient, pulse duration and velocity, we input dispersion
coefficient in B5 so that the three parameters are in a continuous range in the new sheet (Sheet1
(3)).
Type in formula =B5/B3 (λ=D/v) in B6 for dispersivity calculation. The dispersivity is further
used in column C for prediction calculation in the same way as in Section 2 (Fig. 4.1).
Step 3 Open Jacobian Dialog to calculate and output the Jacobian matrix
Select menu CXTFIT->Calculate sensitivity… to open Jacobian dialog (Fig. 4.2).
Select the new parameter range, change the offset to 1, and click Calculate (Fig. 4.2).
The Jacobian matrix is output next to the prediction range because the offset is set to 1 (Fig. 4.3).
Step 4 More sensitivity analysis
Type in formulae =D11*B$3, =E11*B$4, =F11*B$5 in cell G11, H11, and I11, and double
clicking on the right bottom corner of these cells to extend the formulae to calculate the scaled
sensitivity in column D, E, and F for velocity, pulse and dispersion coefficient (Fig. 4.3).
Type in formula =SQRT(SUMSQ(G11:G46)/COUNT(G11:G46)) in G6 to calculate composite
scaled sensitivity for v. Copy cell G6 and paste to H6, and I6 for pulse and dispersion coefficient.
The sensitivity is shown in plot in Fig. 4.3. The pulse appears to be relatively very sensitive
while v and D are relative insensitive. This may suggest that a small error in pulse may have
significant impact on the model prediction, as well as the estimate of v and D.

15
Fig. 4.1 Change the parameter range to analyze sensitivity for velocity, dispersion coefficient and
pulse duration.

Fig. 4.2 Jacobian dialog

16
Fig. 4.3 Sensitivity analysis results

17
5. Error Propagation
Suppose that we are interested in assessing the uncertainty for the prediction of B breakthrough
at distance of 50 cm. The velocity is 50 cm/h. The retardation factor with a standard deviation of
0.2 in addition to the estimates from Section 3 is used for model prediction (Fig. 5.1).
Step 1 Make a copy of Sheet1 and set up the problem like Fig. 5.1
Step 2 Open Propogate Dialog
Select menu CXTFIT->Propagate parameter uncertainty to open Propagate dialog (Fig. 5.2).
Select the parameter range and prediction range, change the offset for error propagation to 1, and
click Calculate (Fig. 5.2). The error propagation from the parameters to the predictions is output
next to the prediction range (Fig. 5.3).
Step 3 Calculate prediction uncertainty
Assume that the average error of the calibrated model is 3% (RMSE = 0.03 in cell F1), a rough
approximation of the standard deviations for the predictions is calculated in column D by typing
in formula = SQRT(C11 + $F$1^2) in cell D11, double click on the right left corner of cell D11
to extend the formula.
Step 4 Calculate the approximate prediction confidence interval
Assume the t value of 2.0 (2.0 in cell F2), type in formula = B11 - $F$2*D11 in cell E11, double
click on the right left corner of cell; type in formula = B11 + $F$2*D11 in cell F11, double click
on the right left corner of the cell. The confidence band is plotted against the prediction in Fig.
5.3.

Fig. 5.1 Error propagation example

18
 Fig. 5.2 Error propagation dialog

Fig. 5.3 Prediction uncertainty assessment results

19
6. Monte Carlo Analysis
Suppose that we want to evaluate the global uncertainty/sensitivity of dispersivity, mobile water
fraction and mass transfer coefficient for the simulation of 3H2O breakthrough curve. We start
from Section 2 and use the mobile-immobile water model at first, then we will create random
input and conduct Monte Carlo simulation.
Step 1 Make a copy of Sheet1, work on sheet Sheet1 (5)
Step 2 Update the forward prediction with the mobile-immobile model
Add values for mobile water fraction and mass transfer coefficient in cells B7 and B8 (Fig. 6.1).
Change the formula in C11 to =MIM((1, A11, B$4, B$2/B$6, B$7, B$8), double click on the
right bottom corner of cell C11 to extend the formula.
Remove the contents in C6:H6 and F2:F3
Change the formula to =SQRT(SUMSQ(D11:D46)/(COUNT(B11:B46)-COUNT(B6:B8))) in F1.
We use RMSE instead of SSR as the merit function because RMSE is an approximation of the
fitting error and has the same unit as concentration. In case of reduced concentration for
observation in this example, it is a relative error.
Step 3 Create random input values
Type in =RAND() in cell G2, extend the formula to G1001. This creates 1000 random numbers
uniformly distributed between 0 and 1 (Fig. 6.2).
Select cells G2:G1001, press Ctrl + C
Select cell H2, select menu Edit->Paste as special…, check Value, click OK paste it as values in
cells H2:H1001. Repeat the operation to copy it as values in I2:I1001, and J2:J1001.
Type in formula =EXP(-4+6*H2) in cell K2, double click on the right bottom corner of cell K2
to extend the formula. ln(λ) is sampled in randomly distributed value between -4 and 2 (λ ranges
from 0.02 to 7.4).
Similarly, type in formula =I2 in cell L2; extend the formula; mobile water fraction is sampled
between 0 and 1; type in formula = J2*10 in cell M2, and extend the formula. The mass transfer
coefficient is sampled between 0 and 10. The contents in G2:G1001 can be removed then.
Step 4 Open Monte Carlo Analysis Dialog to perform Monte Carlo simulation
Select menu CXTFIT->Monte Carlo analysis… to open Monte Carlo Analysis dialog (Fig. 6.3).
Select the merit function cell, parameter range, random parameter input range and progress cell
as shown in Fig. 6.3, click Calculate.
Cell F2 shows the current set of parameters that is calculating during the process. The
corresponding RMSE is recorded right next to the random parameter input range (column N).
The RMSE is plotted against the parameters in Fig. 6.4. The small amount of sampling results
show that the parameter uncertainty is quite significant as long as the measurement error is equal
to or greater than 0.03.

20
Fig. 6.1 Update the forward prediction for Monte Carlo analysis

Fig. 6.2 Input random values for Monte Carlo analysis

21
Fig. 6.3 Monte Carlo Analysis dialog

Fig. 6.4 Monte Carlo analysis results

22
7. Response Surface Calculation
Suppose that we use the nonequilibrium convection dispersion model to simulate the data in
Section 2, and we want to exam the response surface with respect to mobile water fraction (β)
and mass transfer coefficient (ω).
Step 1 Set up the merit function and values for β and ω
At first, we set up the problem with merit function following Step 1 and Step 2 in Section 6 as
Fig. 7.1. Then we input values from 0.60 to 0.99 in column E for β, -1 to 1 in row 1 for log10(ω),
and formula =log10(F1) in cell F2, and extend the formula from G2 to AT2.
Step 2 Calculate response surface
Open Calculate Response Surface dialog by selecting menu CXTFIT->Calculate response
surface… to open the dialog (Fig. 7.2);
Select the merit function cell, parameter range, vertical input range, and horizontal input range
(Fig. 7.2)
Press “Calculate” button to conduct the calculation.
Step 3 Plot contour.
Consult Excel help files for help.
Step 4 Calculate response surface with a different λ values
Make a copy of the resulting sheet from the previous steps (see Step 1 and Fig. 3.1 in Section 3).
Change λ value in cell C3.
Open Calculate Response Surface dialog by selecting menu CXTFIT->Calculate response
surface… to open the dialog (Fig. 7.2), and click on button Calculate.
This step can be repeated for a number of λ values to calculate RMSE for three parameters. A 3-
d contour can be plotted in graphic software with these data.
Note as more rows or columns are added, the calculation can be performed incremently by
selecting only the added rows and/or columns to save computational time.

23
Fig. 7.1 Input values for β and ω for response surface calculation.

Fig. 7.2 Response surface calculation dialog

24
Fig. 7.3 Contour for response surface

25
8. Weighted Least Squares with Penalty Function
Suppose that the chemical analytical error is 0.03 for 3H2O and 0.05 for B, measurement error for
pulse is 1% for both tracers, and the uncertainty in retardation factor is 5% of the mean for B.
Assume that we do not have direct measurement for dispersivity, mobile water fraction and mass
transfer coefficient, we use a large uncertainty for these parameters. Then we can redo the
parameter estimation in Section 3 with weighted least squares method with penalty function to
incorporate the measurement error for concentration observations as well as experiment
conditions (pulse, retardation factor).
Step 1 Make a copy of sheet Sheet1 (2) and reorganize the new sheet as shown in Fig. 8.1.
Since pulse and retardation factor are usually sensitive parameters, their uncertainty is not
ignored when they are included for parameter estimation. The mean, standard deviations,
estimates for adjustment for the parameters are given in B3:B8, C3:C8, and E3:E8. The weighted
residuals are given in D3:D8 with formula =(B3-E3)/C3 for cell D3.
The observations for both tracers are moved into column G, and the standard deviations for
observations are given in column H. The weighted residual is calculated in column J with
formula =(G11-I11)/H11 for cell J11.
The formula for the objective function is =SUMSQ(J11:J76)+I1*SUMSQ(D3:D8) in cell L1
with cell I1 as the penalty cell.
Step 2 Open Solve Dialog and Solve Option Dialog to solve the problem
Select menu CXTFIT->Solve… to open CXTFIT/Excel Solve Dialog.
Select the objective function cell, parameter range and prediction range as shown in Fig. 8.2
Click Option button to open the Solve Option Dialog, selection options as shown in Fig. 8.3.
Click Ok button to close Solve Option Dialog, click Solve button to solve the problem.
The parameter estimates are updated in the parameter range (E3:E8), the standard deviations and
correlation matrix are output in H3:N8 (Fig. 8.4).
Note that the values for observation and prior parameter estimates are hypothetical. Nevertheless,
the option to include this information can be useful when measurement errors are reliably
quantified.

26
Fig. 8.1 Set up to incorporate measurement error for parameter estimation

Fig. 8.2 Solve Dialog

27
 Fig. 8.3 Solve Option Dialog

Fig. 8.4 Estimation results using weighted least squares with prior information included as
penalty.

28
9. Use of Numerical Integration Options
The example Fig. 5.1 in Toride et al. (1995) is used to illustrate the use of numerical integration
options. The parameters are input in D5:D13, and E11:G12. The time instants are put in
B21:B221. The dimensionless pulse is calculated in cell G8 by formula =D9*D8/D5. The Peclet
number is calculated in cell G9 by formula =D9*D5/D10.
The five numerical integration schemes are listed in N1:N5, and are used to create a list in cell
L5 using menu Data->Validation. Similarly, FALSE and TRUE are listed in O1:O2, and are used
to create a list in cell L6. The tolerance is input in L7 for the adaptive numerical integration
schemes (Romberg, Simpson, and Lobatto), and number of integration points is in L8 Chebyshev
and Gauss scheme. Then define strNI, LogTransform, tol, and nPoint for the L5, L6, L7, and L8.
Then the formula for D21 is shown in Fig. 9.1, and can be extended to the rest of the cells for
forward solution. Users can select different numerical integration schemes, whether time
coordinates be log transformed, change the tolerance or number of integration points and
compare the efficiency and accuracy of different schemes.

Fig. 9.1 Illustration of using different numerical integration scheme.

29
10. Import Code and Configure Excel File from Scratch
10.1 Source Code Files
The source code is provided in [Link], which includes all of the functions and
subroutines (macros). The user-friendly interfaces (dialogs) are provided in the following files
[Link], [Link]
[Link], [Link]
[Link], [Link]
[Link], [Link]
[Link], [Link]
[Link], [Link]
[Link], [Link]

10.2 Import Source Code

Step 1 Open a new Excel file if not opened.
Step 2 Install Solver if not installed.
If Solver is installed, the menu item Solver is added in the menu Tools. If not installed, Select
menu Tools->Add-Ins… to open the Add-Ins dialog (Fig. 10.1)
Check the box next to the Solver Add-in, Click OK. Excel will install the add-in and place a new
menu item (Solver…) under tools menu.
Step 3 Import CXTFIT/Excel source code
Select menu Tools->Macro->Visual Basic Editor or press Alt+F11 to open Microsoft Visual
Basic Editor (Fig. 10.2).
Select menu File->Import File or press Ctrl + M to open the Import File dialog (Fig. 10.3)
Select one file at a time, and press Open to import the file. Import all of the files listed in Section
10.1.
Step 4 Configure Solver Reference in Visual Basic Editor
In Visual Basic Editor, select menu Tools->References… to open References-VBAProject dialog
(Fig. 10.4).
Check Solver, and Click OK. Close Visual Basic Editor and now CXTFIT/Excel is appropriately
configured. You may save the file as a blank CXTFIT/Excel template file.
In the case of not finding Solver in available references list, our experience is to go back to
Excel, run Solver by selecting menu Tools->Solvers, and then close Solver. Then open Visual
Basic Editor and select menu Tools-References… to open Reference-VBAProject Dialog again.
Usually, Solver will show up in the list.
Step 5 Add code to manage menu CXTFIT
Select ThisWorkbook (Fig. 10.2), add code like Fig. 10.5 so that CXTFIT menu will be added
when this workbook is activated and removed when the workbook is deactivated.

30
Fig. 10.1 Add-Ins Dialog to install Solver

Fig. 10.2 Microsoft Visual Basic Editor

31
 Fig. 10.3 Import File Dialog to import source code

Fig. 10.4 References-VBAProject to set references to Solver

32
Fig. 10.5 Add code to manage menu CXTFIT

33
34
11. Use of CXTFIT/Excel in Excel 2007

In addition to different graphic user interface (GUI), Excel 2007 uses a different solver add-in
(C:\Program Files\Microsoft Office\Office12\Library\SOLVER\ [Link]) from Excel
2003 (C:\Program Files\Microsoft Office\Office11\Library\SOLVER\ [Link]). An
Excel 2007 file, [Link], is provided for Excel 2007 users. When macro is enabled and
Solver Add-in is loaded, use of CXTFIT/Excel in Excel 2007 is similar to that in Excel 2003.
The CXTFIT menu is in Add-Ins tab. Defining Excel range or constant name is a little easier in
Excel 2007 by using Name Manager in Formulas tab.
The following procedure from Excel help file to load Solver add-in is included here

1. Click the Microsoft Office Button , and then click Excel Options.

2. Click Add-Ins, and then in the Manage box, select Excel Add-ins.

3. Click Go.

4. In the Add-Ins available box, select the Solver Add-in check box, and then click OK.

Tip If Solver Add-in is not listed in the Add-Ins available box, click Browse to locate the add-in.

If you get prompted that the Solver Add-in is not currently installed on your computer, click Yes to install it.

5. After you load the Solver Add-in, the Solver command is available in the Analysis group on the Data

tab.

Users may also need to open CXTFIT/Excel files in Excel 2003. When both Excel 2003 and
Excel 2007 are installed, and Solver Add-in is properly loaded in Excel 2003, Excel 2007 may
use Excel 2003 Solver Add-in. In that case, both CXTFIT menu and Solver are in Add-Ins tab.

If Excel 2007 can not access Excel 2003 Solver Add-in, users need to replace Solver Add-ins
after opening Excel 2003 file. At first, Solver should be loaded in Excel 2007, and then follow
Step 4 in Section 9.2 to replace the Solver Add-ins (C:\Program Files\Microsoft Office\Office12\
Library\SOLVER/[Link]).

Similarly, when Excel 2003 is used to open Excel 2007 file, users need to replace Excel 2003
solver add-in.

35
Table 1. Predefined Excel range and constant names to specify input for and output from
CXTFIT/Excel macros.

Name Desciptions
ObjFuncCell cell for objective function (Obj in Eq. 3)
ParameterRange range for parameters to be estimated
PredictionRange range of predictions corresponding to observations, and/or the
Jacobian, error propagation to be calculated
OffsetParaStd offset from ParameterRange for input of standard deviations for
estimated parameters
OffsetParaPtb offset from the ParameterRange for input of user specified
perturbations for derivative calculation, not used if not defined or zero
NoParaConstraint no constraint to be imposed on the estimated parameters if defined.
Otherwise, the values in the two columns next to ParameterRange are
used as lower and upper constraints for parameter estimation
OffsetPredStd offset from PredictionRange for input of the standard deviation of the
observations
OffsetPredJcb offset from PredictionRange for output of Jacobian matrix
OffsetPredEP offset from PredictionRange for output of parameter uncertainty
propagation
PenaltyCell cell for relative penalty ( in Eq. 3), default value 1 if not defined
MCMeritCell cell for merit function or error measure for Monte Carlo analysis
MCParaInputRange range of user input random values for parameters
MCCurrentCell cell to show index of current set of parameters for which model
prediction is running
SolveUserFinish if defined, the results dialog for solver will not pop up.
RSMeritCell cell for the merit function for response surface calculation.
RSVRange a continuous range in a column for response surface calculation.
RSHRange a continuous range in a row for response surface calculation.

36