How to not skip ranks and deal with ties?

summetg

Hi all,

I worked with the Prodesk on using the RANKEQ function which was helpful, but I am realizing I have records which are ties. Essentially I have a column which ranks our projects; if the weight is 0 it's "not ranked" and should be thrown out of the rankings, if it's anything else it's ranked. The formula is as follows:

=IFERROR(RANKEQ([Weighted Project Score]@row, [Weighted Project Score]:[Weighted Project Score], 0), "Not Ranked")

I found 2 issues with this and it's the following:

• It's skipping numbers (i.e. I've got 3 entries with the rank # if 99, then it jumps to 102, not seeing the ones in between)
• There's no way to clearly see the ties.

How can I modify the formula to do the following:

1. If there's a tie in there's, to not skip the ranking sequence of numbers
2. If there's a tie in the range, to highlight this
3. If there's a tie, to look at another column (i.e. Initiative ID or defined date field) and automatically re-rank based on that.

I found a similar thread and tried adapt the solution to mine, with no luck (Sequential numbering of duplicates — Smartsheet Community)

Genevieve P.

Hi @summetg

I agree with Paul's solution in the post you linked to! This should work for you as well, you would just need to update the column names:

=VALUE(RANKEQ([Weighted Project Score]@row, [Weighted Project Score]:[Weighted Project Score], 1) + "." + COUNTIFS([Weighted Project Score]:[Weighted Project Score], @cell = [Weighted Project Score]@row, Row:Row, @cell <= Row@row))

The first column added would be a System Generated auto-number column (called "Auto").

Then the second added column uses this Auto column to create number values (called "Row"):

Then you can use Paul's formula above:

This would show you ties because you will see if they are .2 or .3 in the list.

However if you prefer to see full numbers and want to number the tied values as a whole number, you can then add in another helper column with your original RANKEQ formula, looking at the Rank column instead:

Is this what you were looking to do?

Cheers,

Genevieve

Genevieve P.

Hi @summetg

I agree with Paul's solution in the post you linked to! This should work for you as well, you would just need to update the column names:

=VALUE(RANKEQ([Weighted Project Score]@row, [Weighted Project Score]:[Weighted Project Score], 1) + "." + COUNTIFS([Weighted Project Score]:[Weighted Project Score], @cell = [Weighted Project Score]@row, Row:Row, @cell <= Row@row))

The first column added would be a System Generated auto-number column (called "Auto").

Then the second added column uses this Auto column to create number values (called "Row"):

Then you can use Paul's formula above:

This would show you ties because you will see if they are .2 or .3 in the list.

However if you prefer to see full numbers and want to number the tied values as a whole number, you can then add in another helper column with your original RANKEQ formula, looking at the Rank column instead:

Is this what you were looking to do?

Cheers,

Genevieve

summetg

Hi Genevieve,

This was very helpful and I made some headway - I actually had a Row ID column which is an auto-number column. I'm kind of stuck at the last step now where I was receiving an "Invalid Data Type' error and trying different things.

My goal is to just keep things showing as ties, but not skip - tried your RANKEQ formula above (let's swap the column 'Rank' with 'Prioritized Score Helper').

=RANKEQ([Prioritized Score Helper]@row, [Prioritized Score Helper]:[Prioritized Score Helper], 1)

This produced an '#invalid data type' error. I then tried the following:

=IFERROR(RANKEQ([Prioritized Score Helper]@row, [Prioritized Score Helper]:[Prioritized Score Helper], 1), "Not Ranked")

This resulted in all entries being 'Not Ranked". In my sheet, we have certain cells which have a value of "not ranked", so I think the RANKEQ function is being thrown off by this. I'm going to try a few other things, but do you have any suggestions on how to adjust this? I feel need to add some type of IF statement to ignore condition to not rank the "Not Ranked" values and keep them noted in this new column....Thanks!

Genevieve P.

Hi @summetg

Is it possible there's an "Invalid Data Type" error in the Prioritized Score Helper column? Formula errors can create domino effects, so if there's even one cell with an error in that column it will roll up to this other formula.

Try wrapping the IFERROR around your Prioritized Score Helper formula to see if that resolves it!

If not, we can add in a COLLECT statement to the final RANKEQ formula.

summetg

Hi Genevieve,

Maybe I created some confusion with the column names, so I'll include some screenshots. I essentially used your formula you have in "RANK" in the column "Prioritized Score Helper" column which has the "IFERROR" and adjusted it so it's descending. For reference here it is

=IFERROR(RANKEQ([Weighted Project Score]@row, [Weighted Project Score]:[Weighted Project Score], 0) + "." + COUNTIFS([Weighted Project Score]:[Weighted Project Score], @cell = [Weighted Project Score]@row, ROW:ROW, @cell <= ROW@row), "Not Ranked")

I'm getting the right result where ties are noted with decimals and items that should not be ranked are populated as such (because they are also not weighted):

Then the "official ranking" aka "Prioritized According to Score" uses the formula to keep the ties and Not Ranked:

=IFERROR(RANKEQ([Prioritized Score Helper]@row, [Prioritized Score Helper]:[Prioritized Score Helper], 0), "Not Ranked")

Instead all are being noted as "Not Ranked" - so this is where I am stuck.

Genevieve P.

Hi @summetg

Thanks for identifying you're using text in some of the rows, this helps a lot.

Ok try the following - we'll use a COLLECT function to identify that you only want to Rank the rows that have a Number in them, like so:

=IFERROR(RANKEQ([Prioritized Score Helper]@row, COLLECT([Prioritized Score Helper]:[Prioritized Score Helper], [Prioritized Score Helper]:[Prioritized Score Helper], ISNUMBER(@cell)), 0), "Not Ranked")

Let me know if this worked!

Cheers,

Genevieve

summetg

Hi Genevieve,

Thanks for the follow up - turns out we're going to keep the standard rank logic with the duplications and skips as that's what is the generally accepted principle.

My colleague and I found an alternative solution by updating the helper column to add a "/10" to divide by 10, which would help anyone who wants to keep the ranks sequential when there's a tie.

=IFERROR(RANKEQ([Weighted Project Score]@row, [Weighted Project Score]:[Weighted Project Score], 0) + ((COUNTIFS([Weighted Project Score]:[Weighted Project Score], @cell = [Weighted Project Score]@row, ROW:ROW, @cell <= ROW@row)) / 10), "Not Ranked").

Also an edit to my previous formula for the re-rank aka prioritized according to score column to use the condition of 1 to indicate descending order

=IFERROR(RANKEQ([Prioritized Score Helper]@row, [Prioritized Score Helper]:[Prioritized Score Helper], 0), "Not Ranked")

Your formula above also resulted in the same result from my brief review. Appreciate your help again on this and hope this helps other users in the future