Formula to Display Top 10 Rows with Lowest Ranking

LVU22

I have a sheet that captures the average rankings assigned to requests in order of priority. I need a formula to create a metric that displays the Top 10 request IDs that have the lowest average ranking (lowest ranking = highest priority). Here is an example of what I need:

The metric needs to be dynamic as the top 10 requests could change as new requests are assigned rankings. It would also be really helpful if I could display the dollar amount of these requests in a separate metric.

Any help is very much appreciated!

Kerry St. Thomas

If you add a helper column and use =RANKEQ(NumericalRank@row, NumericalRank:NumericalRank,0), you can then build a report off that column that filters to that rank equal to or under 10. I have used this in the past as a way to get Top-N results (usually for a dashboard). I might have the sorting reversed with what you're after; if I do, just change the 0 at the end of the formula to a 1.

You may want to take it one more layer: if there's a possibility your numerical ranking can be duplicated, you might want to update the formula to this: RANKEQ(NumericalRank@row, DISTINCT(NumericalRank:NumericalRank),0). Adding DISTINCT will make it possible you'll show the top 10-ish. For example, if you had a list of 1, 1, 2, 2, 3, 4, 4, 5, 5, 5, 5 - without DISTINCT you'd have rank 1, 1, 3, 3, 5, 6, 6, 8, 8, 8, 8. (Note the 4-way tie at the end.) With DISTINCT, you'll only make it to rank 5 - so you'll definitely show any ties in rank. This is of course going to be determined whether ten items are more important, or rank value is more important. Your business use case will dictate that.

I know I crawled a bit down that rabbit hole, but hopefully I helped. Good luck!

Kerry St. Thomas

An error will carry through - but the NATURE of the error might not. If Column 1 has a blank in one column that causes a divide by zero error in Column 2 and then you try to do something with that column 2 you might end up with an Unparseable error in Column 3 and so on. I suggest instead wrapping your formulas in an IFERROR to accommodate for the issues so that they won't carry forward or constrain the results with another column (so exclude 0, for example). Good luck!

Kerry St. Thomas

If you add a helper column and use =RANKEQ(NumericalRank@row, NumericalRank:NumericalRank,0), you can then build a report off that column that filters to that rank equal to or under 10. I have used this in the past as a way to get Top-N results (usually for a dashboard). I might have the sorting reversed with what you're after; if I do, just change the 0 at the end of the formula to a 1.

You may want to take it one more layer: if there's a possibility your numerical ranking can be duplicated, you might want to update the formula to this: RANKEQ(NumericalRank@row, DISTINCT(NumericalRank:NumericalRank),0). Adding DISTINCT will make it possible you'll show the top 10-ish. For example, if you had a list of 1, 1, 2, 2, 3, 4, 4, 5, 5, 5, 5 - without DISTINCT you'd have rank 1, 1, 3, 3, 5, 6, 6, 8, 8, 8, 8. (Note the 4-way tie at the end.) With DISTINCT, you'll only make it to rank 5 - so you'll definitely show any ties in rank. This is of course going to be determined whether ten items are more important, or rank value is more important. Your business use case will dictate that.

I know I crawled a bit down that rabbit hole, but hopefully I helped. Good luck!

LVU22

https://community.smartsheet.com/discussion/comment/467409#Comment_467409

Hello Kerry!

Thank you so much for your solution, it is very innovative. Quick question about this helper column—I have about 5 people that submit, in their opinion, a rank that a particular request should be assigned. I am basing the helper column on another column that takes the average ranking given by all 5 of these people. So, if all 5 have not submitted their rankings, thus giving a "#DIVIDEBYZERO" error in the average ranking column, would it also make sense that the helper column results in the same error? I just want to ensure that I have the general formula working.

Thanks again!

Kerry St. Thomas

An error will carry through - but the NATURE of the error might not. If Column 1 has a blank in one column that causes a divide by zero error in Column 2 and then you try to do something with that column 2 you might end up with an Unparseable error in Column 3 and so on. I suggest instead wrapping your formulas in an IFERROR to accommodate for the issues so that they won't carry forward or constrain the results with another column (so exclude 0, for example). Good luck!