CODEWITHSUNDEEP
excelexcel-formulanestedmaxaverage
Cra538
Cra538

Reputation: 33

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

Upvotes: 0

Views: 109

Answers (2)

nkalvi
nkalvi

Reputation: 2614

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

Upvotes: 2

P.b
P.b

Reputation: 11628

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

Upvotes: 2

Related Questions