intercept graph vs. function

[Misha]

Hi:

I have a small data set, and I'm trying to do make some forecasting
functions work, so I use the INTERCEPT() and SLOPE() function in order
to get the line's major elements. BUT! When I double check the
INTERCEPT() and SLOPE() results with the Trendline that Excel has done
using the data set, I get completely different values for the
INTERCEPT!

Any advice out there on how to handle this?? My data set follows

2007 2008 2009
2010
A 195,734.53 211,970.79 229,935.20 249,846.68
B 57,893.65 66,527.44 76,613.14 88,412.49

So the equation from the graph/trendline function: A: y = 18030x +
176797; and B:
y = 10164x + 46951..

The SLOPE() function is fine and I get matching values, but my
INTERCEPT() values are way off: -35,991,552.05 for A; -20,342,474.73
for B.

Thanks!

 

[MrShorty]

The devil is in the details, and you've left out one important detail:
what are you using for known_x? The intercept, of course, is the value
of y when x=0.

If I use "2007, 2008, 2009, 2010" for known_x, I get the same from
INTERCEPT as your worksheet got. In this case, known_x refers to
"years since 1 BC" (ie 1 BC is year 0).

If I use "1, 2, 3, 4" for known_x, I get the same from INTERCEPT as
your chart trendline gave. In this case, known_x refers to "years
since 2006" (ie 2006 is year 0).

As long as you're consistent in your use of independent variable,
neither is necessarily more correct than the other.

 

[Mike Middleton]

Misha -

If you have a Line chart type, Excel uses 1,2,3,... for the X values of the
trendline.

If you create an XY (Scatter) chart, you should obtain identical results for
the linear trendline and the worksheet functions.

- Mike
www.mikemiddleton.com