# Average of Last x Instances of a Criteria

#### whothemannow

##### New Member
Hi all,

I am working on a spreadsheet about employee performance, with columns of employee name, month, year, performance, etc. (as a result, employees have multiple rows as the data is sorted by dates). How can I calculate the average of each employee's last 6 performance scores (excluding zeroes)? (so that each row would return that employee's average) I have tried numerous formulas and initially thought that I should use AVERAGEIF, but I can't seem to figure it out.
Any help would be greatly appreciated!

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Toadstool

##### Well-known Member
Hi Whothemannow,

With 365 you've more options but here's my approach using Excel 2016.

NOTE: It's probably easier to extract the last 6 (or nth if less than 6 exist) and do an average of those).

Whothemannow.xlsx
ABCDEFG
1NameDatePerformanceWhoStartAverage
2Fred01-Jan-021Jim\$A\$72.833333
3Sue30-Nov-022\$C\$7
4Jim29-Oct-033
5Jim26-Sep-040
6Sue25-Aug-055
7Jim24-Jul-064
8Fred22-Jun-074
9Jim20-May-084
10Jim18-Apr-093
11Fred17-Mar-103
12Jim13-Feb-112
13Jim12-Jan-122
14Fred10-Dec-121
15Jim08-Nov-132
16
Sheet1
Cell Formulas
RangeFormula
G2G2=AVERAGEIF(INDIRECT(F2&":\$A\$99999"),\$E\$2,INDIRECT(\$F\$3&":\$C\$99999"))
F2F2=ADDRESS(AGGREGATE(14,6,ROW(\$A\$2:\$A\$99999)-ROW(\$A\$1)/((\$A\$2:\$A\$99999=\$E\$2)*(\$C\$2:\$C\$99999<>0)),MIN(6,COUNTIF(\$A\$2:\$A\$99999,\$E\$2)))+ROW(\$A\$1),COLUMN(\$A\$1))
F3F3=ADDRESS(ROW(INDIRECT(F2)),COLUMN(\$C\$2))

#### Toadstool

##### Well-known Member
...as I said it's probably easiest to extract the last 6

Whothemannow.xlsx
ABCDEFG
1NameDatePerformanceWhoLast 6Average
2Fred01-Jan-021Jim22.833333
3Sue30-Nov-0222
4Jim29-Oct-0332
5Jim26-Sep-0403
6Sue25-Aug-0554
7Jim24-Jul-0644
8Fred22-Jun-074
9Jim20-May-084
10Jim18-Apr-093
11Fred17-Mar-103
12Jim13-Feb-112
13Jim12-Jan-122
14Fred10-Dec-121
15Jim08-Nov-132
Sheet1 (2)
Cell Formulas
RangeFormula
G2G2=AVERAGE(F2:F7)
F2:F7F2=IFERROR(INDEX(\$C\$2:\$C\$99999,AGGREGATE(14,6,ROW(\$A\$2:\$A\$99999)-ROW(\$A\$1)/((\$A\$2:\$A\$99999=\$E\$2)*(\$C\$2:\$C\$99999<>0)),ROW()-ROW(\$F\$1))),"")

#### Fluff

##### MrExcel MVP, Moderator
Another option
+Fluff 1.xlsm
ABCD
1NameDatePerformance
2Fred26/05/19893
3Sue06/06/19896
4Jim13/09/19931
5Jim17/11/19937
6Sue11/08/19941
7Jim04/06/19956
8Fred04/04/19962
9Jim20/03/199810
10Jim12/07/19985
11Fred22/01/19995
12Jim19/04/199945.50
13Jim12/06/199915.50
14Fred28/03/20073
15Jim23/01/200945.00
16Fred17/12/20106
17Sue07/09/20116
18Jim11/04/201314.17
19Jim22/08/201312.67
20Sue02/10/20142
21Jim28/05/201552.67
22Fred14/06/201574.33
23Jim14/11/201563.00
24Jim01/01/201694.33
25Fred20/12/201785.17
26Jim06/02/201834.17
27Jim04/08/201954.83
28Fred20/06/202086.17
29Jim26/02/202135.17
Master
Cell Formulas
RangeFormula
D2:D29D2=IFERROR(SUM(INDEX(SORT(FILTER(B\$2:C2,A\$2:A2=A2),1,-1),SEQUENCE(6),2))/6,"")

#### whothemannow

##### New Member

ADVERTISEMENT

Hi guys,

Thanks for the replies. Unfortunately, they do not exactly fit my needs as they are currently, so I thought some greater elaboration would be required. Attached is a screenshot of a construction of a sample table similar to what I currently have. Essentially, I am looking for column L to return that specific employee's average of their last 6 average performance scores excluding zeroes (not necessarily a rolling average). So L2 should be the average of John's overall last 6 average scores regardless of what the month or year is (of course in the actual table there are more months and years). This won't be used for employee specific insights, so it's not feasible to extract the last 6 responses separately for each employee.

I thought about adding a helper column (maybe COUNTIF) to return whether that month's Average Performance for that employee is one of their most recent 6. I thought this would make the averaging easier, rather than having everything in 1 formula, but I wasn't quite able to wrap my head around that either.
Note that I have Office 365 (not sure whether this makes a difference or not).
Thanks in advance!

#### Attachments

• Annotation 2021-07-30 115240.png
23.5 KB · Views: 7

#### Fluff

##### MrExcel MVP, Moderator
What should happen if there aren't 6 scores to average?

#### whothemannow

##### New Member

ADVERTISEMENT

What should happen if there aren't 6 scores to average?
Ideally then it would just return either a zero or blank value

#### Fluff

##### MrExcel MVP, Moderator
In that case how about
Excel Formula:
``=IFERROR(SUM(INDEX(FILTER(K\$2:K2,A\$2:A2=A2),SEQUENCE(6),2))/6,"")``

#### whothemannow

##### New Member
In that case how about
Excel Formula:
``=IFERROR(SUM(INDEX(FILTER(K\$2:K2,A\$2:A2=A2),SEQUENCE(6),2))/6,"")``
Hmm, that doesn't seem to work unfortunately, despite John having 6 responses

#### Attachments

• Annotation 2021-08-03 103934.png
42.4 KB · Views: 2
• Annotation 2021-08-03 103935.png
25.5 KB · Views: 2

#### Fluff

##### MrExcel MVP, Moderator
Change the 2 after the sequence to 1

### Similar threads

Replies
8
Views
375
Replies
3
Views
243
Replies
2
Views
168
Replies
19
Views
198
Replies
9
Views
931

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Threads
1,151,697
Messages
5,765,982
Members
425,320
Latest member
Galin

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

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