Rank help (without skipping numbers and skipping blanks)

[adurham22926192]

I need a formula to rank without skipping numbers. This is for a dance scoring software. In column C, I need to rank column B without skipping numbers so if say for example there is a 2 way tie for 4th (Rank: 4) it won’t go to 6 on the next rank it will go to 5. Also, i need the formula to skip the blank cells. Please help.

	A	B	C
1	Competitor Number	Score	Rank
2	27	70
3	13	80
4	41	76
5
6	14	74
7	31	60
8

 

[Eric W]

Try:

Book1 (version 1).xlsb
	A	B	C
1	Competitor Number	Score	Rank
2	27	70	2
3	13	80	4
4	41	74	3
5
6	14	74	3
7	31	60	1
8
Sheet14

Cell Formulas
Range	Formula
C2:C7	C2	=IF(B2="","",SUMPRODUCT(($B$2:$B$10<>"")*($B$2:$B$10<B2)*(MATCH($B$2:$B$10&"",$B$2:$B$10&"",0)=ROW($B$2:$B$10)-ROW($B$2)+1))+1)

 

[Eric W]

I just realized that I have it so that low scores rank higher. If it's the other way around, change the <B2 to >B2 about halfway through the formula.

 

[adurham22926192]

Thank

Try:

Book1 (version 1).xlsb
	A	B	C
1	Competitor Number	Score	Rank
2	27	70	2
3	13	80	4
4	41	74	3
5
6	14	74	3
7	31	60	1
8
Sheet14

Cell Formulas
Range	Formula
C2:C7	C2	=IF(B2="","",SUMPRODUCT(($B$2:$B$10<>"")*($B$2:$B$10<B2)*(MATCH($B$2:$B$10&"",$B$2:$B$10&"",0)=ROW($B$2:$B$10)-ROW($B$2)+1))+1)

Thank you but I want to rank it in descending order so like score 80 would have the rank of 1 not score 60

 

[Eric W]

I noticed that just a few minutes ago (post 3). Change the formula to:

=IF(B2="","",SUMPRODUCT(($B$2:$B$10<>"")*($B$2:$B$10>B2)*(MATCH($B$2:$B$10&"",$B$2:$B$10&"",0)=ROW($B$2:$B$10)-ROW($B$2)+1))+1)

 

[adurham22926192]

Okay so I need more help. I’ve realised that I need to rank it with two columns as the range. I will show below

	A	B	C	D	E	F
1	Competitor Number	Score	Rank	Competitor Number	Score	Rank
2	27	70		12	60
3	13	80
4	41	76		38	60
5				12	60
6	14	74
7	31	60

So in Cell C2 I want a rank formula to rank B2 value compared to the range B2:B7 and E2:E7.

 

[adurham22926192]

BUMP, please help

 

[Eric W]

Much trickier with 2 disjoint ranges:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	1	Competitor Number	Score	Rank	Competitor Number	Score	Rank
2	2	27	70	5	12	60	6
3	3	13	80	1
4	4	41	76	2	38	75	3
5	5				12	60	6
6	6	14	74	4
7	7	31	60	6
Sheet18

Cell Formulas
Range	Formula
D2:D7,G2:G7	D2	=IF(C2="","",SUMPRODUCT(--(C2<(IF(COUNTIF($C$2:$C$7,ROW(INDIRECT("1:101"))-1)+COUNTIF($F$2:$F$7,ROW(INDIRECT("1:101"))-1),ROW(INDIRECT("1:101"))-1,-1))))+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Also keep in mind that this formula will only work on integer scores from 0 to 100.

 

[adurham22926192]

Much trickier with 2 disjoint ranges:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	1	Competitor Number	Score	Rank	Competitor Number	Score	Rank
2	2	27	70	5	12	60	6
3	3	13	80	1
4	4	41	76	2	38	75	3
5	5				12	60	6
6	6	14	74	4
7	7	31	60	6
Sheet18

Cell Formulas
Range	Formula
D2:D7,G2:G7	D2	=IF(C2="","",SUMPRODUCT(--(C2<(IF(COUNTIF($C$2:$C$7,ROW(INDIRECT("1:101"))-1)+COUNTIF($F$2:$F$7,ROW(INDIRECT("1:101"))-1),ROW(INDIRECT("1:101"))-1,-1))))+1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Also keep in mind that this formula will only work on integer scores from 0 to 100.

Thank you so much. Does this work with decimals, say someone had a score of 86.5 would it still work?

 

[Marcelo Branco]

I think the easiest way is to create a list of Unique Scores. You can achieve this copying all the scores to an empty column and using Data > Remove Duplicates (see the gray area)

Then the formulas to get the rankings would be much simpler.

Pasta1
	A	B	C	D	E	F	G	H
1	Competitor Number	Score	Rank	Competitor Number	Score	Rank		Unique Scores
2	27	70	4	12	60	5		70
3	13	80	2					80
4	41	74	3	38	70	4		74
5				12	86,5	1
6	14	86,5	1					86,5
7	31	60	5					60
8
Plan2

Cell Formulas
Range	Formula
C2:C7	C2	=IF(B2="","",COUNTIFS(H$2:H$7,">"&B2)+1)
F2:F7	F2	=IF(E2="","",COUNTIFS(H$2:H$7,">"&E2)+1)

M.