Mar 29 2021 09:24 PM
Hi All, I'm hoping I can get some help with the following query.
I have been using the sort and filter function in the attached workbook to work out the top 5 results from a list of names and scores.
Instead of finding the top 5 results I want to be able to show the results for people who achieve a score of 5 or more including any duplicates who score 5.
Have been struggling with this one for a couple of days and would appreciate some help.
Cheers
This is the formula I am using, what do I need to change to be able to see all records who scored 5 or above.
=SORT(FILTER(A2:B12, B2:B12>=LARGE(B2:B12, E2)), 2, -1)
Mar 29 2021 10:07 PM
Solution@robwill100 See attached. Two working formulae, though I feel that there must be better ways to solve this. But I can't thinks of anything else right now.
=SORTBY(FILTER(Table1,Table1[Score]>=E2),FILTER(FILTER(Table1,Table1[Score]>=E2),{0,1}),-1)
Or, same as above but wrapped in LET:
=LET(
fltr,FILTER(Table1,Table1[Score]>=E2),
return,SORTBY(fltr,FILTER(fltr,{0,1}),-1),
return
)
Mar 29 2021 10:49 PM
Mar 30 2021 11:10 PM
@Riny_van_Eekelen I hope I am not stretching my luck in asking another question.
I would like to add a 3rd column to be displayed when i apply the formula you provided to me. The additonal column contains values related to the person in the "name" column and the results in the "Score Column".
Once again appreciate your help.
Mar 31 2021 02:24 AM
@robwill100 See attached.
Mar 29 2021 10:07 PM
Solution@robwill100 See attached. Two working formulae, though I feel that there must be better ways to solve this. But I can't thinks of anything else right now.
=SORTBY(FILTER(Table1,Table1[Score]>=E2),FILTER(FILTER(Table1,Table1[Score]>=E2),{0,1}),-1)
Or, same as above but wrapped in LET:
=LET(
fltr,FILTER(Table1,Table1[Score]>=E2),
return,SORTBY(fltr,FILTER(fltr,{0,1}),-1),
return
)