111 I1.03: Section 2
Section 2: Using a spreadsheet model to predict values
The benefit for having a good model formula for a relationship is that you can predict what output value should be expected for any input value, even if you have no data with that input value. This can be done simply by computing what y value the model evaluates to when the input value is used as the x value. For example, the model from the previous section predicts an output 21.3 for an input value of 10, because when 10 is substituted for x, the formula y = 1.4 x + 7.3 becomes y = 1.4×10 + 7.3 = 21.3.
Predictions are particularly easy to produce from a model if you already have the model formula entered into a worksheet similar to the one you made in the previous section. Whenever you enter a new x value into the cell in column A that is immediately below the last data value (such as A13), the spreadsheet automatically extends the formula in column C to that same row, showing the model’s prediction for the new input value. (This process can be repeated for additional input values as needed. If your spreadsheet program does not automatically extend column C, you can spread the formula down by hand to get the same effect.)
Show Answer
[a] y = 21.3 [b] y = 17.8 [c] y = 3.1 [d] y = 11.3 [e] y = 761.9 [f] y = -279.7
When adding values to columns A and C, what should be done to column B?
This is a natural question, since the empty cells (such as B13 and below) seem to leave a gap in the pattern of the worksheet. But don’t add to column B when predicting with the model. You can make as many predictions with the model as you wish, but you mustn’t make up data. Leave column B blank except for the data values that you are given to start with.
How reliable will the predictions of a model that closely matches the data be?
Notice that a model formula can be used both for interpolation for x values within the range of the data (such as in [b] and [d] above) and for extrapolation, in which the predicted point is for an x value that is outside the data range (such as in [a], [c], [e], and [f] above). Interpolation is generally dependable, although the noise in the relationship will usually keep it from exactly predicting future measurements. Extrapolation is less reliable, especially when the prediction being made is for a point that is very far away from the data on which the model is based. This is discussed in more detail in a later section.
Example of erroneous extrapolation: For a given child, one can record the child’s height and age in years. During the elementary-school years, that is a fairly linear relationship, with a growth rate of about 3 to 4 inches per year. But you would get very erroneous results if you use that model to estimate the typical height of 30-year-olds (who would be about 10 feet tall if the youthful pattern continued).
