We've updated our
Privacy Policy effective December 15. Please read our updated Privacy Policy and tap

Study Guides > Mathematics for the Liberal Arts

L1.08: Section 7

Section 7: Adapting a modeling spreadsheet to use any modeling formula that is specified

The only important differences between the various templates in Models.xls are the formula that is put into cell C3, and the labels that are put in column H next to the parameters that start at row 3 in column G. This means that a spreadsheet can easily be adapted to any specified formula. These are the formulas that are the same in every modeling spreadsheet that uses the Models.xls layout:
  • “=B3-C3” in cell D3 (this computes the deviation from the model)
  • “=D3^2” in cell E3 (the computed the squared deviation)
  • “=SUM(E3:E100)” in cell H8 (this is the sum of squared deviations, which Solver will minimize)
Cell C3 will have a different formula for each type of model. These formulas will use “A3” to refer to x (this will automatically change to A4, A5, etc. as the formula in C3 is spread down to the other data rows. For the parameters of the model, the formula in C3 will use absolute references to cells in column G: $G$3 for the first parameter, $G$4 for the second parameter, etc. For clarity, each parameter should have a label put beside it in column H, but the labels do not affect Solver or the fitting process. To make a spreadsheet apply to a specific model, the following three steps are essential (in addition to the standard formulas that are entered into columns D and E, and into cell H8:
  1. Identify the parameters in the model we intend to use.
  2. Decide which parameter cell in column G (e.g., G3) corresponds to each parameter.
  3. Enter an appropriate spreadsheet formula for the model into cell C3 (and spread it down beside all the data rows, along with the formulas in D3 and E3).
It will be easier to use the new model if we also take two additional steps:
  1. Put labels in column H next to each of the chosen parameters in column G.
  2. Describe the kind of modeling formula either in G1 or on the tab of the worksheet.
Once these modifications have been made, the worksheet is used in the same way as the other templates:
  • Make a graph of the data and model together, so that you can see if a good fit is found.
  • Set the parameters to initial values that ensure the model and data are in the same region.
  • Use the Solver tool to minimize H8 (the sum of squared deviations).
Often a new model formula is based on a combination or adaptation of models you already have used. For example, we earlier used an exponential model to fit data showing the cooling of a liquid to a known room temperature. What if the room temperature had not been known? We could have found it from the data by adding it as a parameter to the exponential model, which would now be an exponential-plus-baseline model. The model suitable for that is shown in the example below.

Example 10: Fit the model y = a (1+ r)x + b to this dataset (a, r, and b are parameters)

Answer: Modify a spreadsheet to use this formula and the standard modeling components:

  1. copy the data to columns A & B of a sheet, with the numbers starting in row 3.
  2. assign cells G3, G4, and G5 to parameters a, r, and b, respectively.
  3. set cell C3 to the formula “=$G$3*(1+$G$4)^A3+$G$5”.
  4. set cells D3 to “=B3-C3” and E3 to “=D3^2”, as usual.
  5. spread the formulas in C3, D3, and E3 down beside all the data rows.
  6. set cell H8 to “=SUM(E3:E99)” to compute the sum of squared deviations.
  7. tell Solver to minimize cell H8 by changing G3, G4, and G5.
The resulting parameter settings are a = 54.2, r = 0.13, and b = 75.   These show that the liquid started at 54.2 degrees above the room temperature of 75 degrees, and cooled at the rate of 13% of the difference per minute.
Minutes Degrees
0 127.2
1 116.4
2 107.7
3 100.8
4 95.3
5 91.0
6 87.7
7 85.3
8 82.9
9 81.3
10 79.9
11 78.9

Licenses & Attributions

CC licensed content, Shared previously

  • Mathematics for Modeling. Authored by: Mary Parker and Hunter Ellinger. License: CC BY: Attribution.