Challenge level: 4 out of 5
Estimated reading time: 20 min
Excel functions covered: SUM(), OFFSET(), MATCH() and IF()
Excel attachment reference sheets: template_Excel
Step 1. Calculate Rolling Last-12-Months (L12M) Total
The first complex calculation we are going to tackle here is the total rolling last 12 months’ figure.
The Excel formula for this consists of embedded OFFSET, MATCH and IF functions within a SUM function.
Phew, we know! Let’s break down the formula further. Let’s look at our ROWS field.
Step 1.1. Start at the right row
First, you would need to define the latest month you want to base your calculations off. In this example, we have chosen “200806” from the drop down list in cell G3.
To determine the correct row that Excel should start from to sum up figures, the MATCH function helps return the correct position for this. You will need to subtract 1 from the returned position value to exclude the header row (i.e. the row listing the words “category” and the different months ).
Step 1.2. Start at the right column
To determine the correct column that Excel should start from to sum up figures, the MATCH function helps return the correct position for this. You will need to subtract 1 from the returned position value to exclude the category column.
Step 1.3. Narrow down the number of rows to sum
With the optional HEIGHT field at 1, the selected data range to be summed gets narrowed down to $AB$9:$BL$9.
Step 1.4. Define the number of columns to sum
As the final step, we need to define the correct number of columns to sum. We utilise the IF function here to account for situations when there aren’t 12 months to sum up.
We also need to reflect a negative figure for the correct number of columns to be summed as we are counting backwards to consolidate past months values from the latest YYYYMM.
Step 2. Calculate Last-12-Months Average (L12M_Average) figures
The IF function is used here again to factor in situations where there are less than 12 months to sum up and hence to divide against to get an accurate average figure.
With this base understanding of how the L12M formula works, we push further and explore how slight tweaks to the formula can easily give us other important figures like Year-to-Date (YTD) and Financial Year-to-Date (FYTD). But that's a topic for tomorrow!
This is the second article in a 5-part introduction tutorial that juxtaposes Excel formula with Power Query and Power Pivot. Here are the other parts if you missed them: