21.5: I1.05- Section 3 Part 2
- Page ID
- 51717
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)Example 3: Using Models.xls to fit a linear model to a dataset
The table to the right gives data on the measured rate at which sediment built up in a factory holding tank during routine operation, after a cleaning process that is repeated a couple of times per year. The factory operators want to use this information to make a formula to predict sediment depth at any chosen time after a cleaning.
A preliminary graph of the data shows that the pattern of the points is reasonably close to a straight line. Therefore, the “Linear Model” worksheet in Models.xls is the appropriate one to use.
Days since cleaning | Depth (mm) |
10 | 29.9 |
20 | 48.0 |
30 | 60.5 |
40 | 88.6 |
50 | 102.9 |
60 | 114.1 |
70 | 141.1 |
80 | 149.5 |
Solution:
In an earlier topic, you used a spreadsheet to adjust the intercept and slope of a linear equation and saw the resulting changes in the position of the straight-line graph. We will now use that same technique to make a good linear model for this data with Models.xls.
- Insert a new worksheet into Models.xls, labeling its tab “Linear Sediment Model”. Then copy into the new worksheet the contents of the read-only worksheet labeled “Linear Model Template”.
- In this case we want to predict sediment depth for any given number of days since the last cleaning. This means that we want to use day as the input variable x and depth as the output variable y.
- Copy the data to the spreadsheet (columns A and B, rows 3 to 10 for the numbers), then label the top of the data columns with “Days” in A2 and “Depth” in B2.
- Select C3 (which contains a preset linear formula based on the values in G3 and G4) and spread the formula down to row 10, matching the data. At first, these model values will be zeros.
- Also select and spread D3 and E3 down to row 10. The values in columns D and E will not be very meaningful until you adjust the model to be a good fit.
- Make a scatter plot of the data and model columns together (that is, the rectangle A1:C10). At first, the model points will lie on a horizontal line along the x-axis.
- Adjust the parameters in G3 and G4 so that the model points are as close as you can get them to the data points.
For a linear model, here is a good parameter-adjustment strategy:
- Set the intercept to approximately where the data trend crosses the y axis (about 10 in this case, although you do not need to be exactly right since you will adjust the intercept again in [c] below),
- Adjust the slope to make the model line parallel to the data trend (in this case, 1 is too low a value for the slope, and 2 is too high; 1.8 seems about right).
- Now adjust the intercept to its best value, moving the model line without changing its slope until the model goes right through the data (in this case, a value of 11 for the intercept works well).
- Check to see if the model is good. In this case, the model points are close to the data points over the whole data range, showing that a linear model is the correct type to use for this data.
- Write the mathematical formula for the model you have found: y = 1.8 x + 11
The Linear Sediment Model worksheet should look about like this at the end of this solution process:
A | B | C | D | E | F | G | ||
1 | x | y data | y model | Data-Model | Linear model: y = m * x + b | |||
2 | Days | Depth | Prediction | deviation | y = 1.8x+11 | |||
3 | 10 | 29.9 | 29 | 0.9 | 11 | b: Intercept | ||
4 | 20 | 48.0 | 47 | 1 | 1.8 | m: Slope | ||
5 | 30 | 60.5 | 65 | -4.5 | ||||
6 | 40 | 88.6 | 83 | 5.6 | ||||
7 | 50 | 102.9 | 101 | 1.9 | ||||
8 | 60 | 114.1 | 119 | -4.9 | ||||
9 | 70 | 141.1 | 137 | 4.1 | ||||
10 | 80 | 149.5 | 155 | -5.5 | ||||
11 | ||||||||
12 | ||||||||
13 |

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