Ranking without skipping ranks and percentile

[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

 

[GS]

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

 

[GS]

Typo in formula! It should be...

=$A3/$A$2

...where scores list starts at row2.

--
Garry

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

 

[Jee]

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