Excel Slope Function

[LGMan]

Have taken over a piece of work that uses the Slope function, to work out trends from 12 months of data. The original owner of the workbook has the following

Cell B1 through to N1 hold the months of the fiscal year
while Cells B2 through to N2 hold the Average Daily Sales per month.

The guy that created the workbook uses a slope function which he multiplies by 11 so =slope(range,range)*11
I'm told because there are 11 steps between the start and end months.
I'm new to Slope so have 'googled' a few examples, but none of them mention having to multiple the result so am a little confused.

Any help gratefully received.

 

[mintjulep]

I just played around with some very simple sets of data points and the results of the slope function are what you would expect from junior high school geometry class.

So either the guy who wrote the spreadsheet didn't know what he was doing, or you don't understand what he was trying to do yet.

 

[combo]

It depends on the question. In case of monthly trend there is no need to multiply by anything, as above. If someone needs to calculate yy trend, the monthly result from SLOPE function should be adjusted, but the factor should be 12.

combo

 

[mintjulep]

The slope function returns the slope of a regression LINE through the point pairs. A line has only one slope.

If you were calculating the slope of the line between each adjacent set of points then you would need to average those and multiply by 11. But I'm not sure that calculation has any useful meaning.

 

[combo]

A line has only one slope

Yes, but in this case it is measured in $$/month (I assumed that the input for x-axis is 1, 2,...). If you rescale it:
[slope in $$/year]=12*[slope in $$/month].

For me the '11' factor is wrong. If the sales are concentrated in the middle of the months, the Jan.-Dec. distance is 11, but the growth is calculated between 11 months too, so still the monthly slope is calculated and the rescalling factor is 12 (or I miss something, there are 13 columns between 'B' and 'N').

combo

 

[lionelhill]

To be honest, the whole calculation is a little dubious in its interpretation. I assume it's supposed to give you an idea about whether sales are going up or down. Given that the sales of most things vary according to an annual pattern, the slope of a line calculated yearly actually only tells you how the year looks. If you always calculate Jan - Dec, and you have a Christmas peak in sales, the slope will always be positive, but this doesn't mean that sales are always increasing. In fact sales this year could be much worse than last year, but the slope could still be just the same.

Given that this is what you're doing, all you really have is a metric. If you change the way it is calculated, you make it less easy to compare to whatever was going on in previous years. It might be better to accept it's a bit of a weird metric and keep the definition the same, for backward compatibility.

 

[LGMan]

Hi fat fingers I'm affraid! your'e correct range is B:M rather than B:N

Probable easier to show you an example

  A        B       C	     D	    E   	F	    G	    H	    I	    J	    K	    L	    M
Slope	  1	   2	     3	    4	   5	    6	    7	    8	    9        10	   11	   12
15.11	21.56	25.85	14.21	21.52	30.14	45.88	35.96	38.01	34.53	28.72	27.09	39.87

Sorry don't know how to get the real image of my excel sheet into view, so the top row are the columns A : M, row 2 are the month numbers B1:M1, Row 3 A2 is the slope answer of 15.11 while B2:M2 are the monthly values.
The formula in cell A2 is =SLOPE(B2:M2,$B$1:$M$1)*11
If we're looking for the start of year to end of year result, is the formula correct?

Thanks for bearing with me on this one!

 

[mintjulep]

Yes, but in this case it is measured in $$/month (I assumed that the input for x-axis is 1, 2,...). If you rescale it:
[slope in $$/year]=12*[slope in $$/month]

Uh, no.

The slope of a line is the same as any segment of the line.

If you take the slope of the lines between adjacent point pairs of a thing that is not a line then the slope of the regression fit line through the points is still not the number of segments multiplied by the slope of any given segment.

 

[combo]

It's not a problem of combination of line segments.

In this example result of SLOPE is a number, that should be interpreted as growth in [$/month]. It's calculated with months as units of x-axis. How, basing on this figure, express it in [$/year]?

combo

 

[mintjulep]

The Excel Slope() function returns the slope of the best fit regression line through the points provided as arguments.

If you want the growth of $/month for any month then give it only 2 points the work on. Last month and this month.

If you want the overall growth of $/month on a yearly basis then give it 12 points, the figures for each month. This will return an "average" for the whole year. Multiplying by anything is simply wrong.