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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,120
Maybe something like this?

ABCD
1NameMonth3-mo average
2A1/1/201623
3B1/1/201619
4C1/1/201634
5D1/1/201692
6A2/1/201653
7B2/1/201643
8C2/1/201623
9D2/1/201643
10A3/1/20161630.66667
11B3/1/20166542.33333
12C3/1/20165738
13D3/1/20163456.33333
14A4/1/20165340.66667
15B4/1/20166658
16C4/1/20164341
17D4/1/20162333.33333
18A5/1/20162130
19B5/1/20166766
20C5/1/20165451.33333
21D5/1/20163530.66667

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

Worksheet Formulas
CellFormula
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

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

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

Forum statistics

Threads
1,136,637
Messages
5,676,924
Members
419,659
Latest member
ShalDRH

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
Top