Rank based on multiple criteria?

Adam Joly

Hello!

Is there a way to calculate unique rank values based on multiple criteria?

In this example, I'm attempting to rank a list of performers based on average rating. Several performers have the same average rating, so I'm using "# Reviews" as a secondary/tiebreak criterion. I've tried using "RankEQ" and "RankAVG" but those formulas return the same rank for any tied values. I'm thinking i need a second tiebreak criteria to differentiate.

Thanks everyone!

Will Jeffords

Hi @Adam Joly , Smartsheet Formula AI to the rescue (I hope)!

Try this…you have to create a helper column (which you can hide after set-up), but this worked for my little test:

The 1️⃣ formula creates a decimal-based result that then the 2️⃣ formula can rank in the simple order that you want. I used AI to help generate the formulas (I only needed to pay attention to the descending/ascending part of the formulas once created).

Let me know if these formulas work for you:

=RANKAVG([Avg Rating]@row, [Avg Rating]:[Avg Rating], 0) + RANKAVG([Number of Reviews]@row, [Number of Reviews]:[Number of Reviews], 0) / 10000

=RANKAVG([Helper Ranking Column]@row, [Helper Ranking Column]:[Helper Ranking Column], 1)

All my Best,

Will

Adam Joly

@Will Jeffords, thanks so much!

The formulas seem to work, and I like where you're going with this; however, the scenario seems to be a bit more complicated than I first thought (of course it is). I'm now seeing some rows have ratings AND review counts that are the same (thus getting the same rank once again). I suspect this is really happening b/c the data is so new and will resolve itself over time.

The end-goal is to create a "top 5 highest rated" list for a dashboard. Now I'm wondering if it'd be easier to use some sort of "collect" formula to retrieve all the items that match the highest scores.

Will Jeffords

hi @Adam Joly nice! Yeah, for grabbing the top five always, I suggest leveraging Sheet Summary fields and doing a COLLECT JOIN of Performers whose ranking is < 6. Let me know if you need further help on this or any other use case! Have an awesome weekend!

Will