Ranking

[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]

It woeks fine on my computer
if you put in a -360 and a plus 360 what do you get for a rank?
If you close out of Excel and restart and on a new sheet put you numbers
what do you get.

I think you have at least a corrupted worksheet. Hopefully your Excel
master is not corupted.

 

[Guest]

Not corrupted did a new one and still does not give me what I want

They way I want it to rank is as follows.
As you can see the most negative number is the highest rank. and once
positve the higher the positive number the higher the rank

 

[Guest]

I tried wwhat you did and got the same result.

One possible solution: You could create a dummy column with the absolute
value of your number (=ABS(XX)), then rank the absolute values. If you don't
want to see the column of absolute values, hide the column.

 

[Guest]

The ABS came up. The problem is a -250 should be ranked higher than +250,
using ABS they are equal. The -250 Should be ranked higher than the +250. If
there were a +300 thrown into the mix the ranking needs to be in this order,
-250,300,250

 

[Morrigan]

Assume A1:A9 is your data, try this:

B1
RANK(A1,A$1:A$9,1)+IF(A1>=0,2*(COUNTIF(A$1:A$9,">0")-RANK(A1,A$1:A$9,1)),0)


Hope it helps.

 

[Domenic]

Assuming that A1:A19 contains your numbers, try...

B1, copied down:

=IF(A1>=0,RANK(A1,$A$1:$A$9)+COUNTIF($A$1:$A$9,"<0"),RANK(A1,$A$1:$A$9,1)
)

Hope this helps!

 

[Guest]

Is this the way you want it to rank or the way it is ranking?

I don't know what is happening, but a work around is to try a helper column
with
=A1-Min(A:A)
copy down to the end of the data and rank the helper column.