trendline equation

[excel2000]

I am trying to do a regression analysis on some data in
Excel 2000. I am trying to get my x-intercept, which is a
date. I have plotted the trendline and displayed the
equation and R2 value. Backing out the x-intercept value
gives me a date in 1993. This is weird because the line
obviously crosses the graph somewhere around 1975. We have
discovered that the equation for the line ( y=2E-05x-
0.6805) is not precise enough. The slope is so small that
this can give us an error of up to ~50 years. Is there
anyway that we can get excel to give us a more precise
equation for the trendline.

It is important that we leave the date on the x-axis and
it is important for us to have this equation.

Thanks for any help.

 

[Dan E]

Two options.

Select the label that contains your trendline equation
Right click and select "Format Data Labels..."
Under the number tab change the format of the numbers
to display more precision.

Use the LINEST worksheet function to populate cells
with the slope and intercept.

Ex.
X data in A1:A20
Y data in B1:B20
Select cells C11
In the formula bar enter =LINEST(B2:B20,A2:A20)
array enter the formula (control + shift + enter)
If done properly your formula will appear as
{=LINEST(B2:B20,A2:A20)}
C1 will contain the slope
D1 will contain the intercept

Dan E

 

[Jerry W. Lewis]

LINEST in versions prior to 2003 can have numerical problems. Since the
OP is doing simple linear regression, the alternate numerically stable
formulas at

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

would be a better solution.

Jerry