Ranking without skipping ranks and percentile

  • Thread starter Thread starter Jee
  • Start date Start date
J

Jee

Dear Members,


The following is the table from where I am trying retrieve the desired datainto Rank and Percentile column


Marks Rank Percentile Score
90 1 100
60 2
32 3
30 4
30 5
28 6
28 7
28 8
26 9
26 10
26 11
26 12
26 13
26 14

What should be the formula / code to auto insert rank depends on marks? I have tried with RANK function but when the marks are the same it skips a rank. I also need percentile rank code for the same on the third column considering all values (percentile including x)

Please help me.

Thanks

Jee
 
Given how ranking works.., same score share the same rank. So...

90 1
60 2
32 3
30 4
30 4
28 5
28 5
28 5
26 6
26 6
26 6
26 6
26 6
26 6

...is how the results should display.

If the top score is the marker for percentile then set the NumberFormat
for colC to Percent and enter the following formula in C3...

=$A3/$A$1

...and copy down. The results are...

90 1 100%
60 2 67%
32 3 36%
30 4 33%
30 4 33%
28 5 31%
28 5 31%
28 5 31%
26 6 29%
26 6 29%
26 6 29%
26 6 29%
26 6 29%
26 6 29%

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Dear Garry,

Thanks for your response.

I have the data of ColA only. I would like to have a formula in ColB for Ranks (not percentile; highest first then the second highest etc., with same rank for same mark) and I need percentile ranks in ColC using the given formula below. Both ColB & Col C should be auto generated even if ColA is not sorted for highest to smallest.

90 1 100%

60 2 67%

32 3 36%

30 4 33%

30 4 33%

28 5 31%

28 5 31%

28 5 31%


To find the percentile rank of a score, x, out of a set of n scores, where x is included:

(B + 0.5 E) *(100/n) = Percentile Rank

Where B = number of scores below x
E = number of scores equal to x
n = number of scores

Please help me.

Thanks
Jee
 
Back
Top