how can i rank a column in excel
 

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..

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!

In article ,
"David B." <David wrote:

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..

check the rank() function in help

"David B." wrote:

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..

One play to try ..

Adapted from a post by Daniel M.
( 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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"David B." <David wrote in message
...
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..

Typo, sorry ..

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

should read as:
.. until the last value in col A, with blank rows ("") thereafter
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--