3 Month Moving Average w/ Criteria

[Bithsa]

Hi all,

I have a spreadsheet with multiple employees on it, their number of completed tasks, and the month that it was recorded. It looks something like this:

Employee Month Completed Tasks Moving average
A 1/1/2016 23
B 1/1/2016 19
C 1/1/2016 34
D 1/1/2016 92
A 2/1/2016 53
B 2/1/2016 43
C 2/1/2016 23
D 2/1/2016 43
A 3/1/2016 16
B 3/1/2016 65
C 3/1/2016 57
D 3/1/2016 34
A 4/1/2016 53
B 4/1/2016 66
C 4/1/2016 43
D 4/1/2016 23
A 5/1/2016 21
B 5/1/2016 67
C 5/1/2016 54
D 5/1/2016 35

How do I create a formula to calculate a 3 month moving average for each individual employee (A,B,C, or D) without having to sort the columns? The formula should be able to extend down the column as new monthly data is entered and should travel over into the next year, so that we can see trends.

Thank you!

 

[Eric W]

Maybe something like this?

	A	B	C	D
Name	Month	3-mo average
A
B
C
D
A
B
C
D
A
B
C
D
A
B
C
D
A
B
C
D

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1/1/2016[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1/1/2016[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1/1/2016[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1/1/2016[/TD]
[TD="align: right"]92[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2/1/2016[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2/1/2016[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2/1/2016[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]2/1/2016[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]3/1/2016[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]30.66667[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]3/1/2016[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]42.33333[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]3/1/2016[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]38[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]3/1/2016[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]56.33333[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]40.66667[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]58[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]41[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]4/1/2016[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]33.33333[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]5/1/2016[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]30[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]5/1/2016[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]66[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]5/1/2016[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]51.33333[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]5/1/2016[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]30.66667[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D10[/TH]
[TD="align: left"]=AVERAGEIFS(C$2:C10,A$2:A10,A10,B$2:B10,">="&EOMONTH(B10,-3)+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in D10, then drag down.

Let me know if this helps.

 

[Bithsa]

Hi Eric,

THANK YOU! This is what I was thinking! However we have the possibility of new employees being added and employees leaving that will be removed from the spreadsheet. I'm wondering if the B10 cell reference in the EOMONTH function will pose an issue if the number of entries in each month is inconsistent.

Do you know of a way we can change the formula to have less margin for error?

 

[Bithsa]

Problem solved! Instead of ,">="&EOMONTH(B10,-3)+1), I did:

">="&EOMONTH($B10,-1)+1)&"<="&EOMONTH($B10,1)+1 so that it calculates a rolling average of the month before current, current, and month after current. Thank you for pointing me in the right direction!

 

[Eric W]

Glad to help!