Need to rank values with ties

[MichaelZ]

Could someone provide a duplicate ranks with decimal fractions algorithm. I
need to rank data that has tied values, but I want a ranking that is unique
for each value, so I need to rank the data using decimals, (eg., 1, 2, 3.1,
3.2, 4, etc.).
Thanks in advance

 

[Pete_UK]

Chip Pearson explains it very well here:

http://www.cpearson.com/excel/rank.aspx

Hope this helps.

Pete

 

[HARSHAWARDHAN. S .SHASTRI]

Hi Michael z,

Suppose your data is in cell a2:a100 ,then do one thing sort above data
in descending order.

Put following formula in cell b2 and copy down.

=COUNTIF($A$2:$A$100,A2)

then put 1 in cell c2 and put following formula in cell c3 and copy down.

=IF(AND(A2<>A1,A2=A3),INT(C1)+1.1,IF(A2=A1,C1+0.1,INT(C1)+1))

You will get what you looking for.

Harshawardhan . Shastri

India

 

[Mike H]

Hi,

With your data in a1 - A20 put this in b1 and drag down

=SUMPRODUCT(($A$1:$A$20>$A$1:$A$20
1:1)*(1/COUNTIF($A$1:$A$20,$A$1:A$20)))+(COUNTIF(INDEX($A$1:$A$20,1):INDEX($A$1:$A$20,ROW()-MIN(ROW($A$1:$A$20))+1),$A$1:$A$20)-1)/10+1.1

Mike

 

[Rick Rothstein]

I can get you part way to what you asked for. You request is for 1, 2, 3.1,
3.2, 4... the 4 is the problem with my formula below... it will give the
next item after the 3.1 and 3.2 the rank of 5 (because the 3.1 and 3.2 take
the place of the 3/4 tie, so the next rank after them is 5, not 4). If that
is OK, here is the formula...

=RANK(A1,A$1:A$15,TRUE)&IF(SUMPRODUCT(--(RANK(A$1:A$11,A$1:A$15)=RANK(A1,A$1:A$15)))>1,"."&SUMPRODUCT(--(RANK(A$1:A1,A$1:A$15)=RANK(A1,A$1:A$15))),"")

 

[Rick Rothstein]

Here is my previously posted formula in a little simpler format...

=RANK(A1,A$1:A$20)&IF(SUMPRODUCT(--(RANK(A$1:A$20,A:A)=RANK(A1,A:A)))>1,"."&SUMPRODUCT(--(RANK(A$1:A1,A:A)=RANK(A1,A:A))),"")

And, of course, change the A$20 reference to the last cell of data you have.

 

[Mike H]

Rick,

I got them sequential but can't get rid of the .1 when there is only 1
instance of a number. I've changed mine to sort in the same order as yours
and the comparative outputs are below. Can you get rid of the .1 for a single
instance in my formula, I've got no idea how and it's driving me nuts??

There's a typo in the second rank statement of your formula.

=SUMPRODUCT(($A$1:$A$15<$A$1:$A$15
1:1)*(1/COUNTIF($A$1:$A$15,$A$1:A$15)))+(COUNTIF(INDEX($A$1:$A$15,1):INDEX($A$1:$A$15,ROW()-MIN(ROW($A$1:$A$15))+1),$A$1:$A$15)-1)/10+1.1

Values Yours Mike
15 15 11.1
14 14 10.1
13 11.1 9.1
13 11.2 9.2
13 11.3 9.3
12 10 8.1
11 9 7.1
10 8 6.1
9 7 5.1
8 5.1 4.1
8 5.2 4.2
7 4 3.1
6 2 2.1
6 2 2.2
5 1 1.1

Mike

 

[Rick Rothstein]

How about this?

=SUMPRODUCT(($A$1:$A$20<$A$1:$A$20
1:1)*(1/COUNTIF($A$1:$A$20,$A$1:A$20)))+(COUNTIF(INDEX($A$1:$A$20,1):INDEX($A$1:$A$20,ROW()-MIN(ROW($A$1:$A$20))+1),$A$1:$A$20)-1)/10+1+0.1*(SUMPRODUCT(--(RANK(A$1:A$20,A:A)=RANK(A1,A:A)))>1)

 

[Mike H]

Perhaps,

=IF(COUNTIF($A$1:$A$15,A1)=1,SUMPRODUCT(($A$1:$A$15<$A$1:$A$15
1:1)*(1/COUNTIF($A$1:$A$15,$A$1:A$15)))+1,SUMPRODUCT(($A$1:$A$15<$A$1:$A$15
1:1)*(1/COUNTIF($A$1:$A$15,$A$1:A$15)))+(COUNTIF(INDEX($A$1:$A$15,1):INDEX($A$1:$A$15,ROW()-MIN(ROW($A$1:$A$15))+1),$A$1:$A$15)-1)/10+1.1)

Mike

 

[Mike H]

Rick,

Nice one, I cracked it a different way in my other post

Mike