Rank and Percentile

[John]

I have a long list of percentages that I need to rank. I ran a Rank
and Percentile on them and it came out, roughly, like this.

Percent Rank
97.2 1
96.1 2
95.0 3
95.0 3
95.0 3
94.2 6
93.5 7
93.5 7
92.0 9

In terms of the tied percents and ranks, I need it to look like this.

Percent Rank
97.2 1
96.1 2
95.0 3
95.0 3
95.0 3
94.2 4
93.5 5
93.5 5
92.0 6

What am I missing? Thanks very much.

John

 

[Bob Phillips]

John,

I don't think you can do that the highest rank of 9 being 6 is a bit odd.
What you can do is get a ranking list that has no duplicates if you use this
formula that Aladin Akyurek gave us

=RANK(A1,$A$1:$A$9)+COUNTIF($A$1:A1,A1)-1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Mark Graesser]

John,
You can do this is a new column. Say your Percents are in A1 thru A9, and your Ranks are in B1 thru B9. In cell C1 enter a 1. In cell C2 enter:

=IF(B2=B1,C1,C1+1)

Copy this formula down. This looks to see if the calculated ranks are equal and if the aren't it increments the new rank by 1.

The firast rank of one need to be entered manually. Also this will only work if the data is sorted by rank.

Good Luck,
Mark Graesser
(e-mail address removed)

----- John wrote: -----

I have a long list of percentages that I need to rank. I ran a Rank
and Percentile on them and it came out, roughly, like this.

Percent Rank
97.2 1
96.1 2
95.0 3
95.0 3
95.0 3
94.2 6
93.5 7
93.5 7
92.0 9

In terms of the tied percents and ranks, I need it to look like this.

Percent Rank
97.2 1
96.1 2
95.0 3
95.0 3
95.0 3
94.2 4
93.5 5
93.5 5
92.0 6

What am I missing? Thanks very much.

John

 

[John]

Mark's tip did the trick. Thank you both.