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 would greatly appreciate any help. Also, I want to avoid VBA since this is for another end user and would like to avoid that.
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.
