Formula To Average Recent Groups

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))