Rank

[Richard from Atlanta]

How can I get the rank value for a list of values, without
sorting the items...I have a bunch of cities for example,
and and various metrics that I want to rank, and then
average the ranks...This is a pain as I have to treat each
metric/city combo as it own...

 

[Domenic]

I'm not sure that this is what you want, but here goes...

Assuming that the cities are listed in Column A and their corresponding
values in Column B...

C1, copied down:

=SUMPRODUCT(($A$1:$A$100=A1)*(B1<$B$1:$B$100))+1

Then list the unique cities in a column, let's say Column E, and enter
the following array formula in F1 and copy down:

=AVERAGE(IF($A$1:$A$100=E1,$C$1:$C$100))

....entered using CONTROL+SHIFT+ENTER.

Hope this helps!

 

[richard from altlanta]

Hmmm..

I tried this, but it is not working...let me try to
explain better...

I have 20 cities down col a
I have 10 variables down col b - k
each city has a value for each variable
the systems are sorted alpha
I want to look at the variables b - k and rank them in col
l - u.
Which system ranks highest/lowest in variable b, c, d,
e,...
This does not work because excel requires that the
variables be sorted, and if I sort var b, var c is not
sorted properly, etc...

 

[Domenic]

I'm still not clear as to what you're looking for. Try...

Highest to lowest...

L1, copied across and down:

=RANK(B1,B$1:B$20)

Lowest to highest...

=RANK(B1,B$1:B$20,1)

If this is not what you're looking for, post (in plain text) a sample of
your data (about 5 rows X 5 columns) and your expected results.

 

[JWolf]

=SUM(--(B$1:B$20<=B1)) entered as an array formula (ctrl+shift+enter) in
L1 and filled to U20 should do the trick.

 

[JWolf]

{=SUM(--(B$1:B$20<B1))+1} will work better in case of ties, each will
get lowest score.