SOLVED

average over a period of specific dates

New Contributor

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.

3 Replies
best response confirmed by apovazou (New Contributor)
Solution

@apovazou 

Hi

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

 

Demo.png

 

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

Thank you very much. It's exactly what I needed. I appreciate it.
You're welcome. Glad this helped & Thanks for posting back