Rank based on multiple criteria?

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!

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.