Gradient of a line

WillyNelson · 20 Jun 2014 at 11:39

I'm struggling to convert the equation of a linear trend line to a % gradient.

I've plotted two series of activity which both show an increasing trend. The trendline for series 1 equates to y=119.45x+51062 and series 2 is y=396.05x+50052.

Can anyone explain simply how I can convert these equations into a % gradient, essentially a % increase trend over the period?

Thanks

WillyNelson · 20 Jun 2014 at 12:02

Except the values are not increasing by 11,945% from the beginning of the series to the end. 100% is a 45deg upward slope so that would be an insane increase.

WillyNelson · 20 Jun 2014 at 12:06

x are months Oct 2012 through to May 2014
y values are between 40,000 and 65,000

WillyNelson · 20 Jun 2014 at 12:14

Sunbed said:
Are you sure your gradients are 119.45 and 396.05 to begin with?

It's plotted in Excel with a linear trendline. The formula for the line is from excel as per my OP.

WillyNelson · 20 Jun 2014 at 12:24

That's ok if you've got a constant increase from month to month, but I've got ups & downs. It's the long term trend that I want to get growth for.

Here's my data & the chart

WillyNelson · 20 Jun 2014 at 13:05

D D Danneh said:
% increase is just the difference between months divided by the value of the first.

I know. I'm trying to calculate the long term trend.

All this talk of sandwiches is making me hungry... Lunch.

WillyNelson · 20 Jun 2014 at 14:01

Haircut said:
What is the trend supposed to be showing here?
You haven't even got the months in date order so surely trying to get a trend line is meaningless?

The trend in increased activity.

The months are by fiscal year not calendar year, so they are correct.

Tuppy_Glossop said:
Ffs. For y=mx+c if c!=0 the gradient percentage is (m/c)*100, so y=119.45x+51062 gives (119.45/51062)*100 = 0.234%

So the trend is an increase of 0.234% per period, ie over 10 months you'd expect to have growth of 2.34%? That makes more sense.

WillyNelson · 20 Jun 2014 at 14:03

Sunbed said:
^ I don't think that's right. Looking at OP's graph, the trendline starts at about 52000 and ends at 55000. Resulting in an overall % increase of 5.77%.

This is harder than I thought and now I'm stuck. OP this thread may help:
http://www.excelforum.com/excel-general/672000-trendlines.html

By my understanding of Tuppy's method it'd be 0.234%*19months = 4.446%, which sounds about right.

WillyNelson · 20 Jun 2014 at 14:27

rubberduck said:
Ignoring the actual answer for a second, I'm not sure if you are analysing the data in a meaningful manner. Correct me if I'm wrong but aren't you just drawing a straight line between the first and last data point and saying that that is giving you the 'trend over time'. Who says there is a trend over time at all, perhaps what you are extrapolating is simply the scatter in the data?

No. It's a calculated trend line and you can see that it's not on the first & last data points but slightly below.

WillyNelson · 20 Jun 2014 at 14:40

R^2 = 0.0146

WillyNelson · 20 Jun 2014 at 14:57

rubberduck said:
This basically means you have very little confidence that the trendline can be used in a predictive fashion.

Isn't r-squared more about correlation though...? It wouldn't account for seasonality, which the data has.

Tuppy_Glossop said:
Extrapolation is a mug's game. Visual analysis suggests a sinusoid with a six month period around a constant.

Which would be about right. Underlying (mostly) constant level of activity with seasonality.

Maybe I'm using the wrong measure & something like a 6 month rolling average would be better suited.

WillyNelson · 20 Jun 2014 at 16:14

D.P. said:
You can't extrapolate a linear trend from that data, the r-value showing that a linear trend is meaningless. The visual analysis confirms this.

If you want to extrapolate future trends then you will need a much more complex model. I would start with a Fourier analysis of the data to find the periodicity.

I you are certain that the sinusoidal changes relate to seasonal changes then you could create a simpler more linear data set by collapsing all months into a single year (total or average of 12 months) and then you can compare year on year changes. The model would then be limited in only comparing 12-month periods to other 12 month periods and predicting outcomes in periods of 12 months. You will also need much more data in order to create a robust analysis.

For that reason I suggest you work on the former solution and find a better model. there are lots of ways to do it. A polynomial fit wont work well for this kind of function. If you are right about the seasonal changes then you could fit a some function of sine using a least squares fit.

What you are doing at the moment it bogus statistics and will only lead to meaningless outcomes.

I'm not looking to extrapolate data though, I just want a general trend over the periods I have to see the degree of growth/decline. I have a second set which includes additional activity over the same period & I want to compare the rate of growth between the two.

Predicting the future activity would need to account for a whole host of variables, and that's not something I either want or need to get into.

Thanks for all your help though folks, I've got what I was looking for.