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.67577882I 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).
Thank you