Ranking when duplicates

Question

I need help with a challenging formula.

I have the following situation:

Row#

Value Score

Effort Score

Priority Score

Rank*

1

50

5

45

4

2

70

10

60

1

3

65

5

60

2

4

60

20

40

5

70

25

45

3

If Priority Score = Value score - Effort score

then rank by priority score (with greatest priority score = rank  1)

if priority score is duplicated like in row 1 and 5 as well as row 2 and 3, then row with greatest value score should have higher rank instead of duplicate rank.

So, for example, since row 2 and 3 have a priority score of 60, row 2 should be ranked 1 since it has the greater value score of 70 compared to 65 in row 3.

So, with a working formula, values would populate like how I've captured them in the above Rank column.

I understand Rankeq would rank them but it duplicates rank.

I know I can use countif to determine if there is a duplicate.

BUT, how can I capture if priority score = duplicate, then compare duplicate row's value score and rank the duplicates by their value score?

The formula will also need to consider if there are more 3+ rows with the same priority score.

For now, I'm assuming there are no duplicate priority scores with duplicate value scores. That will be my next problem to concur.

Paul Newcome · Answer

It does actually address the need to rank based on priority then by value by giving them a combined string. If you notice my screenshot, it ranks exactly how you wanted based on your initial screenshot.

To incorporate additional variables, you would simply extend the string. If you go with something along the lines of "PSESVSYYYYMMDD". We may have to get a little creative with the date, maybe a helper column for the dates themselves if you want earlier dates to be higher ranked, but this does work.

mbeaudoin · Answer

This is helpful but it doesn't address my need to first rank based on priority then rank the duplicates by their value score. If it was just this simple then this solution would work but after this problem, I need to then add ranking continued duplicates by their effort score and then if duplicates still persist those duplicates will get ranked by date.

Paul Newcome · Answer

@chamna Please check your source(s) before posting. There is no RANK function in Smartsheet, and your formula does not incorporate the [Value Score] column at all.