Ranking without skipping ranks and percentile
 

Dear Members,


The following is the table from where I am trying retrieve the desired data into 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

Ranking without skipping ranks and percentile
 

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

Ranking without skipping ranks and percentile
 

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

Ranking without skipping ranks and percentile
 

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



On Monday, 25 March 2013 20:38:12 UTC+5:30, GS wrote:
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