Rolling/Sliding window of data

0

Is there a way to create a table that will automatically update as a new month is added and the oldest month is taken away?  The primary reason for this is to have a rolling one year average (including the current month and previous eleven) in each monthly cell of a table.

I was under the misconception that I could lay out the months by row instead of column format, transpose those values into a table with the months in columns (using column$row absolute addressing).  And then, when adding a month to the bottom row of the first layout and removing the the first row, the table in the second layout would automatically update.  Unfortunately in this case, the references get updated.  The "$" addressing apparently only applies to the fill/replicate process.

I have attached the test file with which I am working.  The twelve month average is "Recpt Avg."  The first format mentioned above is at the bottom and the second format is at the top.

Answer
Discuss

Answers

0

Consider that =LOOKUP(2,1/(A:A<>""),ROW(A:A)) returns the number of the last used row in column A. Therefore, LOOKUP(2,1/(A:A<>""),ROW(A:A))-11 returns the first of a 12-months' period ending in the last row. Therefore the formula below would return the last 12 months recorded on the sheet, depending upon the value of "11".

=INDEX(A:A,LOOKUP(2,1/(A:A<>""),ROW(A:A))-11)

Working from there toward a solution consider replacing your data table in row 71 with a proper Excel table. By default its name will be Table1 but I changed that to Tbl because it has less characters. Use Table Tools > Design or the Name Manager to change the name.

Now I can use =ROWS(Tbl) to determine the last row and, accordingly, ROWS(Tbl)-11 the rolling year. The formula below will have the same effect as the one above. It's not only much shorter but also eats less computing power.

=INDEX(Tbl[MMM-YY],ROWS(Tbl)-11)

In the folowing, I will now show you how to create a copy of your rolling year table (A5:M12) starting from cell I17. Start by understanding the ROW() function. It returns the number of the row which it is in. Accordingly, =ROW() has a value of 18 when entered in row 18 and changes that value to 19 when copied down. Therefore it is a simple way of creating consecutive numbers. The COLUMN() function works the same way horizontally. This is important when you think of transposing data. Enter this formula in I18.

=INDEX(Tbl[#Headers],ROW()-16)

Tbl[#Headers] identifies the range of the table's headers (predictably so lol). You want the value in the second column ("Receipts") which is 2, of course, but you want to express this as a ROW() number so that you can copy it down. This is where the 16 comes in. ROW(), being in row 18, returns 18. 18-16 = 2. Now copy the formula down to I24 and you get a neat listing of the table headers.

Now we take the formula =INDEX(Tbl[MMM-YY],ROWS(Tbl)-11) already developed above. It already returns the correct result for J17. However, we want the 11 to become a dynamic counter, changing with the column. We might replace it with COLUMN()-1. In column J COLUMN() returns 10, in context -10, which we need to increase it by another 1. But when we copy this formula to the right the deduction increases whereas we need it to decrease. 

12-COLUMN() reverses the counter. 12 - 10 (for column J) = 2. Add another 9 to make up the 11 we need: 12-COLUMN()+9 = 21-COLUMN(). I created a positive number intentionally. Surround the expression by parentheses to improve its readability. -(21-COLUMN()). The formula below goes to J17. Copy from there to the right. Note that the 12 is an arbitrary number I chose because you want 12 months. However, if the number is smaller than the COLUM() a negative column number will result which Excel can't use. If this happens to you, increase the number 12 to any number which will be larger than the largest COLUMN() value expected.

=INDEX(Tbl[MMM-YY],ROWS(Tbl)-(21-COLUMN()))

The rest of the table follows the same pattern. Just the column Tbl[MMM-YY] needs to become dynamic. for that we use =MATCH("Receipts",Tbl[#Headers],0) and, since "Receipts" must also be variable, we arrive at =MATCH($I18,Tbl[#Headers],0) to specify the column in the INDEX function below.

=INDEX(Tbl,ROWS(Tbl)-(21-COLUMN()),MATCH($I18,Tbl[#Headers],0))

The location of the MATCH function in the formula seems reversed but it isn't. The INDEX function specifies the cell by Row and Column, in that sequence. In the formula =INDEX(Tbl[MMM-YY],ROWS(Tbl)-(21-COLUMN())) the column number is omitted because the range only has a single vector. However, because the vector is, in fact, a row the "row number" in the formula turns out to be a column number in effect.

Anyway, enter the above formula in J18 and copy to J18:U24. The list isn't the same as what you have in row 4. To achieve that result, delete rows 95:98 which demonstrates that the table changes automatically to comprise the last 12 months in the table at the bottom. I attach a copy of your file with the above changes implemented.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login