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]

No, it doesn't work with decimals. I agree with Marcelo, the best way to do this is with a list of unique scores. I came up with a formula based way to get them (see columns I and J below). Then I also realized Marcelo's use of COUNTIF is better than the SUMPRODUCT I had been using.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1	1	Competitor Number	Score	Rank	Competitor Number	Score	Rank		ColC	ColF
2	2	27	70	5	12	60	6		70
3	3	13	74	3	8	72.87	4		74	72.87
4	4	41	76	2	38	76	2		76
5	5				12	60	6
6	6	14	86.5	1					86.5
7	7	31	60	6	13	86.5	1		60
8
Sheet18

Cell Formulas
Range	Formula
I2:I7	I2	=IF(C2="","",IF(MATCH(C2,$C$2:$C$7,0)=ROW($C2)-ROW($C$2)+1,C2,""))
J2:J7	J2	=IF(F2="","",IF(AND(MATCH(F2,$F$2:$F$7,0)=ROW($F2)-ROW($F$2)+1,COUNTIF($C$2:$C$7,F2)=0),F2,""))
D2:D7,G2:G7	D2	=IF(C2="","",COUNTIF($I$2:$J$7,">"&C2)+1)