Moving Averages: Auto Calculating Similar Weeks Between Multiple Years

[LStreet]

Hello,

I am working with moving averages for the first time in Excel and have found the "Calculating Moving Averages" post from April 2009 in the "Learn Excel" forum particularly helpful. However, I'm wondering if more can be done with my data.

I have a long column of data, updated each week. Current weekly average formulas appear to be working fine. However, I am wondering if there is a formula to auto calculate moving averages during similar weeks for previous years data in the same column.

For instance, when I add a new week data and auto caluclate the current four week Moving Average (cells B702:B705); Is there a formula I can type into B718, that would show me the 4 week Moving Average for similar weeks for year 2012 -- located within cells B610:B661?

More over, would the formula update in the Past Year's Weekly Moving Averages when new weekly data is added?

Thank you kindly for looking at this question.

Best Regards,

Lisa

 

[bobhc]

Good day Lisa

I would use the data for a pivot table, but you would need to sort your data in to one table with all data header on one line, then you could pivot, slice and dice how ever you want.

It is a good idea to spend a little time on your data table to get the rows/colums as you need, you can not have areas of blank columns/rows but each column does not have to have to same column data length as its Neighbours. This way as you add to your data it is a one click to refresh the pivot with the new data.

 

[SirJB7]

Hi, LStreet!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, maybe you find this useful too:
http://chandoo.org/forum/threads/moving-average-averaging-every-12-cells.11756/#post-68870

I played a little with your uploaded file, make many changes, and I'm stuck with this doubt:
What do you mean by "same" weeks of previous years?

For example, for Current 4 WK MA the formula references rows 702-705, corresponding to week numbers 41-44 (of actual year), related to dates of 10/11, 10/18, 10/25 and 11/1. No problem with this.
But for 2012 4 WK MA the formula references rows 675-678, corresponding to week numbers 14-17, related to dates 4/5, 4/12, 4/19 an 4/26 of year 2013.
For 2011 4 WK MA the formula references rows 623-626, corresponding to week numbers 14-17, related to dates 4/6, 4/13, 4/20 an 4/27 of year 2012.
And for 2010 4 WK MA the formula references rows 571-574, corresponding to week numbers 14-17, related to dates 4/8, 4/15, 4/22 an 4/29 of year 2011.
Are the previous year formulas correct or they're not updated? Please clarify.

Regards!

 

[TomK]

Hello Lisa,
One way to get similar weeks for different years is to utilize =weeknum(), and =year(), which will extract the week number in a year, and the year, from a date. A couple of helper columns telling you the year of each date, and the week number of each date, will make this easier.
In your workbook, things like 2012 4 wk MA can be calculated using averageifs ‘the Year helper column is 2012’, and ‘the weeknum helper column <= the current weeknum’, and ‘>= the current weeknum -4’.

Using dynamic, named ranges will allow for new data being added to the bottom to be included, and formulas to update.
Sorry, I don't have time right now to set this all up in a workbook, but I hope that makes some sense.

 

[SirJB7]

Hi, LStreet!


I assumed your formulas for previous years were correct... once, time ago, and that you've never updated them since then. So I went on and arrived to this new monster. Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Moving Averages_ Auto Calculating Similar Weeks Between Multiple Years - Moving Averages (for LStreet at chandoo.org).xlsx


It uses a few (6) dynamic -most except MinCell- ranges:

MinCell: cell of column A where totals start
=Sheet1!$A$709

DateTable: columns A: D, yes, 2 columns added
=DESREF(Sheet1!$A$4;;;CONTARA(INDIRECTO("Sheet1!$A$4:$A$"&COINCIDIR(MinCell;Sheet1!$A:$A)-1));CONTARA(Sheet1!$4:$4)) -----> in english: =OFFSET(Sheet1!$A$4,,,COUNTA(INDIRECT("Sheet1!$A$4:$A$"&MATCH(MinCell,Sheet1!$A:$A)-1)),COUNTA(Sheet1!$4:$4))

DateList: column A
=DESREF(DataTable;;0;;1) -----> in english: =OFFSET(DataTable,,0,,1)

