Ranking
 

Hi all,

Trying to figure out the "Rank" function and not doing well.
I have a column of data formated as % and would like to see what the rank
each one is without sorting them assending. If some one can just explain the
use of the rank function I would appreciate it. Unless of course there is a
better way!
xl2k
130.64% 2
167.39% 10
187.91% 14
139.48% 6
130.86% 3
126.51% 1
203.13% 15
139.04% 5
214.37% 16
181.59% 11
185.17% 13
182.76% 12
257.97% 17
165.81% 8
133.17% 4
166.64% 9
142.08% 7

I did this the "HARD" way and still not real confident they are right. Need
a formula.

Mike R.

The rank function takes 3 arguments. The fist is the number you wish to rank,
the second is the range to compare that number to and the third determines if
you want the comparison done ascending or descending.

So assuming your data is in the range A3:A19 you would enter the following
formula in cell B3 and then copy it down.

=RANK(A3,$A$3:$A$19,1)

The reference to A3 is relative so it will change as you copy down. In each
case this is the number you are ranking. The range is absolute and stays
fixed in each case. The final 1 states that you want to order ascending i.e.
the lowest value will have a rank of 1. For the highest value having a rank
of one you would enter 0 here.

Hope this helps
Rowan

"Mike R" wrote:

Hi all,

Trying to figure out the "Rank" function and not doing well.
I have a column of data formated as % and would like to see what the rank
each one is without sorting them assending. If some one can just explain the
use of the rank function I would appreciate it. Unless of course there is a
better way!
xl2k
130.64% 2
167.39% 10
187.91% 14
139.48% 6
130.86% 3
126.51% 1
203.13% 15
139.04% 5
214.37% 16
181.59% 11
185.17% 13
182.76% 12
257.97% 17
165.81% 8
133.17% 4
166.64% 9
142.08% 7

I did this the "HARD" way and still not real confident they are right. Need
a formula.

Mike R.

Rowan
Thanks you for the post...
It was the absolute reference that did it!!!! Thanks for the help and the
contribution to my continuing education.

Mike R.


"Rowan" wrote:

The rank function takes 3 arguments. The fist is the number you wish to rank,
the second is the range to compare that number to and the third determines if
you want the comparison done ascending or descending.

So assuming your data is in the range A3:A19 you would enter the following
formula in cell B3 and then copy it down.

=RANK(A3,$A$3:$A$19,1)

The reference to A3 is relative so it will change as you copy down. In each
case this is the number you are ranking. The range is absolute and stays
fixed in each case. The final 1 states that you want to order ascending i.e.
the lowest value will have a rank of 1. For the highest value having a rank
of one you would enter 0 here.

Hope this helps
Rowan

"Mike R" wrote:

Hi all,

Trying to figure out the "Rank" function and not doing well.
I have a column of data formated as % and would like to see what the rank
each one is without sorting them assending. If some one can just explain the
use of the rank function I would appreciate it. Unless of course there is a
better way!
xl2k
130.64% 2
167.39% 10
187.91% 14
139.48% 6
130.86% 3
126.51% 1
203.13% 15
139.04% 5
214.37% 16
181.59% 11
185.17% 13
182.76% 12
257.97% 17
165.81% 8
133.17% 4
166.64% 9
142.08% 7

I did this the "HARD" way and still not real confident they are right. Need
a formula.

Mike R.