# Best way to calculate rolling 6 months average

#### PCRIDE

##### Well-known Member
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
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
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
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
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
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
Setting up data as much as possible like a database is usually the best approach - good, normalised data in structured tables.

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

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