Best way to calculate rolling 6 months average

PCRIDE

Well-known Member
Joined
Jan 20, 2008
Messages
890
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
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,889
Office Version
2007
Platform
Windows
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.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
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
 

PCRIDE

Well-known Member
Joined
Jan 20, 2008
Messages
890
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,889
Office Version
2007
Platform
Windows
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.
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
Setting up data as much as possible like a database is usually the best approach - good, normalised data in structured tables.
 

Forum statistics

Threads
1,082,305
Messages
5,364,401
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top