Ranking order

[Guest]

I have a column with 24 numbers. I want to rank them. Using the standard
rank function works fine until one of the numbers is a negative.

They way I want it to rank is as follow
Rank Number
1 -360
2 -250
3 75
4 50
5 22
6 9
7 4.5
8 .06
9 0

The higher then negative number, the higher the rank. A negative number is
ranked higher than a positive number. A low positive number is ranked lower
than a high positive number. Does this make sense?????????

Any help wopuld be appreciated

 

[Guest]

You might want to conside the ABS function. This returns the absolute value
of a number - a number without its sign. I.e. the absolute value of 5 is 5.
The absolute value of -5 is still 5.

Use it in your comparisions. the absolute value of -360 is greater than
the absoute value of -250

Does this help?

 

[Guest]

That won't work. I may have a -250 and a 250. if I use ABS they are equal
when ranked. for my purpose the -250 should be ranked higher than the
positive 250

 

[Bob Umlas]

if all else fails, you can add 10000 to all the numbers first and rank THAT.

 

[Mangus Pyke]

That won't work. I may have a -250 and a 250. if I use ABS they are equal
when ranked. for my purpose the -250 should be ranked higher than the
positive 250

You could force it down a hundreth (or some increment less than your
smallest value).. which, doesn't exactly do wonders for the integrity
of your data, but would result in the ranking you want.

=IF(A1<0, ABS(A1)-.01,ABS(A1))

Technically, it would cause -250 to become 249.99, which would put it
lower than +250.

::chuckle::

That actually was a half-serious workaround.

MP-

 

[Guest]

Hi Tim,

I suggest - if your values are in column B:

C1:
=CHOOSE(SIGN(B1)+2,B1,1E+307,1/B1)

D1:
=RANK(C1,$C$1:$C$9,TRUE)

Copy C11 down to C99

HTH,
Bernd