Ranking (ties)

**ExcelUser45** · 03-13-2008, 11:26 AM

I have data that ranks results like this....

1
2
3
3
5
6
7
8
8
8
11
12
13

What I would like it to show is.....
1
2
3.5
3.5
5
6
7
9
9
9
11
12
13

I've been trying to use the built in help on Excel and I've researched on google. I can't seem to make it work. Can someone give me a Ranking with Ties for Dummies version of how to get this happening?

Thanks!

**NBVC** · 03-13-2008, 11:35 AM

Check here for samples on Ranking.

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

**darkyam** · 03-13-2008, 11:45 AM

If the original values are in A1:A13 and the ranked values you showed are in B1:B13, add +(COUNTIF(A$1:A$13,A1)-1)/2 to the end of the ranking formula in B1 and drag down.

**ExcelUser45** · 03-13-2008, 12:32 PM

Originally Posted by darkyam

If the original values are in A1:A13 and the ranked values you showed are in B1:B13, add +(COUNTIF(A$1:A$13,A1)-1)/2 to the end of the ranking formula in B1 and drag down.

Thanks. This helped but I still needed to do some rigging because column had has a variable amount of entries and the columns that don't have anything show a 0. Therefore the COUNTIF statement is counting too many cells as having data. So I had to change that statement to reflect the difference.