Search code examples
excelfinance

How do I calculate the average slope of an array in Excel?

I have a series of returns in a column of cells and would like to calculate the average slope calculated from the differences between each return value. 

Returns:          
0.05              
0.06 
0.04 
0.07 
0.05 
0.06

I do not want to create a row for the differences because I have more columns of return streams beside the one above. I'm looking for a non-vba solution.

The answer I'm looking for is shown below:

Required Value: = Sum of slopes/No. of periods
Ans: -0.001666666

Cheers!

Solution

  • At risk of being controversial:-

    =(A7-A2)/COUNT(A3:A7)

    because (A7-A6)+(A6-A5)...+(A3-A2) = A7-A2 and because there are only 5 differences between the 6 numbers, so my answer is 0.002..