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.

jmyzk_cloudsmart_jp

Hi @Monica & @Danielle Arteaga & @Paul Newcome

My first choice to address the tie issue is to use the RANKEQ function as follows:

[RANKEQ] =RANKEQ([Vote Totals]@row, [Vote Totals]:[Vote Totals], 0)

If you prefer to use the RANKAVG function, you can handle ties with the MATCH function like this:

[MATCH] =MATCH(RANKAVG@row, RANKAVG:RANKAVG)

You can derive the RANKAVG value from the RANKEQ value using the following formula:

=SUM(COLLECT(No:No, RANKEQ:RANKEQ, RANKEQ@row)) / COUNTIF(RANKEQ:RANKEQ, RANKEQ@row)

For example, if there are six 4th places, you calculate (4+5+6+7+8+9)/6 = 6.5. In the formula, the COLLECT part performs the summation (4+5+6+7+8+9), and the COUNTIF part counts the same rank numbers.

Finally, you can convert the rank to an ordinal number with the following formula:

=IF(OR(RANKEQ@row = 11, RANKEQ@row = 12, RANKEQ@row = 13), RANKEQ@row + "th", IF(RIGHT(RANKEQ@row, 1) = "1", RANKEQ@row + "st", IF(RIGHT(RANKEQ@row, 1) = "2", RANKEQ@row + "nd", IF(RIGHT(RANKEQ@row, 1) = "3", RANKEQ@row + "rd", RANKEQ@row + "th"))))

You can access the demo sheet below from the following URL.

https://app.smartsheet.com/b/publish?EQBCT=a83481fc059047208f17f811e52bc94a

Monica's Example

https://app.smartsheet.com/b/publish?EQBCT=e501ea1db08343b18d540fc3a7867857

In this example, I showed you can use just the MATCH function to get the equivalent of the RANKEQ;

[RANKEQ by MATCH] = =MATCH(RANKEQ@row, RANKEQ:RANKEQ)

I also showed a way to get RANKAVG from RANKEQ without using the No helper column, using the formula for the sum of arithmetic progressions (*) ;

=(COUNTIF(RANKEQ:RANKEQ, RANKEQ@row) * (RANKEQ@row + RANKEQ@row + COUNTIF(RANKEQ:RANKEQ, RANKEQ@row) - 1)) / 2 / COUNTIF(RANKEQ:RANKEQ, RANKEQ@row)

or

[the number of tied ranks] * ([the first tied rank] + [the last tied rank]) / 2

(*) Sn = n * (a + l) / 2

I hope the above has illustrated the relationship between MATCH, RANKEQ, and RANKAVG functions and how to use them for ranking with ties.

Paul Newcome

@jmyzk_cloudsmart_jp I have a different solution I use for when we want to split out ties into different ranks that requires less columns and less formulas. It is similar to the JOIN except I remove the DISTINCT function from the LARGE piece and use an INDEX function and a COUNTIFS in place of the JOIN to pull the 1st, 2nd, 3rd, etc. for each particular rank.

jmyzk_cloudsmart_jp

i @Paul Newcome & @Monica

Thank you, Paul.

I missed this part, " how can I make the first option work?".

Below is a solution based on Paul's method.

[Score] =IFERROR(LARGE(DISTINCT({Source Sheet Rank Column}), Rank@row), "")

[Nominees] = =IF(ISBLANK(Score@row), "", Rank@row + " - " + JOIN(COLLECT({Source Sheet Nominees Column}, {Source Sheet Rank Column}, Score@row), " & "))

The formula shows the nominees nicely, as expected.

https://app.smartsheet.com/b/publish?EQBCT=8895df8184564edbbe9d689aa1da23a7

Source Sheet Ranges

{Source Sheet Rank Column} : [RANKAVG]

{Source Sheet Rank Column}: [Nominees]

https://app.smartsheet.com/b/publish?EQBCT=5c0a3f283b984df4a8397cbc5f98857e

Another Solution

As all the ordering methods, RANKEQ, RANKAVG, and MATCH, order the rank, we can use the INDEX function as follows:

[Score] =IFERROR(INDEX(DISTINCT({Source Sheet Rank Column}), Rank@row), "")

Then, as the above refers to RANKEQ or MATCH ranks, the Nominees formula becomes like this;

=IF(ISBLANK(Score@row), "", Rank@row + " - " + JOIN(COLLECT({Source Sheet Nominees Column}, {Source Sheet RANKEQ or MATCH}, Score@row), " & "))

I found it interesting that various functions are providing different solutions to a single problem😁

https://app.smartsheet.com/b/publish?EQBCT=7de7bee9c3da4861a37fa4e7df64187d