Rank based on two criteria without ties

[mii0502]

Hello guys Hope you have a nice day!

I need to rank projects in descending order based on:
1. Their scores
2. If their scores are a tie, compare their YTD scores - the higher score the better rank
3. If their YTD scores are also a tie, just randomly assign their ranks
*I want the ranks to be shown as Rank/Total (Ex: 16/30)

I'm stucking at step 3. Now they have the same rank if their scores and YTD scores are tie.
For step 1&2, I use the formula: =RANK([@Score],[Score],0)+SUMPRODUCT(--([Score]=D4),--([YTD]>C4))&"/30"
Please refer to the sample as below.

Thanks so much for your help!

Sample.xlsx
	B	C	D	E
3	Project	YTD	Score	Rank
4	CV	85.1	87	8/30
5	Green	86.3	84	14/30
6	Happy	80.9	80	20/30
7	Happy Premier	86.6	87	7/30
8	HP	83.4	83	16/30
9	HP Premier	88.8	86	9/30
10	MD	89.5	92	2/30
11	NK	77.6	73	30/30
12	NP	82.8	79	22/30
13	PV	77.0	78	26/30
14	H24	87.1	87	6/30
15	P5	78.6	82	17/30
16	SSR	83.0	76	28/30
17	MD3	77.0	78	26/30
18	MD4	80.9	80	20/30
19	R1-2	80.9	85	13/30
20	R3	77.9	78	25/30
21	Cross	74.5	79	24/30
22	Star	85.1	84	15/30
23	S43	86.2	86	10/30
24	M5	86.0	86	11/30
25	M7	90.5	91	3/30
26	M6	90.6	93	1/30
27	H63	84.1	86	12/30
28	Amelie	88.6	91	4/30
29	Chateau	83.4	90	5/30
30	H22	83.6	81	18/30
31	HCN	78.0	76	29/30
32	R	77.0	79	23/30
33	S	79.8	81	19/30
Ranking (2)

Cell Formulas
Range	Formula
E4:E33	E4	=RANK([@Score],[Score],0)+SUMPRODUCT(--([Score]=D4),--([YTD]>C4))&"/30"

 

[KRice]

I would consider something like the approach shown below, which will require some explanation. You have 3 ranking/tie-breaking criteria and 30 projects. I would create a composite score based on mapping each ranking/tie-breaking criteria to powers of 10. Since you have 30 projects, you need at least 10^2 (100) positions to accommodate each ranking/tie-breaking criteria. These scoring positions need to be separated sufficiently so that the highest priority criterion (based on Score) cannot be affected by the 2nd highest priority criterion (based on YTD). Similarly, the YTD rankings cannot be affected by the randomization in the 3rd step, should it be necessary. So I propose a ranking composite score that looks like this:

=10000*RANK([@Score],[Score],0)+100*RANK([@YTD],[YTD],0)+RAND()

This formula ranks primarily by Score and weights the ranking by 10^4. Next a smaller component of the composite score is added, consisting of the rank by YTD weighted by 10^2. Because each of these constituent rankings range from 1..30, the weighting factors ensure that YTD rankings can not override the Score rankings. Then a small random number between 0..1 is weighted by 10^0 (i.e., 1) and added to the composite score. Finally, since all of the tie-breaking elements are accounted for in these composite scores, the composite scores are ranked from 1 to 30 using:

=RANK([@[Composite Score]],[Composite Score],1)

MrExcel_20220909b.xlsx
	B	C	D	E									N	O
3	Project	YTD	Score	Rank									Composite Score	Final Ranking
4	CV	85.13	87	8/30									61100.67	8/30
5	Green	86.25	84	14/30									140800.25	14/30
6	Happy	80.88	80	20/30									201900.69	21/30
7	Happy Premier	86.63	87	7/30									60700.24	7/30
8	HP	83.38	83	16/30									161500.23	16/30
9	HP Premier	88.75	86	9/30									90400.12	9/30
10	MD	89.5	92	2/30									20300.04	2/30
11	NK	77.63	73	30/30									302600.86	30/30
12	NP	82.75	79	22/30									221800.70	22/30
13	PV	77	78	26/30									252700.31	26/30
14	H24	87.13	87	6/30									60600.74	6/30
15	P5	78.63	82	17/30									172300.39	17/30
16	SSR	83	76	28/30									281700.92	28/30
17	MD3	77	78	26/30									252700.48	27/30
18	MD4	80.88	80	20/30									201900.60	20/30
19	R1-2	80.88	85	13/30									131900.69	13/30
20	R3	77.88	78	25/30									252500.68	25/30
21	Cross	74.5	79	24/30									223000.66	24/30
22	Star	85.13	84	15/30									141100.81	15/30
23	S43	86.2	86	10/30									90900.08	10/30
24	M5	86	86	11/30									91000.61	11/30
25	M7	90.5	91	3/30									30200.87	3/30
26	M6	90.63	93	1/30									10100.22	1/30
27	H63	84.13	86	12/30									91300.90	12/30
28	Amelie	88.6	91	4/30									30500.79	4/30
29	Chateau	83.38	90	5/30									51500.43	5/30
30	H22	83.63	81	18/30									181400.76	18/30
31	HCN	78	76	29/30									282400.57	29/30
32	R	77	79	23/30									222700.77	23/30
33	S	79.75	81	19/30									182200.42	19/30
Sheet3

