3 Month Moving Average w/ Criteria

Bithsa

New Member
Joined
Jul 25, 2016
Messages
10
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!
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Maybe something like this?

ABCD
NameMonth3-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.
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,550
Messages
6,179,463
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top