# 3 Month Moving Average w/ Criteria

#### Bithsa

##### New Member
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Eric W

##### MrExcel MVP
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

</tbody>
Sheet1

Worksheet Formulas
CellFormula
D10=AVERAGEIFS(C\$2:C10,A\$2:A10,A10,B\$2:B10,">="&EOMONTH(B10,-3)+1)

</tbody>

<tbody>
</tbody>

Put the formula in D10, then drag down.

Let me know if this helps.

#### Bithsa

##### New Member
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
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!

Replies
3
Views
48
Replies
10
Views
197
Replies
5
Views
140
Replies
12
Views
62
Replies
0
Views
35

1,133,243
Messages
5,657,577
Members
418,401
Latest member
B_A_M155

### 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.

### Which adblocker are you using?

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

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