Cell Formulas
Range	Formula
E4:E33	E4	=RANK([@Score],[Score],0)+SUMPRODUCT(--([Score]=D4),--([YTD]>C4))&"/30"
N4:N33	N4	=10000*RANK([@Score],[Score],0)+100*RANK([@YTD],[YTD],0)+RAND()
O4:O33	O4	=RANK([@[Composite Score]],[Composite Score],1)&"/30"

 

[Peter_SSs]

3. If their YTD scores are also a tie, just randomly assign their ranks

Would it be acceptable instead to make that final split based simply on which order the projects are in the table? If so you could try the following.
(I have also taken out the hard-coded 30 denominator in case table rows are added/deleted.)

mii0502.xlsm
	B	C	D	E
3	Project	YTD	Score	Rank
4	CV	85.125	87	8/30
5	Green	86.25	84	14/30
6	Happy	80.875	80	20/30
7	Happy Premier	86.625	87	7/30
8	HP	83.375	83	16/30
9	HP Premier	88.75	86	9/30
10	MD	89.5	92	2/30
11	NK	77.625	73	30/30
12	NP	82.75	79	22/30
13	PV	77	78	26/30
14	H24	87.125	87	6/30
15	P5	78.625	82	17/30
16	SSR	83	76	28/30
17	MD3	77	78	27/30
18	MD4	80.875	80	21/30
19	R1-2	80.875	85	13/30
20	R3	77.875	78	25/30
21	Cross	74.5	79	24/30
22	Star	85.125	84	15/30
23	S43	86.2	86	10/30
24	M5	86	86	11/30
25	M7	90.5	91	3/30
26	M6	90.625	93	1/30
27	H63	84.125	86	12/30
28	Amelie	88.6	91	4/30
29	Chateau	83.375	90	5/30
30	H22	83.625	81	18/30
31	HCN	78	76	29/30
32	R	77	79	23/30
33	S	79.75	81	19/30
Sheet1 (2)

Cell Formulas
Range	Formula
E4:E33	E4	=RANK([@Score],[Score],0)+SUMPRODUCT(--([Score]=D4),--([YTD]>C4))+COUNTIFS(INDEX([Score],1):[@Score],[@Score],INDEX([YTD],1):[@YTD],[@YTD])-1&"/"&COUNT([Score])

 

[KRice]

I like Peter's suggestion for the 3rd level tie-breaker. Using the list position is less volatile than using RAND()...which is what I used above to add some jitter to the composite scores. With RAND(), you will find the ranks changing as the spreadsheet recalculates due to unrelated edits elsewhere. The list position is probably not random--as you might sort it according to various criteria--but it is reasonably stable. I also like the idea to generalize the formula to avoid a hardwired "30" denominator. Based on the approach I described for weighting, my formula would be limited to 99 projects before the potential exists for the 2nd tie-breaker to take priority over the 1st. Should that ever occur, one remedy is to change the weighting factors to 10^6 and 10^3. A variant of my original approach that adopts these ideas from Peter is here:

MrExcel_20220909b.xlsx
	B	C	D	E									N
