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

"Misha" <[email protected]> wrote in message
news:[email protected]...
> 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!
>