| Elevation (feet) | Surface (sq-ft) |
|---|---|
| 325 | 100 |
| 326 | 1350 |
| 327 | 10,100 |
| 328 | 31,250 |
| 329 | 80,150 |
I have been working with this data set attempting to use a nonlinear regression to generate an equation. I found a fit using a 3rd degree polynomial which can be shown in the image, however when I use the equation, the results for surface area are not correct. These values are also shown in the attached image despite having an R squared value at 0.9997. I don't understand why this is not working properly and if there is a better way for accomplishing this type of computation. I am trying to generate an equation that will extrapolate this data from 1' intervals to 1" intervals. For example 325 1/12, 325 2/12, etc. I can use this equation to expand the data while calculating the surface area at the expanded elevation. Why is this nonlinear regression in Microsoft Excel not working properly? Is there a better way to accomplish this computation? Please help and thank you in advance for your time and guidance.
May I suggest that you add decimal places to the fit coefficient display. The first thing I see is that your polynomial only shows one or two decimal places for each coefficient. That may be causing you significant round off error when you try to use your fit. Doing so I obtain:
$$y=1.6875*10^3 x^3-1.6477*10^6 x^2+5.3631*10^8 x-5.8186*10^{10}$$ $$R^2=9.9965*10^{-1}$$
I cannot say for sure what is wrong without more information as to what is going wrong and how you know it's wrong.
Also, the fit is linear. It's not a non-linear regression because the fit is linear in its polynomial coefficients. This is quite important to understand for statistical purposes.