469,923 Members | 1,777 Online

Calculate slope of a data set or trend line

Hello everyone,
I hope someone is out here who can help me with a simple calculation...
I have a sales data base in access with monthly sales history by
product. to make future predictions I need the slope (representing
growth) of monthly sales.
the data is setup in a crosstab with the months in different collums,
say column 1 to 12 for last year. I want to calculate the slope for
every row (on the same query or another if needed)

Does anyone have an idea how to calculate the slope of the trendline
for those values? In excel I can just use =slope(known y's, known x's).
the slope i need is from the trendline in common statistics (I believe
it is calcuated by minimizing the sum of the squared deviation - OLS)

I cannot use an ordinary excel export (too many lines).

Anyone an idea how to work around that?

Help is much appreciated,

Thanks
Kai

Mar 24 '06 #1
4 22220 In your case, if the months are m=1,2...12 and, for each record
you have the 12 observations: y(1)...y(m)...y(12),

the slope is given by:

[ sum for m = 1 to 12 of (m-6.5) * y(m) ] / 143

PS
In the particular case when y(m) = b * m for each m,
you should get slope = b where b is any real

-tom

kux ha scritto:
Hello everyone,
I hope someone is out here who can help me with a simple calculation...
I have a sales data base in access with monthly sales history by
product. to make future predictions I need the slope (representing
growth) of monthly sales.
the data is setup in a crosstab with the months in different collums,
say column 1 to 12 for last year. I want to calculate the slope for
every row (on the same query or another if needed)

Does anyone have an idea how to calculate the slope of the trendline
for those values? In excel I can just use =slope(known y's, known x's).
the slope i need is from the trendline in common statistics (I believe
it is calcuated by minimizing the sum of the squared deviation - OLS)

I cannot use an ordinary excel export (too many lines).

Anyone an idea how to work around that?

Help is much appreciated,

Thanks
Kai

Mar 25 '06 #2
Hello Tom,
thanks a lot for that formula. It works great with the data i checked.

but how do you get to 143?
I need to do similar calculations with other time frames as well (24,
36 and 48 months). I could always try it out but I would rahter enter a
formula in case someone changes the lenght of the period later...

THanks

Kai

Mar 27 '06 #3
Hi Kai, sorry for ignoring your second question, I missed it.

I hope I got it right (I have done some quick hand computation)...

In a more general case, where one has H consecutive equispaced time
occasions
y(1)...y(m)...y(H), the slope should be:

[ sum for m = 1 to H of (m - (H+1)/2) * y(m) ] / D

where: D = H * (H ^ 2 - 1) / 12
or, if you prefer: D = H * (H + 1) * (H - 1) / 12
-tommaso
PS
let me know if it works fine

Kux ha scritto:
Hello Tom,
thanks a lot for that formula. It works great with the data i checked.

but how do you get to 143?
I need to do similar calculations with other time frames as well (24,
36 and 48 months). I could always try it out but I would rahter enter a
formula in case someone changes the lenght of the period later...

THanks

Kai

Mar 27 '06 #4
Hey Tom,
to be honest, I dont understand why the formula looks like it does. but
it WORKS!

....and in the meantime I found that it is also the sum for m = 1 to 12
of (m-6.5)^2. (you can replace 12 with H and 6.5 with (H+1)/2)
That holds true at least for the values I checked. I would be surprised
if that is a coincidence but could not say why it works.

thank you so much. That completely solved my problem.

kai

Mar 27 '06 #5

 5 posts views Thread by Subrahmanyam Arya | last post: by 2 posts views Thread by BCM | last post: by 2 posts views Thread by Joe | last post: by reply views Thread by kux | last post: by 5 posts views Thread by kux | last post: by reply views Thread by bubbles | last post: by 3 posts views Thread by ellisrj | last post: by 1 post views Thread by ashwini1680 | last post: by 1 post views Thread by whnkee | last post: by reply views Thread by eddparker01 | last post: by reply views Thread by eddparker01 | last post: by reply views Thread by lanliddd | last post: by reply views Thread by Trystan | last post: by reply views Thread by Trystan | last post: by 9 posts views Thread by anoble1 | last post: by reply views Thread by WIPE | last post: by reply views Thread by MikeCant | last post: by 2 posts views Thread by Usman55 | last post: by