What formula would work on finding the Top 50 largest values in a column/sheet.

Andrea Payne

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

Genevieve P.

Hi @Andrea Payne

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

Genevieve P.

Hi @Andrea Payne

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

Andrea Payne

Thanks Genevieve, appreciate your help. I will have a go at this!