Problem with trendline formula

[Dom]

I have added a trendline to a line chart where I am
plotting a value y axis against a date series x axis and
have shown the trendline equation on the chart.

The only trouble is - it is wrong. When I calculate the
formula (so as to be able to forecast future periods) the
values clearly are not those that are being displayed on
the chart.

I have tried doing the same using no x axis (i.e.
defaults to 1,2,3,4 etc.) rather than using my dates, and
this produces a correct trend equation.

Am I goin mad - or is there a problem with Excel?

I am working in Excel 2000, and am using the following
data:
Value
24/03/2004 102.0584
25/03/2004 102.512
26/03/2004 102.512
27/03/2004 #N/A
28/03/2004 101.1513
29/03/2004 #N/A
30/03/2004 102.0584
31/03/2004 102.0584
01/04/2004 #N/A
02/04/2004 100.6977
03/04/2004 101.6048
04/04/2004 102.512
05/04/2004 101.6048
06/04/2004 102.0584
07/04/2004 101.1513
08/04/2004 102.0584
09/04/2004 #N/A
10/04/2004 #N/A
11/04/2004 #N/A
12/04/2004 102.9656
13/04/2004 #N/A
14/04/2004 100.6977
15/04/2004 101.6048
16/04/2004 #N/A
17/04/2004 102.0584
18/04/2004 101.1513
19/04/2004 #N/A
20/04/2004 100.6977
21/04/2004 #N/A
22/04/2004 #N/A
23/04/2004 99.3369
24/04/2004 100.2441
25/04/2004 #N/A
26/04/2004 100.6977
27/04/2004 100.2441
28/04/2004 #N/A
29/04/2004 99.7905

The trendline equation added on my chart is y=-
0.059x+2347.1.

On the chart - the initial point for the trendline looks
like it is around 102.5. Calculating from the equation
on the first point for 38070 (24/3/4) gives 100.97.

Any help greatly appreciated.

Thanks
Dominic

 

[Jerry W. Lewis]

Use an "XY (Scatter)" chart. Despite its misleading name, Line chat has
nothing to do with whether you want a line or not. The definition of a
Line chart is that Y is charted against x values of 1,2,3,... regardless
of the labels you may optionally apply to those values.

Jerry

 

[Jerry W. Lewis]

On closer examination of your data, all your dates increment by 1, so
you get equivalent fits. The issue in your case is that you are using
heavily rounded slope/intercept values. Three possible approaches are

- Right click on the trendline equation and format as Scientific with
14 decimal places

- Use David Braden's VBA code to extract the coefficients directly
from the chart into cells
http://groups.google.com/[email protected]

- Use the worksheet functions SLOPE and INTERCEPT. You will have to
set them up as array formulas (Ctrl-Shift-Enter) to ignore the #N/A values
=SLOPE(IF(ISNUMBER(ydata),ydata),xdata)

Jerry
Excel MVP