0

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

Improve this question
3
  • Your IFs are missing a value if false Commented Aug 6, 2024 at 19:59
  • Maybe filter by person then sort by date(desc) and result (desc) and take the top three values but too much data to type in and test. Commented Aug 6, 2024 at 21:17
  • Could you please confirm that dynamic array support and FILTER are available in your installation? They are available in Excel 2021 according to this: support.microsoft.com/en-us/office/… Commented Aug 7, 2024 at 15:12

2 Answers 2

Reset to default
2

With AVERAGEIFS, with spill

Here's an option with helper column and AVERAGEIFS; helper column simplifies the formula.

  • Add a helper column for result ranking
  • Since it is a dynamic formula, there's no need to fill-down

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

Averageifs result

Formula text for averageifs

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:

Result 2021

Formula screenshot:

Formula screenshot

Corrected 2024-08-07

Grouped by person/date/test per P.b's suggestion

  • Add a helper column 'rank' for convenience
  • Latest results grouped for person/date/test
  • Spill formulas
=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
    )
)

Result Grouped by person date test

Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

Top 3 values per test for max date for person (1)
=REDUCE({"Person","LastestDate","AvgforTop3"},UNIQUE(Table1[Person]),LAMBDA(acc,person,LET(latest_date,MAXIFS(Table1[Date],Table1[Person],person),test_and_results_for_latest_date,FILTER(HSTACK(Table1[Test],Table1[Result]),(Table1[Date]=latest_date)*(Table1[Person]=person)),tests,TAKE(test_and_results_for_latest_date,,1),results,TAKE(test_and_results_for_latest_date,,-1),top_3_per_test,DROP(REDUCE("top_3_per_test",UNIQUE(tests),LAMBDA(acc,test,VSTACK(acc,AVERAGE(TAKE(SORT(FILTER(+results,tests=test),,-1),3))))),1),avg_for_top_3s,(top_3_per_test),
IFNA(VSTACK(acc,HSTACK(person,latest_date,avg_for_top_3s)),""))))
I couldn't fit it in 1 comment, so you need to paste both comments behind eachother. I did not change much on your code. Main change to fix: Moved the AVERAGE from one layer to a prior (around the TAKE(SORT(FILTER(),,-1),3).
=REDUCE({"Person","LastestDate","Test#","AvgforTop3"},UNIQUE(Table1[Person]),LAMBDA(acc,p,LET(latest_date,MAXIFS(Table1[Date],Table1[Person],p),res_latest,FILTER(HSTACK(Table1[Test],Table1[Result]),(Table1[Date]=latest_date)*(Table1[Person]=p)),tests,TAKE(res_latest,,1),results,INDEX(res_latest,,2),top_3,DROP(REDUCE("top_3_per_test",UNIQUE(tests),LAMBDA(acc,test,LET(top3_test_res,TAKE(SORT(FILTER(HSTACK(tests,results),tests=test),2,-1),3),VSTACK(acc,HSTACK(INDEX(top3_test_res,1,1),AVERAGE(INDEX(top3_test_res,,2))))))),1),VSTACK(acc,IFNA(HSTACK(p,latest_date,top_3),HSTACK(p,latest_date))))))
|
2

=LET(f,FILTER(C2:E41,(A2:A41="Person 1")*(B2:B41=MAXIFS(B2:B41,A2:A41,"Person 1"))),MAP(UNIQUE(TAKE(f,,1)),LAMBDA(m,AVERAGE(LARGE(FILTER(TAKE(f,,-1),TAKE(f,,1)=m),{1;2;3})))))

First a filter is created for the max date for person 1 (f). Next the unique tests are mapped as m. Then the largest 3 values per test are averaged.

This would work in Excel 2021 as far as I know:

=LET(x,"Person 1",
     f,FILTER(Table1[[Date]:[Result]],(Table1[[Person]:[Person]]=x)*(Table1[[Date]:[Date]]=MAXIFS(Table1[[Date]:[Date]],Table1[[Person]:[Person]],x))),
     u,UNIQUE(INDEX(f,,2)), 
     t,SORT(FILTER(INDEX(f,SEQUENCE(ROWS(f)),{2,4}),INDEX(f,,2)=INDEX(u,ROW(A1),)),2,-1),
IFERROR(INDEX(CHOOSE({1,2},INDEX(t,1,1),AVERAGE(INDEX(t,{1,2,3},2))),,COLUMN(A1)),""))

It is incapable of spilling. but you can drag it down and to the right to show the next test average of 3.

It shows the test name/ID in the first column you paste it in and the average of top 3 values of that test in the column to the right of it, when dragged.

Improve this answer

3 Comments

Thank you, I am sure this would work. Unfortunately my office subscription doesn't have the LAMBDA or MAP function. I am working with Office Professional Plus 2021. A quick search seems to show that function hasn't been pushed out to my version.
I'll check later today. But maybe you can use it online: Excel.new ?
@Cra538 check the edit. I worked with table references, let me know it that works for you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.