Formula To Average Recent Groups

Hey all, I'm having trouble figuring out how to make a formula that will give me the average values of a recent group, and am hoping that somebody here will have a suggestion.

The formula needs to do this: Search the sheet for all the efficiency values of the five most recent dates by a specified user, calculate the average of these values, and display that number in a cell.

See the attached example. The colours are only there for clarification - they specify which cells are grouped, for example, purple indicates the five most recent dates for WILL, so all six efficiency values for those dates need to be averaged and displayed in Cell C3, in this case 145.

This will then need to be applied for the 2nd though 5th groups as well. Any thoughts?

Are the coloured cells something you've done manually to help us towards the solution you're looking for or are you expecting the formula to pick up the colours?

Formulas can't detect cell colours, that's only possible via VBA (unless the cells are coloured using Condtional Formatting).

Try this:

G3 =LOOKUP(2,1/((COUNTIF($F3:F3,$B$16:$B$61)=0)*($A$16:$A$61=$A$1)*(MID($J$16:$J$61,7,1)+0=$B3)),$B$16:$B$61)

Drag through K3 then down through row 7.

C3 =IFERROR(AVERAGEIFS(H:H,A:A,A$1,B:B,">="&K3,J:J,"GROUP "&B3&" *"),"MORE DATA RQD")

Drag down through C7.

See attachment. The helper cells (G3:K7) can be placed anywhere or even hidden.

@Special-K The colours are only there to aid in clarification, I updated my original post to reflect this.

@63falcondude Thank you, this gets me the results on my example, however, users need to be able to continue adding data, and as they do, the groups (and therefore the average efficiency results) will need to constantly update.

Oh, I see, you're using the CLARIFICATION column as well. I just put that in there to help clarify the way the groups should work. Users won't be adding that data.

Couldn't we use just one helper column that would automatically calculate the group number, and then have one formula set up to use that helper column data to calculate the average?

This setup works great for finding the average of all the entries for the five most recent days, and uses one helper column, hence why I think something similar should technically work for groups as well, but maybe it's not quite that simple?

No, I just threw the CLARIFICATION column in to help explain how the groups work. I suppose it could be in there if the values in the column are added automatically though.

And actually, yes, I think you described the groups perfectly. Group 1 is supposed to represent the five most recent days of efficiency values, Group 2 is the five 2nd most recent days of efficiency, and so on. Which means that once there are five newer entries for WILL, what would have been Group 1 five days ago is now Group 2.

