88 M1.03: Power Models Part II
To make a worksheet to fit the [latex]y=\text{scale}\cdot{x}^{\text{power}}[/latex] formula to data, we will make these changes (also put appropriate labels above or beside the active cells to help remember how they are being used):
- Decide on cells to use for each parameter. We will use G3 for scale and G4 for power
- Put the following formula into cell C3 as the model formula: =$G$3*A3^$G$4
- (This is the only step that is different between different types of model.)
- Put the formula =B3-C3 into cell D3, to compute the Data-Model deviation.
- Put the formula =D3^2 into cell E3, to compute the squared deviation.
- Put the formula =SUM(E3:E100) into H8 (or some other unused cell).
- To promote prompt convergence in the search process, set the initial values for the scale and power parameters so that the graph of the model roughly similar to the graph of the data (see note below).
Now this modified worksheet is a power-function template that can be used to fit a power model in the same way as the Linear, Quadratic, or Exponential templates—add the data, spread the formulas in C3, D3, and E3 down to match the data, make a graph and adjust the parameters to make the model similar to the data, then use Solver to find the best-fit parameters by minimizing H8, the sum of squared deviations.
Optional: a systematic way to set approximate initial values for power-function model parameters
- Set the power parameter equal to zero; this will make the model into a horizontal line at y = scale.
- Set the scale parameter equal to about the middle of the range of output values; the graph of the model will now pass through the data, with some data points above it and some below it.
- If the data is increasing as x increases, set the power parameter to 1; otherwise set it to -1.
- Repeat the sequence below until the graph of the model is roughly similar to the graph of the data:
- If the data is more curved than the model, double the power parameter; if less curved, halve it.
- If the data is all substantially smaller than the model (i.e., closer to the x-axis), double the scale parameter; if the data is all substantially larger than the model, halve the scale parameter.
- Once the model is roughly similar to the data, use Solver starting with these parameter settings.
|
Example 1: [a] Use the dataset on the right to find a model formula for the length of a planet’s year in days based on its average distance from the sun in miles. [b] Use that model to find year length for the dwarf planet Ceres, whose average distance from the sun is 256.1 million miles. Solution approach: [a] Make a power-law spreadsheet as described above, then put the distance data into column A and the year-length data into column B (don’t copy the names). Then use Solver to get the best-fit model. |
|
||||||||||||||||||
|
[b] Once the model has been found, type the Ceres distance of 257.7 million miles into column A in the next row below the data (i.e., A11). The model’s year-length prediction for Ceres will be in C11. Answers: [a] The best-fit model is [latex]y=0.410\cdot{{x}^{1.50}}[/latex] [b] The predicted year for Ceres is 1,680 days. |
Both the square-root function y = x0.5 and the square function y = x2 are power functions. For positive values of x they are also inverse functions, since the square root of the square of a number reproduces the original number (and conversely). This occurs because the numbers 0.5 and 2 are reciprocals of each other. This is true in general—the inverse of a power function is a different power function in which the new power is the reciprocal of the original one.
|
Example 2: [a] Use the data from Example 8 to find a good model for using the length of a planets year to predict its average distance from the sun. [b] Use that model to find the average distance from the sun of the asteroid Eros, whose year is 643 days long. Solution approach: [a] Make a power-law spreadsheet as described above, but this time put the distance data into column B and the year-length data into column A. Then use Solver to get the best-fit model. [b] Once the model has been found, add the Eros year length of 643 days into column A in the next row (i.e., A11). The model’s prediction for Eros’s average distance to the sun will be in C11. Show Answer[a] The best-fit model is [latex]y=1.80\cdot{{x}^{0.667}}[/latex] [b] The predicted average distance for Eros is 134.4 million miles. |
|
