Finding Data Point INSIDE a Trend Line?

[HighLow]

I built an Excel chart using two columns of data. Each column of data
is represented by a single line on an Excel chart.

I then added a Trend Line using Excel's built-in Polynomial function.
I'm using Excel's Polynomial Trend Line and I set it to 2nd Order
Polynomial.

Now... Here is what I need from those "Trend Lines":

I need to get my hands on the actual "data points" that make up each
Polynomial Trend Line. NOT the data points that were used to
"generate" the Polynomial line it-self. Sounds weird, I know.

There are 21 data points (or 21 rows) for each column. Each row has a
unique value and those values were used to plot the baseline chart. I
then used Excel to plot the 2nd Order Polynomial Trend Line using the
two baseline columns as the source.

I NEED TO KNOW THE 21 DATA POINTS (21 INTERVALS) THAT \"ARE\" THE 2ND
ORDER POLYNOMIAL TEND LINE - NOT THE BASELINE DATA POINTS INSIDE BOTH
COLUMNS THAT WERE USED TO \"PLOT\" THE ORIGINAL CHART LINES.

Is this possible? Excel does not show you what those actual trend line
values are. All it does is plot the trend line on screen. It does not
give you the "interval data" for the trend line that corresponds to the
baseline data inside each column and row.

I think Microsoft may have dropped the ball on this one. Having that
interval trend line data is just as important as the underlying data
that made the original chart lines.

I could "display the equation on chart", which is an Excel function,
but that only provides me with the very LAST data point - I need all 21
points.

Hope this makes sense - thanks in advance!

Regards,
HighLow

 

[Jon Peltier]

HighLow -

There are no data points making up the trendline, the trendline is a
continuous locus of the points which would lie on the fitted curve. If
you want to find some predicted Y values for particular X values, you
can extract the trendline coefficients (use LINEST, per Bernard
Liengme's approach:
http://www.stfx.ca/people/bliengme/ExcelTips/Polynomial.htm; or Dave
Braden's macro which extracts the coefficients of the trendline formula,
which google isn't giving me the link for just now). Put some X values
in a column, and worksheet formulas in the next column which use the
coefficients and the X values to predict Y values. Now you can chart the
predicted X-Y line.

- Jon

 

[Jerry W. Lewis]

David Braden's code is at

http://groups.google.com/[email protected]

It is more numerically stable than LINEST, unless you are using Excel
2003. Alternately, display the trendline on the graph and format it to
display all coefficients in scientific notation with 14 decimal places.

Jerry

 

[Jon Peltier]

Thanks, Jerry. I don't know why Google wouldn't give it up. I searched
the *Excel* groups for trendline and coefficient, author Braden. I must
be losing my touch.

- Jon