average over a period of specific dates

[apovazou]

Hello there,

I've got a table in which I insert my athletes' performance data on a daily basis. Each session can be either a training or a game session. I want to create a formula where I'll get an average of the data over the last 4 games only. 

So, to make it clear, I want my formula to skip the training session dates and calculate the average of the latest 4 games only.

I have created the following formula:

=AVERAGEIFS([Total Distance],[Athlete],[@Athlete],[Type Refined],2,[Date],"<="&MAXIFS(DailyEntry[Date],DailyEntry[TypeRefined],2),DailyEntry[Date],">="MAXIFS(DailyEntry[Date],DailyEntry[Type Refined],2)-3)

Type refined represents the type of the session (1=training, 2=game)

The problem in this formula is the -3. It subtracts 3 dates in total and not 3 game dates only.

Any ideas?

Thank you.

[#2 · Accepted solution]

@apovazou 

Hi

(in Welcome to your Excel discussion space!: Excel product name and version number :))

in G2:

=AVERAGEIFS(DailyEntry[Total Distance], DailyEntry[Athlete],F2, DailyEntry[Type Refined],2, DailyEntry[Date],">=" & AGGREGATE(14,4,(DailyEntry[Athlete]=F2)*(DailyEntry[Type Refined]=2)*DailyEntry[Date],3))

in H2 (Excel 365/Web) assuming you always have 3 dates otherwise this can be fine tuned:

=AVERAGE(INDEX(SORT(FILTER(DailyEntry, (DailyEntry[Athlete]=F2)*(DailyEntry[Type Refined]=2)),4,-1),SEQUENCE(3),1))

corresponding sample attached

[#3]

Thank you very much. It's exactly what I needed. I appreciate it.

[#4]

You're welcome. Glad this helped & Thanks for posting back