excelexcel-formulanestedmaxaverage

Excel extracting values from multiple tests


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


Solution

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

    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

    =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