polynomial trendline values in a spreadsheet

  • Thread starter Thread starter Dave Broer
  • Start date Start date
D

Dave Broer

Hello,

I have about 20 years worth of temperature data that I'm trying to
devise the smoothed normal values from. For example, the average
temperature for the past 20 July 4th's is 80, July 5th 81, July 6th 79,
etc. I have the data ploted nicely on a XY Scatter chart. I also have
a polynomial trendline (order 6) plotted through the data and it
perfectly smooths the data.

However, I can only estimate the values for a given day and I would like
to have the value that the trendline is ploting as a value in a cell. I
can put the formula on the chart, but not much else. I know that the
formula has to be deriving a value somehow and plotting it on graph.

Is there someone here who might know how I can do this? I can provide
example data if needed.

Thanks for any and all assistance!

Dave.
 
Hi Dave

After you plot the graph and set up the poly trend line if you select
OPTIONS on the Format Trend line menu and then select "Display equation on
chart" and "Display R^2 value on chart"
Your trend line Poly will appear with the correlation coefficient.
You could then rework the equation in terms of y or x as desired.

Does this help ?

Best

J
 
If you display the equation on the chart, by default the coefficients
will be so heavily rounded as to be useless for reproducing the graph.
There are several approaches:

- Right click on the equation displayed on the graph and reformat to
display scientific notation with 14 decimal places.

- Use David Braden's code to extract chart coefficients
http://groups.google.com/[email protected]

- Use the LINEST worksheet function. The first paragraph of Help for
LINEST is misleading, LINEST is not restricted to straight lines -- it
will fit any function (including polynomials) that are linear in the
unknowns
=LINEST(y_data,x_data^{1,2,3,4,5,6})
fits a 6th order polynomial, assuming that the data are in columns. If
they are in rows, then use
=LINEST(y_data,x_data^{1;2;3;4;5;6})

For Excel versions prior to 2003, the chart options are potentially much
more accurate than LINEST. The algorithm for pre-2003 LINEST is
mathematically exact, but can numerically unstable. That means that
with challenging data (easily obtained with higher order polynomials) it
can waste most or even all of the available computational precision by
subtracting two numbers that are nearly equal.

Jerry
 
Back
Top