Formula To Average N Most Recent Group

How can I write a formula that finds all of a specified users' five Nth recent dates, and then displays the calculated efficiency average?

Please see the attached file for an example. I already have a helper column which specifies the entry number for each unique date. Thanks so much!

Desired Operation:

Formula in Cell F8: The idea is to find the first most-recent group of five entries for the user name specified in the D column, then display the efficiency average (column H) of those entries. In my example, all the purple cells are the ones being calculated.

Formula in Cell F9: The idea is to find the second most-recent group of five entries for the user name specified in the D column, then display the efficiency average (column H) of those entries. In my example, all the blue cells are the ones being calculated.

Formula in Cell F10: The idea is to find the third most-recent group of five entries for the user name specified in the D column, then display the efficiency average (column H) of those entries. In my example, all the green cells are the ones being calculated, but since there isn't enough data, 'MORE DATA RQD' is displayed instead of the error message.

Admin note: moved to General

Not sure where you get your sample answers from, but this works on your sample data...
=AVERAGEIFS($H$16:$H$38,$J$16:$J$38,E8,$B$16:$B$38,">="&LARGE(IF($J$16:$J$38=E8,$B$16:$B$38),MIN(5,COUNTIF($J$16:$J$38,E8))))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Hello swordswinger710,

Been quite a while.

Sorry, but I fail to see how the "Helper" Column could be of any use in this instance, since it returns duplicate values.

Your requirement would need a VBA approach to count values in Colored Cells (Not colored by Conditional Formatting), or you will have to do it manually.

The other alternative is to manually sort and sum by color to obtain the required results.

Regards.

@FDibbins Thank you, it looks like my example wasn't quite as clear as I thought it was though. I updated the file, reattached it, and here is how it's supposed to function:

Formula in Cell F8: The idea is to find the first most-recent group of five entries for the user name specified in the D column, then display the efficiency average (column H) of those entries. In my example, all the purple cells are the ones being calculated.

Formula in Cell F9: The idea is to find the second most-recent group of five entries for the user name specified in the D column, then display the efficiency average (column H) of those entries. In my example, all the blue cells are the ones being calculated.

Formula in Cell F10: The idea is to find the third most-recent group of five entries for the user name specified in the D column, then display the efficiency average (column H) of those entries. In my example, all the green cells are the ones being calculated, but since there isn't enough data, 'MORE DATA RQD' is displayed instead of the error message.

@Winon It certainly has been a while! I've got married and have three kids in the meantime, how've you been? Thank you for your post, although I think that now if you read what I wrote above, you will find that the helper column might have more significance (although I'd love to figure this out without it), and the colours are only there to help clarify the example. I hope it is easier to understand now!

Hi swordswinger710,

Congratulations with tying the knot, and three kids already!

I am still confused though.

You state:

The idea is to find the first most-recent group of five entries for the user name specified in the D column

There are 15 entries for Bill in total. What criteria should be used to establish the most recent, since you have only 2 entries for Bill in Green, then 6 entries for Bill in Blue and 6 entries again for Bill in Purple.

Your average for Bill in blue shows the average for 6 entries for Bill, and not 5 entries.

How should we tell the program where to distinguish between the entries as per your sample?

Sorry if I sound dumb and stupid, but I really don't get it.

Regards.

I think I understand now, you are after a progressive average of latest 5, latest 4, latest 3 etc? (why even bother with 3, 4 and 5 if there is not enough data anyway?)

1. For your helper )not really needed, you could also use this (more efficient)
=COUNTIFS($A$16:A16,A16,$B$16:B16,B16,$H$16:H16,"<>")

2. You have highlighted 6 purple cells, not 5.
your values are...
5 = 50
4= 112
3= 104
4 = 73
avg = 84.75
5 = 86
avg = 85
6 = 100
avg = 87.5

My revised formula, no helper needed...
=AVERAGEIFS($H$16:$H$38,$A$16:$A$38,$D$8,$B$16:$B$38,">="&LARGE(IF($A$16:$A$38=$D$8,$B$16:$B$38),6-ROWS($A$1:A1)))

@Winon & FDibbons The colours are correct, but it looks like I still fumbled on the explanation yet again - this one is giving me a bit of trouble with being clear. This is not your fault at all, and I really appreciate your continued efforts in helping me achieve what I'm after!

So when I'm saying I need to find the 1st most-recent group of five entries for Bill, I'm trying to say that the formula finds the 5 latest dates for Bill, which in my example are the 6 purple cells:

1ST MOST RECENT GROUP OF FIVE DATES
1. 12-Nov-18 (1 entry)
2. 11-Nov-18 (1 entry)
3. 10-Nov-18 (2 entries)
4. 08-Nov-18 (1 entry)
5. 07-Nov-18 (1 entry)

..and then calculates the average efficiency for all 6 entries across those dates and displays that value in F8.

For the 2nd most-recent group of five entries for Bill, I'm trying to say that the formula finds the five next latest dates for Bill, which in my example are the 7 blue cells:

2ND MOST RECENT GROUP OF FIVE DATES

6. 02-Nov-18 (1 entry)
7. 01-Nov-18 (1 entry)
8. 14-Oct-18 (1 entry)
9. 05-Oct-18 (3 entries)
10. 03-Oct-18 (1 entry)

..and then calculates the average efficiency for all 7 entries across those dates and displays that value in F9.

ETC for the 3rd, 4th, and 5th groups...

There will be more and more new entries added to the list, and I'll be using the formula to calculate averages for other users' entries as well, once we figure this one out. Does this help?

Does anyone else have any thoughts on my situation?

This proposed solution not only doesn't get rid of the helper column, although it does modify it, but also adds a helper sheet. Perhaps on seeing this one of the other contributors can improve it.
1) The helper column on the AVG Group sheet uses: =COUNTA(A16:A100) in J16 and =IF(J16>0,J16-1,"") in J17 and down
2) On sheet 1 the first four columns invert the order of the data on the AVG Group sheet from the most recent to the oldest.
3) Columns E:F filter the date and efficiency data for a specific user.
4) Column G (Helper2) groups and ranks the dates using: =IF(E2="","",IF(E2<>E1,SUM(G1,1),G1))
5) On the AVG Group sheet cells F8:F12 are populated using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

@ JeteMc,

Hi JeteMc, kudos for your brilliant solution.

Regards.

@ JeteMc,

I was working on it as well, but with a DropDown List to select any Username. May we include it in your Sheet as well?

Regards.

@ Winon, Thank You for the compliment. I think that the drop down is a nice improvement.

@Jason, Good Show!

Thanks guys, there are some good ideas here. I prefer the simplicity of jason.b75's method, but I do run into issues with it if the dates aren't entered in ascending order, which JeteMc's method seems to resolve.

If there is a way to make Jason's method work with dates that aren't in the right order, that would be ideal, but can it be done?