Would my most recent post (#7) be of any help?

Swordswinger - this idea is overly convoluted. I would suggest you rethink the way you are doing this and come up with a simpler layout. Having to constantly alter group names as data is added is ludicrous!

Hello again, AliGW, lovely to see you again.

What I'm ultimately after is not so much the altering of group names, but instead, trying to create a formula to pull data from the most recent dates.

Perhaps this is simpler way of putting it: If I could somehow figure out how to get the average result of data taken from five of the most recent entries, I feel like I would be half-way there.

Apologies for not getting back to you on this, things came up and your other thread on this (which you should have stuck with) got pushed down.

I will take another look for you.

Just to recap, you want the average - per name - of the (1st) last 5 dates, then the avg of the 6-10 last dates?

aahh OK, I just saw your latest post. Back to the drawing board, but I think I have the principal down now

No worries FDibbins, I thought starting over more clearly would be helpful, but it doesn't seem that simple.

And yes, your recap seems correct. The 1st last 5 dates are the closest 1-5, the 2nd last dates are the closest 6-10, the 3rd 11-15, etc.

Originally Posted by swordswinger710

No worries FDibbins, I thought starting over more clearly would be helpful, but it doesn't seem that simple.

And yes, your recap seems correct. The 1st last 5 dates are the closest 1-5, the 2nd last dates are the closest 6-10, the 3rd 11-15, etc.

FT, from post 14, maybe this will make it clearer?

OK I can come back to your 1st answer (145) with this ARRAY beast...
=AVERAGEIFS($H$16:$H$61,$A$16:$A$61,"will",$B$16:$B$61,">"&LARGE(IF($A$16:$A$61="will",$B$16:$B$61),1)-6-ROWS($E$1:E1),$B$16:$B$61,"<="&LARGE(IF($A$16:$A$61="will",$B$16:$B$61),1))
...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.

But then if I drop the latest date (24 Nov), I get 159.8, or if I drop the earliest of 5 dates (20 Nov) I get 166.2, not 153

hmmm your data is very "dirty" for a name (Will), you have in-sequence dates (1, 2, 3 etc), out-of-sequence dates (1, 3, 4, 6) duplicate dates (not a problem) and different names mixed together for the same date

	R	S
33	WILL	14-Nov-18
34	BOB	14-Nov-18
35	WILL	14-Nov-18
36	BOB	14-Nov-18
37	WILL	14-Nov-18

These are throwing all sorts of curve balls

1. Can you sort your date?
2, Would you be OK with a helper (to ID unique name/dates), which could be hidden?

If you want, the data can be sorted on the same sheet - just sort by Date, then Name

Yeah, users enter data this way all the time, but maybe we could sort the rows on another sheet? And hidden helper columns are no issue whatsoever.

IF you can sort your data, this regular formula gives the same values as you did in your sample....
=AVERAGEIFS($H$16:$H$61,$A$16:$A$61,$D$1,$C$16:$C$61,">="&ROWS($A$1:A1)*5-4,$C$16:$C$61,"<="&ROWS($A$1:A1)*5)
copied down

Then in a helper column (I used C, you can use whatever you want, just change the BOLDED ranges...
C16=IF(A16<>$D$1,0,IF(OR(B16<>B17,A16<>A17),MAX($C17:C$62)+1,MAX($C17:C$62)-1))
copied down

This is exciting! Which sample are you using for this? I've having trouble lining up the data.

I have attached the file I worked on, I added sheet1 then sorted the data

Alright, I will check this out, thank you so much!

Keep me posted

swordswinger710 I do not understand this part ...

The formula needs to do this: Search the sheet for all the efficiency values of the five most recent dates by a specified user, calculate the average of these values, and display that number in a cell.

How do we determine 5 most recent dates when with one exception they are all in the future ... starting with 1-Oct-18.

What am I missing?

Ah! Thank you Ford.

I scanned through the posts looking for some kind of clue.

I missed the point of that one.

Took me a while to catch on, too lol, that post did it for me though

Just for S's and G's, see what you come up with, you're a pretty good formula-smith

Well I am confused.

I put a helper column in I16:I61 to get Group numbers.

Formula:

=IF(J16="","",--MID(J16,7,1))

Then this duplicates expected results. It makes no sense to me because it includes none of the qualifiers mentioned so far.

Formula:

=IF(COUNTIF($I$16:$I$61,B3)<5,"MORE DATA RQD",ROUND(AVERAGEIFS($H$16:$H$61,$I$16:$I$61,B3),0))

FlameRetired, the CLARIFICATION column is just there to help explain how the groups work, which means they won't normally be there.

Is there a way to make the helper column "I" figure out the most recent 5 groups for each user in the list, giving us the same results as it is now, but without using the CLARIFICATION column? That would solve this, I think.

Sorry for the delay, was a bit tied up.

Did my suggested file work at all for you?

Is there a way to make the helper column "I" figure out the most recent 5 groups for each user in the list, giving us the same results as it is now, but without using the CLARIFICATION column? That would solve this, I think.

Yes. I have a solution that currently uses array constants ... which assume 5 groups.

Those can instead be made reliably dynamic if ... there are not multiple groups with < 5 unique dates. Though I haven't tried it yet I question whether my ranking strategy will work.

If there are it will be back to the "salt mines".

The attached has:

A helper column C that returns unique dates for WILL.

Formula:

=IF(COUNTIFS($A$16:A16,A16,$A$16:A16,$A$1,$B$16:B16,B16)=1,B16,"")

A rank column D that ... ranks those dates.

Formula:

=IF(C16="","",RANK(C16,$C$16:$C$61))

Then column E returns the group #s.

Formula:

=IF(A16=$A$1,LOOKUP(LOOKUP(10^10,$D$16:D16),{0,6,11,16,21},{1,2,3,4,5}),"")

Then D3:D7

Formula:

=IF(COUNTIF($E$16:$E$61,B3) < 5,"MORE DATA RQD",ROUND(AVERAGEIF($E$16:$E$61,B3,$H$16:$H$61),0))

Thanks again everyone, great methods here which seem functional, despite being a bit bulkier than I originally thought they'd need to be.

What do you think about the method attached? This is more in the simplified direction that I was originally thinking, and the only issue I'm finding with it is when the dates aren't entered in ascending order.

Do you guys have any idea if that last issue can be resolved while maintaining this method?

despite being a bit bulkier than I originally thought they'd need to be.

Given the convoluted nature of the problem this doesn't seem a reasonable expectation.

This is more in the simplified direction that I was originally thinking ...

So why not use that?

... , and the only issue I'm finding with it is when the dates aren't entered in ascending order.

What I believe you are saying is that the example given is not truly representative of the real problem.

Having said that would you upload something reflecting the real problem? Please give us a "worst case" scenario.

If not perhaps it's time for you to ask Mod/Admin move this project to Commercial Services.

as for the sorting, you could set up (record) a quick macro that will do that all for you at the press of a button?

Another try. Managed to shrink it a bit, and I am not sure how this will work for all the situations you envision. Try it and let me know.

In D1:E1 two methods for calculating count of unique dates that are "WILL". D1 array entered.

Formula:

=MIN(25,SUM(IF(FREQUENCY(IF($A$1=$A$16:$A$61,$B$16:$B$61),$B$16:$B$61),1)))

In E1 non array entered.

Formula:

=MIN(25,SUMPRODUCT(($A$1=$A$16:$A$61)/COUNTIFS($B$16:$B$61,$B$16:$B$61,$A$16:$A$61,$A$16:$A$61)))

Reference either one for the helper column in I.

Formula:

=IF($A16<>$A$1,"",LOOKUP($D$1-SUMPRODUCT(($A$16:$A16=$A$1)/

COUNTIFS($B$16:$B16,$B$16:$B16,$A$16:$A16,$A$16:$A16))+1,{1,6,11,16,21,26},{1,2,3,4,5,6}))