Best way to calculate rolling 6 months average

PCRIDE

Well-known Member
Joined
Jan 20, 2008
Messages
902
Hi, I have a graphing maxtrix tab that does all my calculations for my dashboard. The tab has the month names as the horizontal header

Jan, Feb, March etc..

For each column /Month I have assigned a month number reference as well (not an actual month number) but a number position. ( I use a large look up table for the next 2 years)

1 Jan 2019
2 Feb 2019
...
...
13 Jan 2020
14 Feb 2020
...
...
25 Jan 2021

The idea here is to use the month index number to do a calculate for rolling 6 months. Since my columns will expand as the pivot table it references grows each month, nothing can be hard coded. For example the Month June will be in column Y, then next month it will be in column Z.

Is there a formula I can use that will use the reference index number range and calculate the month averages in that range?

You can see in the screen shot the average I am doing in AG20, then I'll need that formula to shift down next month, so it grabs the next 6 months. Does that make sense? As you can see I have a series of look up month/year tables, so adding more is not an issue.

https://imgur.com/a/0iCdT1f
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
It doesn't make sense to me. Suggest,

A total re-design. Have normalised data and then query it for the result set you need. Have the query select the data (6 months criteria included) that is needed.

So if a pivot table/chart, or cross tab query, it can get the last 6 months without any helper columns or large lookup tables. Something like
SELECT fields
FROM data
WHERE YEAR(date_field) + MONTH(date_field)/100 BETWEEN YEAR(Date) + (MONTH(Date)-6)/100 AND YEAR(Date) + MONTH(Date)/100

that date filter just to give the idea (I HAVE NOT TESTED THIS: it is just to indicate that somehow the particular date range can be defined in the filter), you'd likely have to fine tune it for full months which is easy enough.
 
Last edited:
Upvote 0
Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:30.42px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /><col style="width:57.98px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >Q</td><td >R</td><td >S</td><td >T</td><td >U</td><td >V</td><td >W</td><td >X</td><td >Y</td><td >Z</td><td >AA</td><td >AB</td><td >AC</td><td >AD</td><td >AE</td><td >AF</td><td >AG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td style="text-align:right; ">nov-18</td><td style="text-align:right; ">dic-18</td><td style="text-align:right; ">ene-19</td><td style="text-align:right; ">feb-19</td><td style="text-align:right; ">mar-19</td><td style="text-align:right; ">abr-19</td><td style="text-align:right; ">may-19</td><td style="text-align:right; ">jun-19</td><td style="text-align:right; ">jul-19</td><td style="text-align:right; ">ago-19</td><td style="text-align:right; ">sep-19</td><td style="text-align:right; ">oct-19</td><td style="text-align:right; ">nov-19</td><td style="text-align:right; ">dic-19</td><td style="text-align:right; ">ene-20</td><td style="text-align:right; ">feb-20</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td style="text-align:right; ">18,750</td><td style="text-align:right; ">18,850</td><td style="text-align:right; ">18,950</td><td style="text-align:right; ">19,050</td><td style="text-align:right; ">19,150</td><td style="text-align:right; ">19,250</td><td style="text-align:right; ">19,350</td><td style="text-align:right; ">19,450</td><td style="text-align:right; ">15,000</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="background-color:#92d050; text-align:right; ">19,200</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >AG20</td><td >=AVERAGEIFS(Q20:AF20,Q19:AF19,">="&DATE(YEAR(TODAY()),MONTH(TODAY())-6,1),Q19:AF19,"<="&DATE(YEAR(TODAY()),MONTH(TODAY())-1,1))</td></tr></table></td></tr></table>

The dates in row 19 must be dates of this type:
1/Jan/2019, 1/Feb/2019, 1/Mar/2019, etc.
In the cell format you can put them as:
Jan 2019, Feb 2019, Mar 2019, etc.
 
Upvote 0
That pseudo idea I detailed above won't work - because of the 12 month calendar & me using simple decimal arithmetic. Instead consider something like below - modify to suit any end of month requirements. Again untested

WHERE YEAR(date_field) + MONTH(date_field)/100 BETWEEN YEAR(Date) + MONTH(Date)/100 AND YEAR(Date) + IIF(MONTH(Date)<6,-1,0) + (MONTH(Date) + IIF(MONTH(Date)<7, 12, 0))/100
 
Upvote 0
Part of the problem the Months are not date formatted so these formulas aren't working (text only). I have to do various look ups to correlate date with the pivot tables. Let me keep trying some of this.

Thanks for the suggestions.
 
Upvote 0
Part of the problem the Months are not date formatted so these formulas aren't working (text only). I have to do various look ups to correlate date with the pivot tables. Let me keep trying some of this.

Thanks for the suggestions.

I hope it is not much trouble changing those texts to date.
 
Upvote 0
Setting up data as much as possible like a database is usually the best approach - good, normalised data in structured tables.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top