151 O.04: Section 2
Section 2: Avoiding confounded parameters—don’t use two parameters to control the same thing
Models formed by adding two basic models together are very useful, but in some cases a problem arises because both models have parameters that control the same thing (e.g., vertical offset). When this is true, there is not any “best-fit” solution for these parameters, since any combination of vertical-offset values that gives a good fit could be replaced by other values which add up to the same thing. In such a situation, what values Solver will find for these “confounded” parameters depends unpredictably on their initial settings.
This problem can be avoided by eliminating one of the confounded parameters. If the compound model is the sum of a linear model and a sinusoidal model, for example, the linear intercept parameter and the sinusoidal baseline parameter both control the vertical offset. In this case, it would be best to leave out the sinusoidal baseline parameter (use only wavelength, amplitude, and phase), because the natural way to think about data of this kind is as a straight line with sinusoidal deviations.
|
Example 3: A series of monthly calibration measurements of the bias in pounds of an outdoor scale produces the data shown to the right. An examination of the graph of the data (shown below) indicates that its pattern is a combination of a gradual multi-year trend (probably due to wear of some part) and a repeating seasonal variation (probably due to temperature variation). Use a compound model combining a linear model with sinusoidal variation to [i] determine the annual rate of change shown by the multi-year trend and [ii] to predict the bias 8 months after the last data point shown. Solution: The compound model should have the two linear parameters of intercept and slope, but it should use only three of the four sinusoidal parameters, since the average parameter is added on to the result in the same way as the intercept. So the formula of the model is y = intercept + x * slope + amplitude*sin(2π*(x+phase)/wavelength), so C3 needs to be “=$G$3+A3*$G$4+$G$6*SIN(2*PI()*(A3+$G$7)/$G$5)”, which is to be spread down beside the data as usual. Once the spreadsheet is set up, we can use Solver to minimize the sum of squared deviations by changing the five parameters G3:G7. When you have a lot of parameters, it becomes more important to start Solver with initial values that ensure that are reasonably correct, so that Solver does not get lost in its search for the best-fit values. This is not difficult if you use the data+model graph for feedback, and make some simple estimates from the data. There is no need to start with a close match – Solver will do that work – but you want to avoid situations where Solver tries a very incorrect value for a parameter like wavelength, for example. In this case, it will work to use values such as 1.0 for the intercept, 12 for the wavelength (since the temperature variations should have a 12-