Search code examples
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.

    • 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