# [2/5] How to calculate Last 12 Months (L12M) Total and L12M Average figures

• 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

BEFORE YOU START: Get your FREE Excel template that accompanies this five-part tutorial by subscribing here!

## 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:

Comment