Ranking Entries

JPavlasek

I have a scoring smartsheet where we are ranking applicants based on a score. I would like to set up a column that automatically ranks them BASED on how high or low a number is in another column.

ex: Column 1, Column 2, Column 3 - all scores

Column 4 - Formula entered to automatically give me the total of Columns 1-3

Column 5 - Rank number assigned BASED on how high or low the number in Column 4 is.

OR ALTERNATIVELY

I would also like to possibly assign a rank based on a range of numbers. If they score between 20-30 points, then they would automatically be ranked as a 3.

I've played around with CountIf formulas and can't seem to figure out the magical one.

is that possible?

Ash A.

The main workaround that comes to mind is creating a Report driven from this Sheet where the rows are arranged based on total scores. Let's say you have a 'Total Score' column which gives you the total based on Column 1, 2, & 3. You would then use a Report with the same columns, however you would set a 'Sort - Descending to Ascending' in order to get the highest number at top, and the lowest at the bottom.

Does that help?

KPH

Hi @JPavlasek

I think you are close, the formula you would need for Colum 4 is a simple SUM, not a COUNTIF. COUNT will count the cells, SUM will add up the values within the cells.

=SUM([Column1]@row, [Column2]@row, [Column3]@row)

The formula above will give you the total score. As shown here:

Then in Column 5 you can use an IF.

In this example the value in Column5 is a text string based on whether or not the value in column 4 is less than 10 or not

=IF([Column4]@row < 10, "less than 10", "10 or more than 10")

You can replace the text output with a second formula. For example:

=IF([Column4]@row < 10, "less than 10", IF([Column4]@row < 20, "not less than 10 but less than 20", "20 or more"))

This second IF is only evaluated if the first is false. Hence why I am describing this as "not less than 10 but less than 20". Here is how that looks:

You can continue to nest IFs. In your case I think you want 0-10 to be ranked 1, 11-20 to be ranked 2, and 21-30 to be ranked 3.

This formula does that. Notice I am using <= to mean less than or equal to. You may want to change this.

=IF([Column4]@row <= 10, "1", IF([Column4]@row <= 20, "2", IF([Column4]@row <= 30, "3", "")))

This part at the end "" means the formula will return nothing if the score is more than 30. You can also omit it (and the preceding comma) but I have left it in as a place holder in case you need more IFs (you would replace "" with your IF).

I hope this helps.

JPavlasek

@KPH This ABSOLUTELY helps, THANK YOU! I think I'm definitely understanding better HOW to formulate a countIF formula overall, however I am STILL struggling.

The ranks are as follows:

1-20 = 4

21-40 = 3

41-60 = 2

61-80 = 1

Here are the three different formulas I've constructed, neither is working for anything past the first statement. Rank 4 is working, but nothing else is. If the count is over 20 for the Grand Total it tells me #INCORRECT ARGUMENT SET

=IF([Grand Total]@row <= 20, "4", IF([Grand Total]@row <= 40, "not less than 21", "3", IF([Grand Total]@row <= 60, "not less than 41", "2", IF([Grand Total]@row <= 80, "not less than 61", "1" ))))

=IF([Grand Total]@row <= 20, "4", IF([Grand Total]@row <= 40, ">= 21", "3", IF([Grand Total]@row <= 60, ">= 41", "2", IF([Grand Total]@row <= 80, ">= 61", "1"))))

=IF([Grand Total]@row <= 20, "4", IF([Grand Total]@row >=21, "<= 40", "3", IF([Grand Total]@row >= 41, "<= 60", "2", IF([Grand Total]@row >= 61, "<= 80", "1"))))

KPH

Hi

In each of your examples you have an extra argument in each IF statement after the first one. These parts in bold should not be there.

=IF([Grand Total]@row <= 20, "4", IF([Grand Total]@row <= 40, "not less than 21", "3", IF([Grand Total]@row <= 60, "not less than 41", "2", IF([Grand Total]@row <= 80, "not less than 61", "1" ))))

The syntax is …

logic, which is something like

[Grand Total]@row <= 40

Then comma

Then something to output or do if true, which you want to be the rank

1 or 2 or 3 or 4

Then comma

Then something to output or do false. Here you want another IF to be evaluated so you put in another formula.

JPavlasek

@KPH OK I was definitely OVER complicating it. I guess I didn't realize that the 2nd, 3rd, 4th statements only happen if the prior one is not true, thus making it irrelevant to add the "not less than" portion. I just fixed it and now it's working. Thank you SO much for your help! not only is my formula working, but I now understand these statements a bit better!

KPH

Wonderful! Troubleshooting is a great way to learn. You'll be able to adjust that formula to do all sorts of thing. 😍