What formula would work on finding the Top 50 largest values in a column/sheet.
In my sheet I need to find the top 50 performers and the total number (minutes) based on a month - so that if the Top #1 performer fell to #2 spot the next month it would update in my dashboard?
I have 30 Teams (column=Team Name) with 25 ppl /team (column=Team Member), each month each person from that team enters their "minutes" (submitted in a form)
Every month I would like to see who the Top 50 performers are and the total minutes.
Please help :)
Best Answer
-
You can use a RANKEQ Function with COLLECT to Rank each row's data based on Month. The way a RANKEQ formula works is we first list what number we're looking at,
=RANKEQ(Numbers@row,
Then we list the Range/Column we're looking in to, so if you didn't need to know the Month it would look like this:
=RANKEQ(Numbers@row, Numbers:Numbers)
However since you also have criteria (a month), we can use COLLECT Function in the place of the Column to filter down:
COLLECT(Numbers:Numbers, Date:Date, MONTH(@cell) = 1)
This would look for the MONTH being 1, or January. If you always want to see "Today's Month", we could update it to:
COLLECT(Numbers:Numbers, Date:Date, MONTH(@cell) = MONTH(TODAY()))
However sometimes the MONTH(@cell) can error if there are blank cells or cells with text in this column, so to be safe lets wrap that in an IFERROR Function to ignore those cells:
COLLECT(Numbers:Numbers, Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
Update the RANKEQ with this "range":
=RANKEQ(Numbers@row, COLLECT(Numbers:Numbers, Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())))
FINAL FORMULA:
Now, you'll see an error for every row where the Month doesn't match. To get rid of this, wrap another IFERROR around the entire formula:
=IFERROR(RANKEQ(Numbers@row, COLLECT(Numbers:Numbers, Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))), "")
Once you have this Column Formula set in your sheet, you can then Create a Report that Filters by this Rank column, up to 50. You can Sort the Report so it shows the rows in order as well. Let me know if this works for you!
Cheers,
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
Answers
-
You can use a RANKEQ Function with COLLECT to Rank each row's data based on Month. The way a RANKEQ formula works is we first list what number we're looking at,
=RANKEQ(Numbers@row,
Then we list the Range/Column we're looking in to, so if you didn't need to know the Month it would look like this:
=RANKEQ(Numbers@row, Numbers:Numbers)
However since you also have criteria (a month), we can use COLLECT Function in the place of the Column to filter down:
COLLECT(Numbers:Numbers, Date:Date, MONTH(@cell) = 1)
This would look for the MONTH being 1, or January. If you always want to see "Today's Month", we could update it to:
COLLECT(Numbers:Numbers, Date:Date, MONTH(@cell) = MONTH(TODAY()))
However sometimes the MONTH(@cell) can error if there are blank cells or cells with text in this column, so to be safe lets wrap that in an IFERROR Function to ignore those cells:
COLLECT(Numbers:Numbers, Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))
Update the RANKEQ with this "range":
=RANKEQ(Numbers@row, COLLECT(Numbers:Numbers, Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY())))
FINAL FORMULA:
Now, you'll see an error for every row where the Month doesn't match. To get rid of this, wrap another IFERROR around the entire formula:
=IFERROR(RANKEQ(Numbers@row, COLLECT(Numbers:Numbers, Date:Date, IFERROR(MONTH(@cell), 0) = MONTH(TODAY()))), "")
Once you have this Column Formula set in your sheet, you can then Create a Report that Filters by this Rank column, up to 50. You can Sort the Report so it shows the rows in order as well. Let me know if this works for you!
Cheers,
Genevieve
Join us for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.
-
Thanks Genevieve, appreciate your help. I will have a go at this!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!