Prev: Web scraping
Next: MGRS to Lat/Long
From: Misha on 1 Feb 2006 16:34
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!

From: MrShorty on 1 Feb 2006 18:42

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.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=22181
View this thread: http://www.excelforum.com/showthread.php?threadid=507461

From: Mike Middleton on 1 Feb 2006 19:00
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" <misha.cheung(a)gmail.com> wrote in message
news:1138829675.322163.211180(a)g14g2000cwa.googlegroups.com...
> 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!
>


 | 
Pages: 1
Prev: Web scraping
Next: MGRS to Lat/Long