Ranking Entries
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
-
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?
-
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.
-
@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"))))
-
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.
-
@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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!