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
1	Name	Month		3-mo average
2	A	1/1/2016	23
3	B	1/1/2016	19
4	C	1/1/2016	34
5	D	1/1/2016	92
6	A	2/1/2016	53
7	B	2/1/2016	43
8	C	2/1/2016	23
9	D	2/1/2016	43
10	A	3/1/2016	16	30.66667
11	B	3/1/2016	65	42.33333
12	C	3/1/2016	57	38
13	D	3/1/2016	34	56.33333
14	A	4/1/2016	53	40.66667
15	B	4/1/2016	66	58
16	C	4/1/2016	43	41
17	D	4/1/2016	23	33.33333
18	A	5/1/2016	21	30
19	B	5/1/2016	67	66
20	C	5/1/2016	54	51.33333
21	D	5/1/2016	35	30.66667

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D10 =AVERAGEIFS(C$2:C10,A$2:A10,A10,B$2:B10,">="&EOMONTH(B10,-3)+1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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!