3	Project	YTD	Score	Rank									Composite Score
4	CV	85.13	87	8/30									61100.03
5	Green	86.25	84	14/30									140800.07
6	Happy	80.88	80	20/30									201900.10
7	Happy Premier	86.63	87	7/30									60700.13
8	HP	83.38	83	16/30									161500.17
9	HP Premier	88.75	86	9/30									90400.20
10	MD	89.5	92	2/30									20300.23
11	NK	77.63	73	30/30									302600.27
12	NP	82.75	79	22/30									221800.30
13	PV	77	78	26/30									252700.33
14	H24	87.13	87	6/30									60600.37
15	P5	78.63	82	17/30									172300.40
16	SSR	83	76	28/30									281700.43
17	MD3	77	78	27/30									252700.47
18	MD4	80.88	80	21/30									201900.50
19	R1-2	80.88	85	13/30									131900.53
20	R3	77.88	78	25/30									252500.57
21	Cross	74.5	79	24/30									223000.60
22	Star	85.13	84	15/30									141100.63
23	S43	86.2	86	10/30									90900.67
24	M5	86	86	11/30									91000.70
25	M7	90.5	91	3/30									30200.73
26	M6	90.63	93	1/30									10100.77
27	H63	84.13	86	12/30									91300.80
28	Amelie	88.6	91	4/30									30500.83
29	Chateau	83.38	90	5/30									51500.87
30	H22	83.63	81	18/30									181400.90
31	HCN	78	76	29/30									282400.93
32	R	77	79	23/30									222700.97
33	S	79.75	81	19/30									182201.00
Sheet3 (2)

Cell Formulas
Range	Formula
E4:E33	E4	=RANK([@[Composite Score]],[Composite Score],1)&"/"&COUNT([Score])
N4:N33	N4	=10000*RANK([@Score],[Score],0)+100*RANK([@YTD],[YTD],0)+ROWS(N$4:N4)/COUNT([Score])

 

[mii0502]

KRice: Thank you so much for your detailed explanation, I'll definitely look more into the math behind it.
Peter_SSs: Thanks for your solution, it worked just like I wanted.

 

[Peter_SSs]

Peter_SSs: Thanks for your solution, it worked just like I wanted.

You're welcome. Thanks for the confirmation.

.. using RAND()...

Just one other comment on the use of RAND for splitting the ties. It would be extremely unlikely, but theoretically possible that two or more of such RAND calculations could produce the same result so it does not give an absolute guarantee that ties would be split.

 

[KRice]

It would be extremely unlikely, but theoretically possible

I chuckled when I read that, Peter. True, but that is more of a theoretical concern than a practical one. I suppose Excel's numeric precision limitations also contribute because a continuous random variable becomes discretized due to those precision limits. I'm mostly opposed to the use of RAND() due to its volatility. A user of the worksheet might be in the process of using the rankings for something only to discover that the displayed ranks no longer match the earlier results. That's why some other scheme for the 3rd tie-breaker makes sense. For as often as a 3rd tie-breaker might be needed, one could rely on the good old-fashioned coin flip and simply resolve the tie manually. For an automated solution, list position certainly leads to a resolution, although one that still might be challenged by someone arguing that the wrong sort order was used. I don't know if the Score and YTD values are rounded to some absolute precision, or how those values are determined, but a 3rd tie-breaker could consider examining those values with higher precision, the distribution of values comprising those scores, or something else related to them.

KRice: Thank you so much for your detailed explanation, I'll definitely look more into the math behind it.

You're welcome...we're happy to help. As for the math, my approach still relies on determining the rank for each project for each of the first two tie-breakers. So for tie-breaker #1, we'll have ranks 1-30. These are mapped to large numbers by multiplying by 10^4, so the ranks assume values of 10000, 20000, 30000, 40000,..., 300000. For tie-breaker #2, we'll again have ranks 1-30, but these get mapped to smaller numbers by multiplying by 10^2, so the ranks assume values of 100, 200, 300, 400,..., 3000. Note that even the largest value here (3000) does not exceed the interval between adjacent scores for tie-breaker #1 (those intervals are 10000), so we can safely add the mapped scores without changing the rankings. The 3rd tiebreaker then uses some arbitrary scheme to add a small amount to each sum. And if we're using list position, I've divided the list position by the total number of projects , so these values are between 1/n and 1...still such a small amount that the 3rd tie-breaker does not interfere with the rankings established by the 1st and 2nd tie-breakers. Looking at this now, the division by n really isn't necessary...we could just as well add the list position 1, 2, 3,..., 30 to the score and that too would work fine, as the largest value (30) is not large enough to exceed the interval of 100 in the 2nd tie-breaker scoring. Knowing how the composite scores are created, one can decipher the ranks for each tie-breaker component. Taking Project MD with a composite score of 20300.23, we can tell that:

