Ultimately I am trying to find the average of the top 3 values per test on the latest day for "Person 1". I am able to find the latest day using the Large(If()) formula, and I am able to plug that into a Unique(Filter()) function to find the corresponding "test" numbers for the day. My problem occurs when I try to extract the actual results from the test. My data is:
Person Date Test Rep Result
Person 1 10/9/2023 1 5 1.06459372
Person 1 10/9/2023 1 4 1.11329722
Person 1 10/9/2023 1 3 0.91809
Person 1 10/9/2023 1 2 0.92332983
Person 1 10/9/2023 1 1 0.81854742
Person 1 10/9/2023 2 5 0.79415372
Person 1 10/9/2023 2 4 0.78722627
Person 1 10/9/2023 2 3 0.77623751
Person 1 10/9/2023 2 2 0.75960889
Person 1 10/9/2023 2 1 0.55552335
Person 1 10/9/2023 3 5 1.25761919
Person 1 10/9/2023 3 4 1.38660111
Person 1 10/9/2023 3 3 1.28825923
Person 1 10/9/2023 3 2 1.11500258
Person 1 10/9/2023 3 1 0.93898195
Person 1 10/9/2023 4 5 1.01453846
Person 1 10/9/2023 4 4 1.06929
Person 1 10/9/2023 4 3 0.93578771
Person 1 10/9/2023 4 2 0.94945872
Person 1 10/9/2023 4 1 0.84496289
Person 1 10/23/2023 1 5 1.58905785
Person 1 10/23/2023 1 4 1.49243315
Person 1 10/23/2023 1 3 1.4587432
Person 1 10/23/2023 1 2 1.58905785
Person 1 10/23/2023 1 1 1.47988413
Person 1 10/23/2023 2 5 0.368215
Person 1 10/23/2023 2 4 1.66144122
Person 1 10/23/2023 2 3 1.3734
Person 1 10/23/2023 2 2 1.75722655
Person 1 10/23/2023 2 1 1.24049032
Person 2 4/29/2024 1 5 1.89406839
Person 2 4/29/2024 1 4 1.90691308
Person 2 4/29/2024 1 3 1.81291382
Person 2 4/29/2024 1 2 1.58922
Person 2 4/29/2024 1 1 1.40970617
Person 2 4/29/2024 2 5 1.70049909
Person 2 4/29/2024 2 4 1.92244355
Person 2 4/29/2024 2 3 1.92599629
Person 2 4/29/2024 2 2 1.63100333
Person 2 4/29/2024 2 1 1.67577882
I am using the formula =FILTER(E:E,UNIQUE(FILTER(E:E,IF((A:A=H3)*(B:B=LARGE(IF(A:A=H3,B:B),1)),C:C))))
where Column E are the test results, and H3 is dropdown for a list of names. This formula gives me a result "#VALUE". I've also tried including a logic where =...,K:K=Unique(Filter())...
For context, I will also be finding integrating a Max to find the max average for the day, if that has any influence on the formula. But my starting point is trying to find the top 3 reps per test on the latest day (10/23/23 for person 1).
With AVERAGEIFS, with spill
Here's an option with helper column and AVERAGEIFS
; helper column simplifies the formula.
Rank column:
=LET(
data, $A$2:$E$5000,
persons, INDEX(data, , 1),
dates, INDEX(data, , 2),
tests, INDEX(data, , 3),
results, INDEX(data, , 5),
COUNTIFS(
persons, persons,
dates, dates,
tests, tests,
results, ">" & results
) + 1
)
Results column:
=LET(
for_persons, H3:H4,
top_n, 3,
data, $A$2:$F$5000,
persons, INDEX(data, , 1),
dates, INDEX(data, , 2),
tests, INDEX(data, , 3),
results, INDEX(data, , 5),
ranks, INDEX(data, , 6),
latest_dates, MAXIFS(dates, persons, for_persons),
AVERAGEIFS(
results,
persons, for_persons,
dates, latest_dates,
ranks, "<=" & top_n
)
)
Updated for Excel 2021, without LAMBDA
Assuming availability of FILTER
=LET(
person, H3,
latest_date, MAXIFS(
Table2[Date],
Table2[Person], person
),
rank_for_result, COUNTIFS(
Table2[Person], person,
Table2[Date], Table2[Date],
Table2[Test], Table2[Test],
Table2[Result], ">" & Table2[Result]
) + 1,
latest_results, FILTER(
Table2[Result],
(rank_for_result <= 3) *
(Table2[Date] = latest_date)
),
AVERAGE(latest_results)
)
Result:
Formula screenshot:
Corrected 2024-08-07
Grouped by person/date/test per P.b's suggestion
=LET(
person_date_test, I2#,
ranks, G2#,
top_n, 3,
AVERAGEIFS(
Table1[Result],
Table1[Person], INDEX(person_date_test, , 1),
Table1[Date], INDEX(person_date_test, , 2),
Table1[Test], INDEX(person_date_test, , 3),
ranks, "<=" & top_n
)
)