Trendline Equations in Excel 2003

[tep]

Using copy and paste I transferred the trendline equation (in this case a
quadratic) from a chart element to a worksheet cell and then subjected it the
original data to validate it. The resulting values are magnitudes different
to the orignal data - in the millions, not explained by regression tolerance,
RHO of 0.998. The x axis values were months - what value does Excel assign to
months in Charts because the 1900 system values (eg 39643 for 14 Jul 2008) do
not appear to be ones used?

 

[Ed Ferrero]

Hi tep,

What Chart Type did you use?

If Line, change it to (x-y)Scatter to get the right result.

Also, how many points in your data? An R-squared of 0.998 may not mean much
if you do not have sufficient data points.

Ed Ferrero
www.edferrero.com

 

[tep]

Thanks Ed Ferrero,
I tried the scatter plot but still no resemblance to the original data.
Here's what I'm using:
Jun08 Jul08 Aug08 Sep08 Oct08 Nov08 Dec08 Jan09 20.00 35.00 50.70
67.00 77.00 86.00 93.00 95.00
Here's the scatter trend equation that Excel presents:

y = -0.0012x2 + 96.719x - 2E+06

Here's the results from this equation:

Jun08 Jul08 Aug08 Sep08 Oct08 Nov08 Dec08 Jan09
-51,720 -51,670 -51,622 -51,575 -51,533 -51,491 -51,453 -51,415

Differences of 50,000 plus.

Here's the Line equation: y = -1.0855x2 + 2843.3x - 2E+06

Here's some of the result for Jun08: -1,591,643,000.00. The results for the
other month's are similar.

Huge differences.

Your interest in helping me solve or correct what I'm doing is much
appreciated.
Regards,
TEP.

 

[Del Cotter]

I tried the scatter plot but still no resemblance to the original data.
Here's what I'm using:
Jun08 Jul08 Aug08 Sep08 Oct08 Nov08 Dec08 Jan09 20.00 35.00 50.70
67.00 77.00 86.00 93.00 95.00
Here's the scatter trend equation that Excel presents:

y = -0.0012x2 + 96.719x - 2E+06

Here's the results from this equation:

Jun08 Jul08 Aug08 Sep08 Oct08 Nov08 Dec08 Jan09
-51,720 -51,670 -51,622 -51,575 -51,533 -51,491 -51,453 -51,415

Small inaccuracies in the displayed numbers will lead to large
inaccuracies in the calculated result. Display the trendline equation to
an accuracy of 14 digits and use those numbers. I just tried this for
you and it works.

Or, abandon the use of Microsoft date format and number the months !, 2,
3 etc. then get a trendline from those. This wil require far fewer
digits to work accurately.

Or, don't use Microsoft Excel charts as an analytic tool when you have a
spreadsheet right there to do the calculating in.

 

[MartinW]

Hi Tep,

That equation is highly rounded and will give you very large errors
if used in the way you explain. You could increase the decimal places
to get something a lot closer or you can use worksheet functions to
calculate the equations separately in the spreadsheet.

Take a look at this site.
http://j-walk.com/ss///excel/tips/tip101.htm

HTH
Martin

 

[Jon Peltier]

If you used months as the base unit of a date scale X axis, Excel uses the
number of months since January 1900. If the X axis is a category type, Excel
uses 1 for the first category, 2 for the second, etc. If you want to use
dates, use real dates as the X values, and use a base unit of days, not
months (your display can show the months by using months for major and minor
units). This will give you as good accuracy as using an XY chart with dates
as the X value.

Your trendline formula does not display many digits. Use a scientific number
format with lots of digits, or as someone else has suggested, carry out the
calculations in the worksheet using LINEST.

- Jon

 

[Jerry W. Lewis]

Either the data are not exactly as you reported (dates not the 1st of each
month or more figures in the y-values) or you miscopied the fitted equation,
since your linear coefficient differs in the 3rd figure from what the chart
trendline gives for your posted data.

You cannot accurately use the trendline equation for large x values (such as
dates) unless you use unrounded coefficients as Jon suggested.

The chart trendline (from an XY catter chart) coefficients agree with LINEST
to 14 figures and agree with exact coefficients to at least 13 figures, which
gives at least 10-figure accuracy on predicted values. If that accuracy is
not satisfactory, subtracting 39707 (16Sep08) from each date will reduce the
condition number from 3E29 to 1E8, so that far less accuracy will be lost to
finite precision arithmetic.

Jerry

 

[tep]

Thank you all for your advice.
I will now also attempt to use the functions within Excel to derive the
equation.