# 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)

*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:

**Identify the parameters in the model we intend to use.****Decide which parameter cell in column G (e.g., G3) corresponds to each parameter.****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).

**Put labels in column H next to each of the chosen parameters in column G.****Describe the kind of modeling formula either in G1 or on the tab of the worksheet.**

- 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).

### 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:

- copy the data to columns A & B of a sheet, with the numbers starting in row 3.
- assign cells G3, G4, and G5 to parameters
*a*,*r,*and*b*, respectively. - set cell C3 to the formula “=$G$3*(1+$G$4)^A3+$G$5”.
- set cells D3 to “=B3-C3” and E3 to “=D3^2”, as usual.
- spread the formulas in C3, D3, and E3 down beside all the data rows.
- set cell H8 to “=SUM(E3:E99)” to compute the sum of squared deviations.
- tell Solver to minimize cell H8 by changing G3, G4, and G5.

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.