Using INDEX to help you dynamically calculate a moving average
Take for example a situation like below where you have a list of actuals by month (I haven’t bothered to show the months) and you want to compare against meaningful averages. A good analyst won’t want to just statically report against say a fixed 12 months. He or she knows that recent events may mean comparing to the last quarter is more revealing because there has been say a sustained growth in business level and a quarterly average is more important than an annual average. Or they might just know their boss might ask for anything so why not build the formula flexible enough for any average!
So in the below example K1 has a cell to set the rolling average period in months to be used. The formulas then in D3:D31 show a calculation for an average based on what the user enters in K1
=IF(COUNT(C$3:C3)<$K$1,”< “&$K$1&” mths history”,SUM(INDEX(C$3:C3,COUNT(C$3:C3)-$K$1+1,1):C3)/$K$1)
So this part of the formula SUM(INDEX(C$3:C3,COUNT(C$3:C3)-$K$1+1,1):C3)/$K$1) uses SUM to dynamically figure out where is the cell positioned in the list and what is the SUM of this cell back X prior months (to the number chosen by the user in k1), then its simple averaged.
The first part of the formula IF(COUNT(C$3:C3)<$K$1,”< “&$K$1&” mths history”, is simply handling the fact that once you go back to the beginning of a list you can run out of cells to use in the average. You might handle this differently and allow an average for less months than chosen by the use in K1 where the formula runs out of data.