Using Trend line to predict values on polynomial line
John Cerny
select the x and y values to generate a chart. I can then generate a
polynomial equation based on the data, and then select the equation and
paste it back into the spreadsheet. I can then enter an x value, which is
then inserted into the equation to arrive at a new Y value. This is long
and tedious.
How can I use the TREND command to interpolate data which would fit a
polynomial curve, i.e. I accessed the TREND command in the HELP folder, and
it explains how to do this for linear data. It then vaguely explains in the
"Remarks" section that you can use TREND for polynomial curves. It reads
"You can use TREND for polynomial curve fitting by regressing against the
same variable raised to different powers. For example, suppose column A
contains y-values and column B contains x-values. You can enter x^2 in
column C, x^3 in column D, and so on, and then regress columns B through D
against column A." What is regressing columns?
In other words, I would like to take data like the following and find the
equation for it:
423.350 27.0E-12
431.921 25.6E-12
440.493 24.3E-12
449.064 23.1E-12
457.636 21.9E-12
466.207 20.8E-12
474.779 19.7E-12
483.350 18.7E-12
491.921 17.7E-12
500.493 16.8E-12
509.064 16.0E-12
517.636 15.1E-12
526.207 14.3E-12
534.779 13.6E-12
543.350 12.8E-12
and then in another column, find the Y values associated with these numbers:
423.35
435.35
447.35
459.35
471.35
483.35
495.35
507.35
519.35
531.35
543.35
Thanks in advance for your help.
Richard Price
That comment in the help on TREND makes more sense if you read the help
on LINEST as well. Then for a really neat way of using the same
principle, look at:
http://www.stfx.ca/people/bliengme/
Follow "Tips and Tricks" then "Polynomial regression. How can I fit my
X, Y data to a polynomial using LINEST?"
Richard Price
"John Cerny" <john.d...@motorla.com> wrote in message
news:9e3arh$fjn$1...@newshost.mot.com...
David J. Braden
I find it easier to use LINEST; it can report more stuff, is easy to use.
Here's how: To fit your data_y to data_x with a polinomial, do
=LINEST(y_values,x_values^{1;2;3})
Use ctrl-shift-enter to get this in after you have selected four adjacent
cells in the same row. The first term it will return is the coefficient for
x^3, the second for x^2, the third for x, and the last one for the constant
term. Use these to buld up the formula for your new values of x.
BTW, the coefficients you get from LINEST will be those you read off the
TRENDLINE eqn, assuming same data and same regression.
HTH
Dave Braden
R.Venkataraman
'best" trend and finds y for x
data
423.35 2.70E-11
431.921 2.56E-11
440.493 2.43E-11
449.064 2.31E-11
457.636 2.19E-11
466.207 2.08E-11
474.779 1.97E-11
483.35 1.87E-11
491.921 1.77E-11
500.493 1.68E-11
509.064 1.60E-11
517.636 1.51E-11
526.207 1.43E-11
534.779 1.36E-11
543.35 1.28E-11
forecast
423.35 2.61875E-11
435.35 2.45606E-11
447.35 2.3013E-11
459.35 2.15334E-11
471.35 2.01087E-11
483.35 1.89764E-11
495.35 1.76809E-11
507.35 1.6359E-11
519.35 1.50351E-11
531.35 1.37195E-11
543.35 1.2424E-11
is it ok
-------------------------
"John Cerny" <john.d...@motorla.com> wrote in message
news:9e3arh$fjn$1...@newshost.mot.com...
Mike Middleton
> why not forecast function ... is it ok<
No. FORECAST is limited to a single x variable (like SLOPE and INTERCEPT).
The question asked about a polynomial, so TREND and/or LINEST must be used.
- Mike Middleton