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!