Formula for ranking top 3 names/nominees in a text/number column

Monica

Hello! We have a nomination form with several text/number columns where people can add the name of the person or people they want to nominate. Most columns have 1 nominee for each row, but some have multiple nominees listed from a team. I would like to create a report or summary that ranks the top 3 names mentioned in each column (category) to help leadership with the selection process. Are there specific formulas I can use to achieve this? Thanks in advance

Danielle Arteaga

The ease of doing this depends a lot on how many nominees you have and the structure of your sheet. But, assuming you can identify the list of possible candidates, here's one approach.

Create a sheet separate from the one where you collect the votes. Set it up with these columns:

(This is just example data I used ...)

In the Nominees column, list each individual nominee. (If it's a huge list, my favorite trick is to export to Excel, de-dup the column, then paste that back into Smartsheet.)

In the [Vote Totals] column, place this formula (column-level):

=COUNTIF({Top Nominees Example Nominees}, CONTAINS(Nominees@row, @cell))

(NOTE - You'll need to change the name between the braces {} by linking to whatever sheet holds the data your nomination form collects, pointing to the column that contains the names)

**At this point, you may want to sort your Vote Totals column in descending order to help things make sense.**

In the [Rank] column, place this formula (column-level):

=RANKAVG([Vote Totals]@row, [Vote Totals]:[Vote Totals], 1)

This assigns an average ranking to each nominee, which helps you identify "ties."

Now, in the Place column, place this formula:

=IF(Rank@row = LARGE(Rank:Rank, 1), "First", IF(Rank@row = LARGE(Rank:Rank, 2), "Second", IF(Rank@row = LARGE(Rank:Rank, 3), "Third", IF(Rank@row = LARGE(Rank:Rank, 4), "Fourth", "Fifth"))))

This will show you ranking across first, second, third, fourth and fifth place vote getters. (If you want to rank more than the top 5, just keep repeating the pattern in the formula for each place you want to capture.

A NOTE OF STRANGENESS - for the life of me, I cannot figure out what this formula does not account for third place. (@Paul Newcome or @Genevieve P. - any thoughts here‽‽) But, you hopefully get a useable solution until Paul or Genevieve works their magic.

Danielle Arteaga

The ease of doing this depends a lot on how many nominees you have and the structure of your sheet. But, assuming you can identify the list of possible candidates, here's one approach.

Create a sheet separate from the one where you collect the votes. Set it up with these columns:

(This is just example data I used ...)

In the Nominees column, list each individual nominee. (If it's a huge list, my favorite trick is to export to Excel, de-dup the column, then paste that back into Smartsheet.)

In the [Vote Totals] column, place this formula (column-level):

=COUNTIF({Top Nominees Example Nominees}, CONTAINS(Nominees@row, @cell))

(NOTE - You'll need to change the name between the braces {} by linking to whatever sheet holds the data your nomination form collects, pointing to the column that contains the names)

**At this point, you may want to sort your Vote Totals column in descending order to help things make sense.**

In the [Rank] column, place this formula (column-level):

=RANKAVG([Vote Totals]@row, [Vote Totals]:[Vote Totals], 1)

This assigns an average ranking to each nominee, which helps you identify "ties."

Now, in the Place column, place this formula:

=IF(Rank@row = LARGE(Rank:Rank, 1), "First", IF(Rank@row = LARGE(Rank:Rank, 2), "Second", IF(Rank@row = LARGE(Rank:Rank, 3), "Third", IF(Rank@row = LARGE(Rank:Rank, 4), "Fourth", "Fifth"))))

This will show you ranking across first, second, third, fourth and fifth place vote getters. (If you want to rank more than the top 5, just keep repeating the pattern in the formula for each place you want to capture.

A NOTE OF STRANGENESS - for the life of me, I cannot figure out what this formula does not account for third place. (@Paul Newcome or @Genevieve P. - any thoughts here‽‽) But, you hopefully get a useable solution until Paul or Genevieve works their magic.

Monica

Thanks a bunch! I am going to give this try today.

Paul Newcome

@Danielle Arteaga It skips third because there is a tie for second. It goes "1 2 2 4" instead of "1 2 3 4".

@Monica We have a few different options on how to handle ties.

We could display it as

1-Bindi

2-Ann & Ellen

3-Charlie, Filipe, Gigi, etc....

or

1-Bindi

2-Ann

2-Ellen

4-Charlie

4-Filipe

or

1-Bindi

2-Ann

3-Ellen

4-Charlie

5-Filipe

or

1-Bindi

2-Ann

2-Ellen

3-Charlie

3-Filipe

Danielle Arteaga

@Paul Newcome Not sure I follow why, though. There is a tie for second, and they are both labeled "second." Then there should be a tie for third, but it skips to fourth. There is a tie for fourth, and yet it doesn't skip to tenth, it labels them fifth. That's why I got stuck understanding why. But, thank you so much for jumping in here to help. Again. 😁

Monica

Thanks again, @Danielle Arteaga! I tried it for one of the nominations category and that worked for me. I am having the issue for ties.

@Paul Newcome - how can I make the first option work?

Paul Newcome

@Danielle Arteaga The IF statement that outputs "First", etc. stops at "Fourth" and then everything else is "Fifth". So it will work for 1st, 2nd, 3rd, 4th, and 5th, but then everything after that including 1,000,000th will show as "Fifth".

@Monica I am suggesting we move the collection to another sheet where we can use some basic helper columns to make life a little easier with pulling everything together.

First helper column is going to be a text/number column (called "Rank" in this example). This will have numbers manually entered in order from 1 to whatever number you want to stop the rankings at. Your original post mentions top three, so I would have

1

2

3

The next column over will be another text/number column (called "Score" in this example). You would use this column formula:

=LARGE(DISTINCT({Source Sheet Rank Column}), Rank@row)

This should look like this using the sample data in the above screenshots:

60

57.5

52.5

Finally we would have a text/number column (called whatever you want) that will display the names that fall into each of those ranks.

=JOIN(COLLECT({Source Sheet Nominees Column}, {Source Sheet Rank Column}, @cell = Score@row), CHAR(10))

The above will insert a line break in between each name that will be visible when you apply the wrap text formatting. If you want a different delimiter between each of the names, you would replace CHAR(10) at the end with whatever delimiter you want. If you want to get a little more complex and have something like commas between the first groups and "and" before the last to be a little more grammatically correct (A, B, C, and D), we can do that too. Just let me know, and we can throw together a SUBSTITUTE function for you to wrap around the JOIN.