How to rank a combination of 2 or more columns?

[JenniferMurphy]

The first 2 columns in the table below are 5-star ratings, such as used on Amazon. I would like to rank them as a combined field sorted the way it is (Rating column Hi-Lo, # Reviews column Hi-Lo).

I discovered that the Rank functions only work with numbers, so the only thing I could think of was to create a helper column that generated a combined integer text string and then convert that to a number.

Is there a better way -- perhaps one that doesn't need the helper column?

Ranking.xlsx
	C	D	E	F	G
5	Rating	# Reviews	Rank	Helper	Rank2
6	4.9	11		4900000011	1
7	4.7	123,620		4700123620	2
8	4.7	18,604		4700018604	3
9	4.7	1,759		4700001759	4
10	4.6	38,086		4600038086	5
11	4.6	21,068		4600021068	6
12	4.6	17,787		4600017787	7
13	4.6	7,795		4600007795	8
14	4.6	7,540		4600007540	9
15	4.6	7,489		4600007489	10
16	4.6	4,864		4600004864	11
17	4.6	4,487		4600004487	12
18	4.6	3,778		4600003778	13
19	4.6	3,246		4600003246	14
20	4.6	2,883		4600002883	15
21	4.6	2,399		4600002399	16
22	4.6	327		4600000327	17
23	4.6	46		4600000046	18
24	4.5	44,427		4500044427	19
25	4.5	9,567		4500009567	20
26	4.5	5,801		4500005801	21
27	4.5	456		4500000456	22
28	4.4	8,509		4400008509	23
29	4.1	43		4100000043	24
Sheet1

Cell Formulas
Range	Formula
F6:F29	F6	=VALUE(TEXT([@Rating]*10,"0") & TEXT([@['# Reviews]],"00000000"))
G6:G29	G6	=RANK.EQ([@Helper],[Helper])

 

[Phuoc]

Try this:

Book1
	C	D	E
5	Rating	# Reviews	Rank
6	4.9	11	1
7	4.7	123620	2
8	4.7	18604	3
9	4.7	1759	4
10	4.6	38086	5
11	4.6	21068	6
12	4.6	17787	7
13	4.6	7795	8
14	4.6	7540	9
15	4.6	7489	10
16	4.6	4864	11
17	4.6	4487	12
18	4.6	3778	13
19	4.6	3246	14
20	4.6	2883	15
21	4.6	2399	16
22	4.6	327	17
23	4.6	46	18
24	4.5	44427	19
25	4.5	9567	20
26	4.5	5801	21
27	4.5	456	22
28	4.4	8509	23
29	4.1	43	24
Sheet1

Cell Formulas
Range	Formula
E6:E29	E6	=COUNTIFS($C$6:$C$29,">"&C6)+COUNTIFS($C$6:$C$29,C6,$D$6:$D$29,">"&D6)+1

 

[Phuoc]

Or try:

=LET(a,C6:C29*10000000000+D6:D29/1000000000,b,SORT(a,,-1),MATCH(a,b,0))

 

[Phuoc]

Your numbers are already sorted, =ROWS($A$1:A1) Copy down

If 2 rows have the same "Rating" and "# Reviews", it will show different rankings.

 

[JenniferMurphy]

Try this:

Perfect. I'll have to study it a bit to figure out how it works. Any hits?

Here's the new table. I did modify your original code to use table syntax.

Ranking.xlsx
	C	D	E	F	G	H	I
5	Rating	# Reviews	Helper	Rank	Phuoc	Phuoc2	Huish
6	4.7	123,620	4.7E+09	2	2	2	1
7	4.5	44,427	4.5E+09	19	19	19	2
8	4.6	38,086	4.6E+09	5	5	5	3
9	4.6	21,068	4.6E+09	6	6	6	4
10	4.7	18,604	4.7E+09	3	3	3	5
11	4.6	17,787	4.6E+09	7	7	7	6
12	4.5	9,567	4.5E+09	20	20	20	7
13	4.4	8,509	4.4E+09	23	23	23	8
14	4.6	7,795	4.6E+09	8	8	8	9
15	4.6	7,540	4.6E+09	9	9	9	10
16	4.6	7,489	4.6E+09	10	10	10	11
17	4.5	5,801	4.5E+09	21	21	21	12
18	4.6	4,864	4.6E+09	11	11	11	13
19	4.6	4,487	4.6E+09	12	12	12	14
20	4.6	3,778	4.6E+09	13	13	13	15
21	4.6	3,246	4.6E+09	14	14	14	16
22	4.6	2,883	4.6E+09	15	15	15	17
23	4.6	2,399	4.6E+09	16	16	16	18
24	4.7	1,759	4.7E+09	4	4	4	19
25	4.5	456	4.5E+09	22	22	22	20
26	4.6	327	4.6E+09	17	17	17	21
27	4.6	46	4.6E+09	18	18	18	22
28	4.1	43	4.1E+09	24	24	24	23
29	4.9	11	4.9E+09	1	1	1	24
Sheet1

Cell Formulas
Range	Formula
E6:E29	E6	=VALUE(TEXT([@Rating]*10,"0") & TEXT([@['# Reviews]],"00000000"))
F6:F29	F6	=RANK.EQ([@Helper],[Helper])
G6:G29	G6	=COUNTIFS($C$6:$C$29,">"&C6)+COUNTIFS($C$6:$C$29,C6,$D$6:$D$29,">"&D6)+1
H6:H29	H6	=COUNTIFS([Rating],">"&[@Rating])+COUNTIFS([Rating],[@Rating],['# Reviews],">"&[@['# Reviews]])+1
I6:I29	I6	=ROWS($A$1:A1)

 

[JenniferMurphy]

Or try:

=LET(a,C6:C29*10000000000+D6:D29/1000000000,b,SORT(a,,-1),MATCH(a,b,0))

I couldn't get this to work.

 

[JenniferMurphy]

Your numbers are already sorted, =ROWS($A$1:A1) Copy down

This just gives the row number, not the rank. The rank will not change if the list is resorted or scrambled.

 

[Scott Huish]

This just gives the row number, not the rank. The rank will not change if the list is resorted or scrambled.

I know that, but that's not what the op posted.

 

[JenniferMurphy]

I know that, but that's not what the op posted.

Yes it is. I am the OP and I asked for a way to "Rank" them.

 

[JenniferMurphy]

I now have 3 quite different solutions as illustrated in the minisheet below. The task was to rank the first two columns, RatIngs & # Reviews, as if they were sorted on Ratings first, then on # Reviews.

Here's what the columns do:

The Helper and Helper Rank Columns were the only way I could think to make it work.

Then I asked here. Phuoc Rank1 is the solution Phuoc posted using cell syntax. Phuoc Rank2 is the version I modified to use table syntax.

I have been playing around with ChatGPT, so I asked it to solve this problem. After several rather obvious errors and misunderstandings, it came up with a solution using SumProduct with a combination of cell syntax for part of the expression and table syntax for the rest. I edited the expression so that ChatGPT Rank1 uses all cell syntax and ChatGPT Rank2 uses all table syntax.

The minisheet is sorted by the rank. In this view, it is sorted on the # Reviews column to show that the ranks all remain correct.

Ranking.xlsx
	C	D	E	F	G	H	I	J
5	Ratings	# Reviews	Helper	Helper Rank	Phuoc Rank1	Phuoc Rank2	ChatGPT Rank1	ChatGPT Rank2
6	4.9	11	4.9E+09	1	1	1	1	1
7	4.7	123,620	4.7E+09	2	2	2	2	2
8	4.7	18,604	4.7E+09	3	3	3	3	3
9	4.7	1,759	4.7E+09	4	4	4	4	4
10	4.6	38,086	4.6E+09	5	5	5	5	5
11	4.6	21,068	4.6E+09	6	6	6	6	6
12	4.6	17,787	4.6E+09	7	7	7	7	7
13	4.6	7,795	4.6E+09	8	8	8	8	8
14	4.6	7,540	4.6E+09	9	9	9	9	9
15	4.6	7,489	4.6E+09	10	10	10	10	10
16	4.6	4,864	4.6E+09	11	11	11	11	11
17	4.6	4,487	4.6E+09	12	12	12	12	12
18	4.6	3,778	4.6E+09	13	13	13	13	13
19	4.6	3,246	4.6E+09	14	14	14	14	14
20	4.6	2,883	4.6E+09	15	15	15	15	15
21	4.6	2,399	4.6E+09	16	16	16	16	16
22	4.6	327	4.6E+09	17	17	17	17	17
23	4.6	46	4.6E+09	18	18	18	18	18
24	4.5	44,427	4.5E+09	19	19	19	19	19
25	4.5	9,567	4.5E+09	20	20	20	20	20
26	4.5	5,801	4.5E+09	21	21	21	21	21
27	4.5	456	4.5E+09	22	22	22	22	22
28	4.4	8,509	4.4E+09	23	23	23	23	23
29	4.1	43	4.1E+09	24	24	24	24	24
Rank on 2 Columns (01)

Cell Formulas
Range	Formula
E6:E29	E6	=VALUE(TEXT([@Ratings]*10,"0") & TEXT([@['# Reviews]],"00000000"))
F6:F29	F6	=RANK.EQ([@Helper],[Helper])
G6:G29	G6	=COUNTIFS($C$6:$C$29,">"&C6)+COUNTIFS($C$6:$C$29,C6,$D$6:$D$29,">"&D6)+1
H6:H29	H6	=COUNTIFS([Ratings],">"&[@Ratings])+COUNTIFS([Ratings],[@Ratings],['# Reviews],">"&[@['# Reviews]])+1
I6:I29	I6	=SUMPRODUCT(--((C6*1000000 + D6) < ($C$6:$C$29*1000000 + $D$6:$D$29))) + 1
J6:J29	J6	=SUMPRODUCT(--(([@Ratings]*1000000 + [@['# Reviews]]) < ([Ratings]*1000000 + ['# Reviews]))) + 1