Ranking Entries

JPavlasek
JPavlasek ✭✭✭
edited 02/06/24 in Formulas and Functions

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?

Answers

  • Ash A.
    Ash A. Employee

    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
    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
    JPavlasek ✭✭✭
    edited 02/07/24

    @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
    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
    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
    KPH ✭✭✭✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!