RANK FUNCTION

[richard from atlanta]

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

 

[RagDyeR]

I don't know if I completely understand what you're asking.

When you say "value for each variable", I assume you mean a number.

With labels in Row1,
And data in A2 to K21,
Try this in L2:

=INDEX($A$2:$A$20,MATCH(MIN(B2:B20),B2:B20,0))

Then drag across to copy to U2.

This will give you the cities with the highest rating for each variable,
assuming number one is the best (highest).

To return the city with the lowest rating, simply change the MIN() to MAX():

=INDEX($A$2:$A$20,MATCH(MAX(B2:B20),B2:B20,0))

If my guessing is off, post back with additional information and/or
descriptions.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



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

 

[richard from altanta]

Well - that is close - what I am looking for and must not
be explaining it well is to find a way to use/create the
rank function and not have to sort the data first.
Getting the min and max is good, but I need all other
values in between as well. IN addition, I need the
numeric values so I can take averages...

For example, when money magazine ranks the 10 best cities
to live, it does so by picking several variables (crime,
cost of living, etc), ranking each city against those
variables, and then rank the best cities based on the best
average score...This is very similar to that.

 

[RagDyeR]

What gives you the impression that your data has to be sorted in order for
Rank() to work correctly?

It works with data in any order !

Maybe you're forgetting to use the third argument if necessary.
OR,
You ARE using the third argument, BUT using it *incorrectly*.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

message Well - that is close - what I am looking for and must not
be explaining it well is to find a way to use/create the
rank function and not have to sort the data first.
Getting the min and max is good, but I need all other
values in between as well. IN addition, I need the
numeric values so I can take averages...

For example, when money magazine ranks the 10 best cities
to live, it does so by picking several variables (crime,
cost of living, etc), ranking each city against those
variables, and then rank the best cities based on the best
average score...This is very similar to that.

 

[Guest]

Got it thanks!

-----Original Message-----
What gives you the impression that your data has to be sorted in order for
Rank() to work correctly?

It works with data in any order !

Maybe you're forgetting to use the third argument if necessary.
OR,
You ARE using the third argument, BUT using it *incorrectly*.
----------
Please keep all correspondence within the Group, so all may benefit !
---------------------------------------------------------- ----------

"richard from altanta"