Here's a formula:
=(D4+D5+D6)/3
Look familiar? Next month it'll need updating to:
=(D5+D6+D7)/3
These formulas and their derivative "=AVERAGE(D5:D7)" live across many a pricer, snuggly sitting below the months which they aggregate. The nice thing about them is how simple and clear they are. Easy to understand and simple to update, with one major drawback: you have to update lots of them every month with ample opportunity for mistakes.
If you have to manage lots of products in your layout you'll appreciate anything that reduces the steps you need to take to roll your sheet. Less steps means less errors, less errors means less mental energy spent on a menial task. However, less steps also means some form of automation, and automation carries a cost in the form of added complexity in your spreadsheet. For people strapped for time and attention this is a trade that's absolutely worth making.
In this post I'll show you how to setup your aggregates so you never need to update another quarter formula again. No macros required.
You'll be replacing the above with a formula that looks like this:
=AVERAGE(INDEX(MonthValues, $A27), INDEX(MonthValues, $A27+1), INDEX(MonthValues, $A27+2)
To achieve our aggregation nirvana we need to have the following 3 things:
Rolling quarter labels (optional)
The row containing the first month in the target quarter
A formula to calculate our aggregate value
Excel is a powerful tool and gives you hundred and one ways to do the same thing. While not strictly needed to achieve our result, we'll be using named ranges to make our formulas more readable. If you're unfamiliar with named ranges check out this excellent article by ExcelJet.
Whether you're using consecutive or interleaved flat/spread periods, this approach will work with almost any layout. Here's the layout we'll use to build our rolling quarters around. A familiar forward curve with manual marks for the front-month and time-spreads calculating the rest of the curve. The quarters are at the bottom and will use the month values above to generate our aggregates.
Before we get started, we need to configure our named ranges. These formulas can be placed in your sheet and referenced in the standard way, but we're sticking them directly into named ranges for convenience.
From the ribbon, open up Formulas -> Name Manager
and add the following named ranges:
| Name | Refers to | Note |
|---|---|---|
| CurrentDate | =TODAY() | For our purposes we're using today's date. Use this if you want your sheet to auto-roll. If you need manual control you'll want to reference a cell with a manually managed date; make sure you use anchors, e.g. $A$1 |
| CurrentMonth | =MONTH(CurrentDate) | Returns current month as integer, based on CurrentDate. |
| CurrentQuarter | =IF(OR(CurrentMonth={11,12,1}), 1,IF(OR(CurrentMonth={2,3,4}), 2,IF(OR(CurrentMonth={5,6,7}), 3,IF(OR(CurrentMonth={8,9,10}), 4,"#err")))) | Gets current quarter as integer (e.g. 1, 2, 3, or 4). This is based on CurrentMonth |
| QuarterLabels | ={"Q1","Q2","Q3","Q4"} | |
| QuarterMonths | ={1,4,7,10} | Used in lookup |
| QuarterMonthsStr | ={"Jan","Apr","Jul","Oct"} | Used in lookup |
| QuarterYearOffset | =IF(OR(CurrentMonth={11,12}),1,0) | Offset used to correct the year for Q1 after Q4 rolls off |
| MonthLabels | =$C$5:$C$26 | Range containing our month labels; this is next to our values |
| MonthValues | =D$5:D$26 | One column range with all of our month values; anchor the rows, not the column. This will ensure the same named range works across multiple columns. |
This part is optional, but it sure does make things cosy when your quarter labels just roll like your months do. We'll need two formulas, and a number of named ranges we defined above to achieve this.
The cell with your first quarter label will act as an anchor for all other cells to follow. We've already done the heavy lifting on working out the current quarter in our named ranges.
This is the formula you'll need (cell C27 in the screenshot):
="Q" & CurrentQuarter & "-" & (TEXT(CurrentDate, "YY") + QuarterYearOffset)
The next cell down (C28) will just increment from this cell using this formula (change C27 to your reference accordingly):
="Q" & IF(INT(RIGHT(LEFT(C27, 2), 1))=4, 1, RIGHT(LEFT(C27, 2), 1) + 1) & "-" & IF(INT(RIGHT(LEFT(C27, 2), 1))=4, RIGHT(C27, 2) + 1, RIGHT(C27, 2))
You can then fill this formula down to get the remaining quarters:
You now have rolling quarter labels!
To get our rolling aggregate formulas working, we will use MATCH with the quarter labels in column C, the QuarterMonthsStr and QuarterLabels named ranges, and the months range in column C; $C$5:$C$26.
You can put the follow formula anywhere, though I would recommend placing on the same row as the quarter you're looking up. Here's the formula:
=MATCH(INDEX(QuarterMonthsStr, MATCH(LEFT(C27, 2), QuarterLabels, 0)) & "-" & RIGHT(C27, 2), MonthLabels, 0)
Fill it down:
We're one step away from our rolling quarter aggregates.
To calculate our quarter values, we simply take the row offset get got from our previous step (column A), and use INDEX to get our month values. Out values live in the range D$5:D$26. We've already created a named range for this called "MonthValues".
=IFERROR(AVERAGE(INDEX(MonthValues, $A27), INDEX(MonthValues, $A27+1), INDEX(MonthValues, $A27+2)), "")
We wrap the whole thing in IFERROR to keep our sheet clean in case values are missing, or the month isn't available.
This formula can then be filled down and across.
That's it! You're quarters will now track your months correctly, and you won't have to manually roll them every again**.
**until you make some catastrophic changes to your spreadsheet that is ...