Best way to calculate rolling 6 months average

[PCRIDE]

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]

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.

 

[DanteAmor]

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]

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]

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]

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]

Setting up data as much as possible like a database is usually the best approach - good, normalised data in structured tables.