How Can I Average Values based Top 5 Values in another column?

[btlizard]

I have been trying to figure this out for a few days and haven't been able to find anything online with this exact situation.

I have a large set of data (~5000) with names of individuals, dates of completion, and scores assigned. I have figured out without too much difficulty how to average all of the scores for a given individual and non-blank scores and dates.

What I need to do now is to average the five most recent scores for each individual. I need to be able to find the five latest dates and average the corresponding scores, all without having to sort or extract anything, since data is constantly being added or modified.

I thought I came up with a decent solution for this, but the problem I am having is that any method that tries to find the five largest (most recent) dates and then find the corresponding score seems to want to find the first instance of that date and report the score. This is a problem for duplicate dates.

Here is the formula I have right now, but I did some test runs and I know that I am getting the wrong average of the five most recent scores for several individuals:

{=IFERROR(IF(E3>0,IF(E3<=5,F3,AVERAGE(IF((--(DESIGNER=A3))*(--(DATE>37000))*(--(SCORE<>""))*((--(DATE=LARGE(IF((--(DESIGNER= A3))*(--(DATE>37000))*(--(SCORE<>""))>0,DATE), {1,2,3,4,5}))))>0,SCORE))),"--"),"--")}

To explain a bit what I have here:

• I have several named ranges for my data set.
• The names are in column A.
• Column E counts how many non-blank scores there are total for each individual.
• Column F is the overall average for all scores.
• Since the formula is interested in only the latest 5 scores, anything 5 or less will be the same as the overall average.
• The reason for DATE>37000 check is ignore any dummy dates.

I would greatly appreciate any help. Also, I want to avoid VBA since this is for another end user and would like to avoid that.

 

[Aladin Akyurek]

Care to post a 10 row sample along with the expected result?

 

[btlizard]

	A	...	E	F	G	H	I
1		...
2	Designer	...	# Scores	Overall Avg	Last 5 Avg	Expected	(delta)
3	Name 1	...	6	91.1%	90.9%	91.2%	+0.31%
4	Name 2	...	23	92.0%	88.2%	88.2%
5	Name 3	...	6	88.6%	87.2%	87.2%
6	Name 4	...	5	81.2%	81.2%	81.2%
7	Name 5	...	52	99.4%	99.7%	99.7%
8	Name 6	...	11	94.8%	96.4%	96.3%	-0.15%
9	Name 7	...	45	93.2%	98.6%	98.1%	-0.56%
10	Name 8	...	6	87.7%	87.4%	87.4%
11	Name 9	...	1	62.7%	62.73%	62.73%
12	Name 10	...	1	67.5%	67.5%	67.5%

<tbody>
</tbody>

Does this help? First 10 rows of my table. As you can see, some averages match while others do not.

Here is the raw data for those that do not:

Name 1
43241 43209 43222 43237 43237 43229
0.9721 0.9063 0.8953 0.8324 0.97 0.8902

Name 6
43124 43124 43129 43136 43146 43165 43195 43174 43206 43222 43222
0.9582 0.8565 0.9546 0.9089 0.975 0.963 0.9492 0.9286 1 0.9858 0.95

Name 7 data is kinda long.

 

[Aladin Akyurek]

Not sure this is what I wanted...

The following formula averages last 5 figures for a given name. If this is similar to what you intend, you can maybe adapt it to your data.

Control+shift+enter, not just enter:

=AVERAGE(IF(ROW(Figures)>=LARGE(IF(Names="jon",IF(ISNUMBER(Figures),ROW(Figures))),MIN(3,COUNTIFS(Names,"jon"))),IF(Names="jon",IF(ISNUMBER(Figures),Figures))))

Hope this helps.