• it ranks 2 for tie-breaker 1 (ignore the 4 digits to the left of the decimal point and see what remains to the left),
• it ranks 3 for tie-breaker 2 (ignore the two digits to the left of the decimal point and look at the next two digits to the left "03"),
• and for the 3rd tie-breaker, we look at the 1's place and the decimal component (0.23), and from this, we can see that this project is in the 7th list position (0.23*30=6.9, note the 0.23 is display-rounded to a precision of 0.01).

 

[jimrward]

Can the end value to rank not be score + (ytd/100)
so for cv it’s 87.8513 this would give a clear ranking apart from a dead heat which resolve as original list order
then rank by what ever method you prefer rank or sumproduct

 

[KRice]

Can the end value to rank not be score + (ytd/100)

Interesting idea...yes, that would work too--a variation of the approach I described except the tie-breaking tiers aren't ranked initially. Instead, their original scores are used directly and mapped to certain decimal locations. The only potential issue is incorporating the 3rd tie breaker, which would need to be an even smaller decimal value that would not conflict with the 2nd tie-breaker score. I've made a comparison of these two mapping ideas below. In the "K" version, I've revised the 3rd tie-breaker to occupy the rightmost 2 digits (10's and 1's place values) so than the ranks/list position can be read directly from the composite score. In the "J" version, I've divided the list position by 10^8 to shift it far to the right of the decimal. The only difference in final rankings is due to the 3rd tie-breaker where the "K" version effectively gives priority to the uppermost of a tied pair while the "J" version gives priority to the lowermost (either option could be justified).

MrExcel_20220909b.xlsx
	B	C	D	E										O	P	Q	R
3	Project	YTD	Score	Rank										K_CompScore	K_Rank	J_CompScore	J_Rank
4	CV	85.125	87	8/30										61101	8	87.85	8
5	Green	86.25	84	14/30										140802	14	84.86	14
6	Happy	80.875	80	20/30										201903	20	80.81	21
7	Happy Premier	86.625	87	7/30										60704	7	87.87	7
8	HP	83.375	83	16/30										161505	16	83.83	16
9	HP Premier	88.75	86	9/30										90406	9	86.89	9
10	MD	89.5	92	2/30										20307	2	92.90	2
11	NK	77.625	73	30/30										302608	30	73.78	30
12	NP	82.75	79	22/30										221809	22	79.83	22
13	PV	77	78	26/30										252710	26	78.77	27
14	H24	87.125	87	6/30										60611	6	87.87	6
15	P5	78.625	82	17/30										172312	17	82.79	17
16	SSR	83	76	28/30										281713	28	76.83	28
17	MD3	77	78	27/30										252714	27	78.77	26
18	MD4	80.875	80	21/30										201915	21	80.81	20
19	R1-2	80.875	85	13/30										131916	13	85.81	13
20	R3	77.875	78	25/30										252517	25	78.78	25
21	Cross	74.5	79	24/30										223018	24	79.75	24
22	Star	85.125	84	15/30										141119	15	84.85	15
23	S43	86.2	86	10/30										90920	10	86.86	10
24	M5	86	86	11/30										91021	11	86.86	11
25	M7	90.5	91	3/30										30222	3	91.91	3
26	M6	90.625	93	1/30										10123	1	93.91	1
27	H63	84.125	86	12/30										91324	12	86.84	12
28	Amelie	88.6	91	4/30										30525	4	91.89	4
29	Chateau	83.375	90	5/30										51526	5	90.83	5
30	H22	83.625	81	18/30										181427	18	81.84	18
31	HCN	78	76	29/30										282428	29	76.78	29
32	R	77	79	23/30										222729	23	79.77	23
33	S	79.75	81	19/30										182230	19	81.80	19
Sheet3 (2)

Cell Formulas
Range	Formula
E4:E33	E4	=RANK([@[Composite Score]],[Composite Score],1)&"/"&COUNT([Score])
O4:O33	O4	=10^4*RANK([@Score],[Score],0)+10^2*RANK([@YTD],[YTD],0)+ROWS(N$4:N4)
P4:P33	P4	=RANK([@[K_CompScore]],[K_CompScore],1)
Q4:Q33	Q4	=[@Score]+[@YTD]/100+ROWS(Q$4:Q4)/10^8
R4:R33	R4	=RANK([@[J_CompScore]],[J_CompScore],0)

 

[jimrward]

You can also use RANK and COUNTIF to give unique ranking but it will give precedence to original place in list