InventoryList: column B
=DESREF(DataTable;;1;;1) -----> in english: =OFFSET(DataTable,,1,,1)

YearList: column C
=DESREF(DataTable;;2;;1) -----> in english: =OFFSET(DataTable,,2,,1)

WeeknumList: column D
=DESREF(DataTable;;4;;1) -----> in english: =OFFSET(DataTable,,4,,1)


I found that most of the dates are Fridays, and the list of that who aren't is at work range L708:L717. This might lead to differences as with the criteria used for the WEEKNUM function to start counting weeks per year. I added 2 work columns E:F to show the differences.

Formulas:
C4: =AÑO(A4) -----> in english: =YEAR(A4)
D4: =NUM.DE.SEMANA(A4;16) -----> in english: =WEEKNUM(A3,16) (16 as starting week on Saturday as your dates are on Fridays, assumed end of the week; if start, then it should be changed to 15)
E4: =CONTAR.SI(C$4:C4;C4) -----> in english: =COUNTIF(C$4:C4,C4)
F4: =SI(D4<>E4;"X";"") -----> in english: =IF(D4<>E4,"X","")


Totals.

If you agree to keep the current layout (i.e., 3 row for min/max/avg, 2 blank rows, and then a group of 4 rows (3 + 1 blank) for each year you'd be able to use only 2 formulas: one for column A and another one for column B, as described below.

Changed the minimum, maximum and average formulas to:
=MIN(InventoryList)
=MIN(InventoryList)
=PROMEDIO(InventoryList) -----> in english: =AVERAGE(InventoryList)

Copied previous totals and formulas to work range I708:J729 for witness purposes.

Added 3 formulas at work range C708:E729 to illustrate about how to calculate (relative to MinCell range):
row diff: C709: =FILA()-FILA(MinCell) -----> in english: =ROW()-ROW(MinCell)
mod 4: D709: =RESIDUO(FILA()-FILA(MinCell);4) -----> in english: =MOD(ROW()-ROW(MinCell),4)
int / 4: E709: =ENTERO((FILA()-FILA(MinCell)-1)/4)-1 -----> in english: =INT((ROW()-ROW(MinCell)-1)/4)-1
used to embed in the next formulas.

Year block: 3 + 1 blank row

Column A: YYYY N WK MA (format)
=(SI(FILA()-FILA(MinCell)>4*2;AÑO(MAX(DateList))-(ENTERO((FILA()-FILA(MinCell)-1)/4)-1);"Current")&" "&RESIDUO(FILA()-FILA(MinCell);4)*4&" WK MA") -----> in english: =(IF(ROW()-ROW(MinCell)>4*2,YEAR(MAX(DateList))-(INT((ROW()-ROW(MinCell)-1)/4)-1),"Current")&" "&MOD(ROW()-ROW(MinCell),4)*4&" WK MA")

Column B: moving average
=SUMAPRODUCTO((YearList=AÑO(MAX(DateList))-(ENTERO((FILA()-FILA(MinCell)-1)/4)-1))*(WeeknumList>=DESREF(WeeknumList;FILAS(WeeknumList)-1;1;1)-(RESIDUO(FILA()-FILA(MinCell);4)*4)+1)*(WeeknumList<=DESREF(WeeknumList;FILAS(WeeknumList)-1;1;1))*(InventoryList))/(RESIDUO(FILA()-FILA(MinCell);4)*4) -----> in english: =SUMPRODUCT((YearList=YEAR(MAX(DateList))-(INT((ROW()-ROW(MinCell)-1)/4)-1))*(WeeknumList>=OFFSET(WeeknumList,ROWS(WeeknumList)-1,1,1)-(MOD(ROW()-ROW(MinCell),4)*4)+1)*(WeeknumList<=OFFSET(WeeknumList,ROWS(WeeknumList)-1,1,1))*(InventoryList))/(MOD(ROW()-ROW(MinCell),4)*4)

For current year, this is a shorter column B alternative (placed in the workbook just for educational purposes, it's simpler) which I recommend you to replace with any other previous year formula:
=PROMEDIO(DESREF(InventoryList;FILAS(InventoryList)-RESIDUO(FILA()-FILA(MinCell);4)*4;;RESIDUO(FILA()-FILA(MinCell);4)*4;1)) -----> in english: =AVERAGE(OFFSET(InventoryList,ROWS(InventoryList)-MOD(ROW()-ROW(MinCell),4)*4,,MOD(ROW()-ROW(MinCell),4)*4,1))

One advantage is that you can copy down each year block to get the same info for previous years.


I didn't check border condition at the beginning of the year when there're not 4 weeks yet, but that could be (I hope) fixed replacing the expression:
MOD(ROW()-ROW(MinCell),4)*4,1)
by a kinda COUNTIF or something alike. Your homework is to test it with proper data and check what figures you get.

Just advise if any issue.

Regards!

PS: all work ranges (columns E:F aside the table, dates not Friday, totals original contents, and 3 columns C:E aside first totals) can be erased anytime, they're just for clearness and easier understanding of the model.

 

[LStreet]

Wow, Sir. That is quite a beast of a solution! I'm going to spend a little time studying what you have done here. Thank you for taking the time and effort to find a solution.

*As per you previous question: Yes they were correct at once upon a time, but I had given up on hand keying in the moving averags for previous years in the spring. Sorry I did not get back to you sooner. I got slammed at work.

 

[SirJB7]

Hi, LStreet!
Don't worry, don't hurry, take it easy, and let us now how it ends. Or come back if stuck.
Regards!

 

[Hui]

27 Dec 02 has been entered as 27 Dec 03
26 Dec 03 has been entered as 26 Dec 04
24 Dec 04 has been entered as 24 Dec 05
31 Dec 04 has been entered as 31 Dec 05
30 Dec 05 has been entered as 30 Dec 06
21 Dec 07 has been entered as 21 Dec 08
28 Dec 07 has been entered as 28 Dec 08

May-Oct 2000 is missing
29 Aug 03 possibly should be 28 Aug 03

 

[Hui]

I would tackle the solution slightly differently to SirJB7

First Shift the reporting area away from Column A or B

I would use named Formula for the Date and Invetory

Date: =OFFSET(Sheet1!$A$7,,,COUNTA(Sheet1!$A:$A)-4,1)
Inventory: =OFFSET(Date,,1)
WDate: =max(date) = Work date or max date

Current 4 week average =AVERAGEIFS(Inventory,Date,"<="&WDate,Date,">="&EDATE(WDate,-1))
Current 8 week average =AVERAGEIFS(Inventory,Date,"<="&WDate,Date,">="&EDATE(WDate,-2))
Current 12 week average =AVERAGEIFS(Inventory,Date,"<="&WDate,Date,">="&EDATE(WDate,-3))

Last Year
4 week average =AVERAGEIFS(Inventory,Date,"<="&EDATE(WDate,-12),Date,">="&EDATE(WDate,-13))
8 week average =AVERAGEIFS(Inventory,Date,"<="&EDATE(WDate,-12),Date,">="&EDATE(WDate,-14))
12 week average =AVERAGEIFS(Inventory,Date,"<="&EDATE(WDate,-12),Date,">="&EDATE(WDate,-15))

2 years ago
4 week average =AVERAGEIFS(Inventory,Date,"<="&EDATE(WDate,-24),Date,">="&EDATE(WDate,-25))
8 week average =AVERAGEIFS(Inventory,Date,"<="&EDATE(WDate,-24),Date,">="&EDATE(WDate,-26))
12 week average =AVERAGEIFS(Inventory,Date,"<="&EDATE(WDate,-24),Date,">="&EDATE(WDate,-27))

etc

 

[Sajan]

Hello,
Here is one more idea...
Setup named ranges called Dates and Inventory as Hui suggested.

To calculate the 4 week average for current year:
=AVERAGE(LOOKUP((LARGE(Dates,ROW(OFFSET(A$1,,,4)))/7)-0*52, Dates/7, Inventory))
enter with Ctrl + Shift + Enter

Replace the 4 with 8, 12, etc. to get 8 week average, 12 week average, etc.
Replace 0 with 1 for "1 year ago", with 2 for "2 years ago", with 3 for "3 years ago", etc.


Cheers,
Sajan.