[Solved] Rank using 3 columns

[idris]

I have a scoring table that I need to rank according to 3 columns.

Col_A and Col_B are ranked with highest score being best.
Col_C is ranked with the lowest score being best.
Scores can be positive, negative or zero.

If the score in Col_A is drawn, Col_B takes priority.
If the score in Col_A and Col_B is still drawn, Col_C takes priority.

I found this thread on a similar subject, but can't figure out how to use the recommended formula and certainly not how to modify it to return the results I need.
viewtopic.php?t=24928

[Lupp]

Check the attached example:

aoo111873_RankingRegardingPriorityOfValues.ods

(19.9 KiB) Downloaded 87 times

[idris]

I'm gonna have to learn how to use SUMPRODUCT(), ISNUMBER(), ROW(), IFERROR() and IFNA() to figure out how that actually works, but thanks.

[Lupp]

If you actually ar using OO.o or AOO, the functions IFEROR() and IFNA() are not available. They were new in LibreOffice a few years ago (2013-02-17, V 4.0.0.3).

[MrProgrammer]

I have a scoring table that I need to rank according to 3 columns. Col_A and Col_B are ranked with highest score being best. Col_C is ranked with the lowest score being best. If the score in Col_A is drawn, Col_B takes priority. If the score in Col_A and Col_B is still drawn, Col_C takes priority.

This attachment uses only the SUMPRODUCT and SUM formulas.
• Column E ranks Score 1 only.
• Column F is a rank tiebreaker using Score 2 if the Score 1 values are equal.
• Column G is a rank tiebreaker using Score 3 if the Score 1 and Score 2 values are equal. It uses > instead of < in the SUMPRODUCT formula because you want the lower score to have a higher rank.
• Column H is a rank tiebreaker which picks the lowest row if all three Score values are equal. You don't have any of the last situation so all of the values in the column are 1. Pay careful attention to the use of $ in H2's formula: =SUMPRODUCT(C2=$C$2:C2;B2=$B$2:B2;A2=$A$2:A2).
• Column I is the sum of columns E, F, G, and H. It's equal to your column D (rank wanted).

202409081041.ods

(13.36 KiB) Downloaded 68 times

This method is explained in more detail in my 2011 tutorial [Tutorial] Sorting and Filtering data with formulas. More information about SUMPRODUCT is in [Tutorial] The SUMPRODUCT function.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Lupp]

Mostly ranking is done in a way that two (or many) data sets get assigned the same rank if all the significant values are the same.
A respective gap is then left in the sequence of used ranking numbers.
RANK() itself handles it this way.
You may be interested in a solution also doing it this way applying the RANK() function only once, but to a column of "virtual" total scores prepared using a few helper columns, and mapping the given sets of scores monotonously to a single value regarding the intended order.

aoo111873_RankingRegardingPriorityOfValuesUsing_RANK.ods

(23.2 KiB) Downloaded 67 times

[MrProgrammer]

Mostly ranking is done in a way that two (or many) data sets get assigned the same rank if all the significant values are the same. A respective gap is then left in the sequence of used ranking numbers. RANK() itself handles it this way.

There are several methods to do ranking, as described in the Wikipedia article. Calc's RANK function uses what Wikipedia calls competition ranking. I can replicate all of these methods using SUMPRODUCT.

As I am sure you have seen, many people find that RANK is not helpful precisely because it does return equal ranks for duplicate values. They want to use RANK with MATCH to get an ordered list, but MATCH gives the same value twice and their list has a duplicate entry. SMALL is not helpful either because it only returns the value, not its index, so it can't be used if more than one column needs to be put in order. Finally, neither RANK nor SMALL allows people to use text values. All these probems disappear when using a SUMPRODUCT formula for what Wikipedia calls ordinal ranking, as illustrated in this attachment.

202409091139.ods

(32.73 KiB) Downloaded 54 times

You may be interested in a solution also doing it this way applying the RANK() function only once, but to a column of "virtual" total scores prepared using a few helper columns, and mapping the given sets of scores monotonously to a single value regarding the intended order.

