how can i rank a column in excel

[Guest]

i have a 10 columns of about 100 rows. I am trying to rank each column.
example.

column A has 5 rows. and each row has a number. I want column B to rank
them.
A B

1 50.2 4
2 37.1 1
3 40.1 2
4 40.1 2
5 50.1 3 the way ive been
doing it is sort the column then maunally rank them in order. I gets
tiresome doing over 100 mumbers per column.
Thanks for your time..

 

[Domenic]

Try...

B1, copied down:

=SUM(IF(A1>$A$1:$A$5,1/COUNTIF($A$1:$A$5,$A$1:$A$5)))+1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Guest]

check the rank() function in help

 

[Max]

One play to try ..

Adapted from a post by Daniel M.
(re: http://tinyurl.com/8snkd )

Assuming the source numbers are in col A, in A1 down

Put in B1 and array-enter the formula,
i.e. press CTRL+SHIFT+ENTER:

=IF(A1="","",RANK(A1,INDIRECT("A1:A"&COUNT(A:A)),1)-(COUNTIF(INDIRECT("A1:A"
&COUNT(A:A)),"<"&A1)-SUM((1/COUNTIF(INDIRECT("A1:A"&COUNT(A:A)),INDIRECT("A1
:A"&COUNT(A:A))))*(INDIRECT("A1:A"&COUNT(A:A))<A1))))

Copy B1 down to say, B200, to cover the max expected data in col A
The desired "continuous" rankings will be returned in col B until the last
value in col B, with blank rows ("") thereafter

 

[Max]

Typo, sorry ..

Line

.. until the last value in col B, with blank rows ("") thereafter

should read as: