I fit a trendline to some data I have, shown below.
809920.89 1688.50
811026.35 1685.90
812370.50 1680.80
813553.47 1673.80
814357.32 1670.65
the equation given by excel (polynomial 2nd degree fit) is
y=-4e-7x^2+.6676x-267718
however, if you plug the first x point (809920) into this equation, the
value given is over 10,000.
where is the problem?
As I wrote previously, your problem was basically due to not
formatting -4e-7 to sufficient precision for the magnitude of "x"
values that you have.
You would not have encountered the problem in the first place if you
had used LINEST() to compute the coefficients in your worksheet
formula. Refer to
http://j-walk.com/ss///excel/tips/tip101.htm .
However, there is a more fundamental question to ask yourself,
namely: why are you using a 2nd order polynomial to fit your data?
True, that might give you the illusion of the best fit. But the
highest RSQ alone does not necessarily determine the best fit.
For example, a 2nd order polynomial describes a parabolic curve. In
your case, the vertex (maximum point on the curve) is at about
807083.88. If you were to use the characteristic formula to
extrapolate beyond that point, is the trendline truly behaving the way
the data would?
When I look at your data, I see either a linear or logarithmic curve
with a downward slope. That really depends on your interpretation of
the model that produced the data. I just wanted to express some
suspicious that a 2nd order polynomial is really the right answer.
(But it might be.)
On the other hand, if you do not intend to extrapolate beyond the "x"
limits in your data, it might make sense to use the 2nd order
polynomial because it presumably does provide the closest fit within
those limits.
