Average of Last x Instances of a Criteria

whothemannow

New Member
Joined
Feb 23, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Mar 5, 2018
Messages
2,437
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 5, 2018
Messages
2,437
Office Version
  1. 2016
Platform
  1. Windows
...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
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 23, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

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
    Annotation 2021-07-30 115240.png
    23.5 KB · Views: 7

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
What should happen if there aren't 6 scores to average?
 

whothemannow

New Member
Joined
Feb 23, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

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
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 23, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
    Annotation 2021-08-03 103934.png
    42.4 KB · Views: 2
  • Annotation 2021-08-03 103935.png
    Annotation 2021-08-03 103935.png
    25.5 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Change the 2 after the sequence to 1
 
Learn Excel from Bill Jelen

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

Forum statistics

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

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