This tip is one of the most challenging Tip I’ve shared but it’s worth the effort to learn.
The index function has a curious, hidden property that’s extremely handy. With this feature you can dynamically enter one row data to another one easily. If you put the index function into a range reference it returns a valid cell address, which means you can build dynamic ranges of data.
Let’s see an example.
Suppose we have a column of values — perhaps it’s recent transaction values or new leads each day — and we want to compute a rolling 7-day average.
Double Index Trick
We start by using this double index trick to extract the last 7 values from the list.
Whenever new data is added, the double index formula includes the new data and “rolls” down the range.
Here’s the formula to extract the last 7 values from column A:
In the image you can see that the double index formula in cell C2 has returned the last 7 values from column A. If we add additional data to column A then the formula will update to show the last 7 values.
(You can change the number 6 to something else if you want a different period.)
Can you see how it works?
The COUNTA() simply counts how many values we have in column A.
The INDEX(A2:A,COUNTA(A2:A)) returns the value at the position we specify. The first INDEX gets the value 7th from the bottom and the second index gets the bottom value.
However, per the index trick above, when we put an INDEX function next to a colon “:” in a range reference it returns the cell address instead of the value! Wow!!
So the two index functions return cell address A10 and A16 respectively, instead of the values in those cells! This gives the range reference A10:A16 which returns the 7 values for us.
This formula is equivalent to saying:
Except that the double index keeps “rolling” with the data to get the last 7 values as new data is added.
The double index formula can be used to create a rolling average, by wrapping this double index with a standard AVERAGE function:
Congratulations, that was a tough lesson! It can make your daily work easy.