Rank with array of cells

M

Mad Ant

Hello all.

I have a problem using the RANK function with an array of cells.

I have numbers placed in (for example) cells A1:A5 and would like to
know their rank.
I select B1 and drag my mouse down to select the cells B1:B5.
I type "=rank(a1;$a$1:$a$5)" (without the quotes) and press
Ctrl+Shift+Enter.

What happens is that it gives me the rank of A1 in each of the cells of
b1:b5. It doesn't increment the value 'a1' so as to give the concording
rank.

So instead of having :
100 - 1
75 - 3
100 - 1
50 - 5
75 - 3

I have :
100 - 1
75 - 1
100 - 1
50 - 1
75 - 1

Does anybody know how I can have it that it gives the correct values ?

One solution is to only select the first cell, do the manuipulation and
copy the cell to the following cells, but I would much rather be able to
do it in one swoop.

Many thanks for any help you can provide.

Anton HARRIS
 
B

Bob Phillips

It is not an array formula, and should be entered en-bloc.

Just enter that formula in B1, and Enter. Then copy down to B5

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

Jerry W. Lewis

Or, if you want it as an array formula, then select all 5 and array enter
=RANK(A1:A5,A1:A5)

Jerry
 
B

Bob Phillips

But why? :))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

Jerry W. Lewis

RANK involves sorting the data, which would be O(n*ln(n)) at best, and
likely O(n^2). I do not know how MS programmed RANK with array input,
and have not done the timing comparisons, but there is the possibility
that an array formula would only have to do one sort instead of a
separate sort for each item to be ranked. Though with n=5, the machine
would have to be extremely slow for anyone to care.

Jerry
 
Top