Jun 30 2021 06:08 AM - edited Jun 30 2021 06:08 AM
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.
Jun 30 2021 06:54 AM
SolutionHi
(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
Jun 30 2021 03:31 PM
Jun 30 2021 10:52 PM
Jun 30 2021 06:54 AM
SolutionHi
(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