Compare the degree slope of trendlines

[yabahoo]

The attached file contains a simple table of employee salaries, for the years 2013 through 2016.

When I look at the bar chart of the data, I can envision a trendline for each employee. One employee's figures have been charted, and the trendline included.

What I would like is a degree slope of each employee's trendline (in a common scale), so that I could compare the rise of one to another.

For example, the degree of Peter's trendline is 10%, indicating a slower growth rate than Robert, whose trendline has a degree slope of 20%...or something like that.

I've been charting this in all manner of ways and haven't had any results that made sense, or were comparable to one another. I've had no luck with breaking apart the trendline formula (y = mx +b) using LINEST or SLOPE functions.

Any thoughts are much appreciated!

[MrShorty]

I am not understanding the difficulty.

My first thought would be to simply compare m from each regression. Larger m represents a faster growth rate. Why are you not using this simple comparison?

[yabahoo]

Thanks for the quick reply MrShorty.
When I display the trendline formulas for some of the employees, I get this:
>Robert: y = 9294.3x + 103554
>Mark: y = 8119x + 171517
>Peter: y = 2937x + 76244

Maybe I just need to know how to interpret the values 9294.3, 8119, and 2937. They don't mean anything to me. That's why I was looking for a degree: I can wrap my head around that.

As a secondary concern, is there a way to apply the solution to a much larger dataset? Would it be just about adding a column containing a (?) formula to the table?

I realize might be missing the obvious. So again, I appreciate the help.

[MrShorty]

Maybe I just need to know how to interpret the values 9294.3, 8119, and 2937. They don't mean anything to me.

Understanding "slope" is a basic part of algebra. This might be a useful tutorial geared towards a beginning algebra student: http://www.purplemath.com/modules/slopyint.htm

Once you understand that, it should be easy to see that "slope" in these regressions represents "change in salary per year"

As a secondary concern, is there a way to apply the solution to a much larger dataset?

Assuming the data are arranged similar to your sample file, I would use a LINEST() or SLOPE() function (or whatever function you find useful for performing the analysis) in a helper column and copy that formula down. If you pay attention to relative and absolute references, this should be an easy copy/paste operation once you have the formula in place in the first row.

[Richard Buttrey]

A linear growth line equation is of the form

y=mx + b

where m is the slope Mr shorty mentioned and b is a constant. If you right click the trend line you'll be offered the choice of displaying it.
Or you can just use the SLOPE or LINEST function

m:

Please Login or Register  to view this content.

which returns the value 9294.3

So for the 1st year x = 9294.3 * 1 + 103554 = 112843
Y2 : 9294.3 * 2 + 103554 = 122143
Y3: 9294.3 * 3 + 103554 = 131437
....etc.

b is given by

Please Login or Register  to view this content.

[yabahoo]

Thanks MS and RB. I am straight now on the meaning of the slope value. It also took me a bit to work through that the different values for b did not affect the slope.

Long removed from my algebra days.

Is it even a valid question to consider if the slope can be expressed as a percentage degree, with no growth equating to 0%?

Thanks again for helping me think this through!

[shg]

Slope is rise (delta y) over run (delta x).

Slope in degrees is =degrees(atan(slope)); so a slope of 1 is 45 degrees. I don't think that's a very useful measure in most applications.

For something increasing at a linear rate, you can't speak of percentage increase; that's a characteristic of an exponential function, not a linear one.

[yabahoo]

Thanks shg. Yea, I certainly didn't get anything "useful" when calculating the slope in degrees using the formula you provided: getting ≈ 89.9_ for every record, which doesn't provide any significant differentiation.

Thanks for ringing in on the topic!

[yabahoo]

I'll mark this thread as solved. Thanks again everyone!

Have a good weekend.

[shg]

Cheers .