I like the way you use -1 in J1 to reverse the order: lowest to highest. You note that the technique is limited by the IEEE-754 Double precision. That is why I have switched to the SUMPRODUCT formulas in 202409081041.ods when multiple columns need to be ranked. I will admit that your method is much easier for beginners to understand when ranking numeric values, and it will usually not encounter a precision limitation.

[Lupp]

As I am sure you have seen, many people find that RANK is not helpful precisely because it does return equal ranks for duplicate values. They want to use RANK with MATCH to get an ordered list, but MATCH gives the same value twice and their list has a duplicate entry.

Yes. And sometimes I answered related questions suggesting a way of disambiguation.
I did no longer for some time now.
The central shortcoming of RANK() is (imo) that it only works with numbers,
Sorting by formulas is a doubtable idea anyway. However, I tried a lot within the recent 15 years though I rarely (never?) needed it for my own work.
One more remark: Sorting original data (as any tampering with them) seems a bad idea. If somebody wants to use spreadsheets partly as a surrogate for a database there may be good reasons, but they should apply some basic principles of DBs then, too.
Concerning the sorting I would also prefer a solution creating a (the) sorting permutation using SUMPRODUCT() probably together with OFFSET(), but most users wouldn't understand that. Of course such a permutation is basically what an enhanced RANK() should do when abandoning the number-restriction, and the mandatory "competition ranking".
The recently in LibO (V24.8) implemented SORTBY() function can do what's needed to get the mentioned permutation, but whether it is used for direct output of sorted data or for the indexing, it will be another function needing to lock an output range with all the annoying consequences.
I just used up some time with developing and testing a little package of user code allowing to circumvent the problem. are you interested?

[MrProgrammer]

The central shortcoming of RANK() is (imo) that it only works with numbers

Possibly this behavior is due to the StarOffice developers replicating the behavior of RANK in Excel. I see that LibreOffice also offers RANK.AVG and RANK.EQ, neither of which:
• is part of the ODF 1.3 standard,
• can be used with text,
• helps with ordinal ranking which, based on forum topics, is what many people want.
Tests show that RANK and RANK.EQ are equivalent, though the documentation which I find doesn't say that they are. It is strange to introduce a new RANK.EQ function that seems identical to RANK and not explain how (or if) it is different.

Sorting by formulas is a doubtable idea anyway

Yes, I don't think I use that in my own spreadsheets. But many people create topics asking for that, so I wrote for them the tutorial referenced earlier. I realize that many people cannot understand how to use formulas in this way. That is their problem, not mine. LibreOffice introduced a feeble SORT function, but it only accepts one key! Later, SORTBY attempts to extend the feature, though it looses the ability to sort columns instead of rows. Neither is part of the ODF 1.3 standard. Neither provides the sorting permutation which access the data in sorted order unless one creates another range for SORTBY with 1 to N (for INDEX) or 0 to N-1 (for OFFSET) . It seems to me that the LO developers make lots of new features (bloat) without understanding what people really want. The documentation that I can find for SORT and SORTBY doesn't say whether they accept keys which are text, and the examples provided only show using numbers. I installed LO 7.5 a couple of months ago but the sorting functions aren't available there. I'm not going to install LO 24.8 just to test.

Concerning the sorting I would also prefer a solution creating a (the) sorting permutation using SUMPRODUCT() probably together with OFFSET(), but most users wouldn't understand that.

My Sorting and Filtering tutorial explains how to do it. And I have found that many people cannot understand it.

I just used up some time with developing and testing a little package of user code allowing to circumvent the problem. are you interested?

Of course. I have considering doing that too: writing a function which would accept both numbers and text, then return the sorting permutation. I already have merge sort implemented in Basic and minor changes would make it callable as a Calc function. It does return the sorting permutation. I understand how to use merge sort to handle multiple keys. A function written in Basic might be a bit slow when sorting 50000 rows by multiple keys, but that should be fine for the more typical case where people have a few dozen rows to sort. You could consider making a Request for comments topic about your package to get feedback, then creating a tutorial which you can link for people who create topics where that would be an easier solution for them than SUMPRODUCT.