Menu
I am running a Nerf league at a party and want my spreadsheet to show the top five contestants.
Contestants are allowed unlimited entries and only their top score is counted. Entries are being collected on Google Sheets and The Top 5 published on a kiosk screen.
Here is some sample data:
- **Full Name,Score** - Test Test,3 - Test2 Test2,1 - Test3 Test3,10 - Test4 Test4,3 - Test5 Test5,42 - Test5 Test5,500 - Test6 Test6,20
Here is the formula I have so far (with thanks to tigeravatar):
=INDEX($A$2:$A$28,MATCH(1,INDEX(($B$2:$B$28=LARGE($B$2:$B$28,ROWS(I$1:I1)))*(COUNTIF(I$1:I1,$A$2:$A$28)=0),),0))
This formula shows all maximum values - if, for example, one person has 5 entries that are higher than everyone else, they will all be counted.
The "top five" must show only the entry with the most points from five different contestants.
What do I need to do to show only the top entry that each contestant has provided?
894
Select cell B2, copy and paste formula =LARGE(A$2:A$16,ROWS(B$2:B2)) into the formula bar, then press the Enter key. See screenshot: 2. Select cell B2, drag the fill handle down to cell B6, then the five highest values are showing.
Method 1: Use the LARGE Function The conditional formatting will highlight the top 5 values in your table. How does this formula work? The LARGE function returns the nth largest element from a dataset. (If you want the top 10 values, simply change the 5 to a 10.)
As we want to extract unique items from a list, we need to search and look for the value 0. This will work when we use the MATCH function. It provides the comparative positioning of the initial equivalent value of the array. Depending on this result, the INDEX formula will extract unique items from a list.
Seems that the formula offered by @AdamL met the requirements:
=QUERY(A2:B28,"select A, max(B) group by A order by max(B) desc limit 5 label max(B) ''",0)
answered Sep 28 '22 18:09
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With