Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Ranking
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 -- Tim Sorry about the duplicate post in another area -- Tim |
#2
|
|||
|
|||
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. "Tim Sullivan" wrote: 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 -- Tim Sorry about the duplicate post in another area -- Tim |
#3
|
|||
|
|||
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. Rank Number 1 -360 2 -250 3 75 4 50 5 22 6 9 7 4.5 8 .06 9 0 As you can see the most negative number is the highest rank. and once positve the higher the positive number the higher the rank -- Tim "bj" wrote: 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. "Tim Sullivan" wrote: 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 -- Tim Sorry about the duplicate post in another area -- Tim |
#4
|
|||
|
|||
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. "Tim Sullivan" wrote: 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 -- Tim Sorry about the duplicate post in another area -- Tim |
#5
|
|||
|
|||
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 -- Tim "SVC" wrote: 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. "Tim Sullivan" wrote: 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 -- Tim Sorry about the duplicate post in another area -- Tim |
#6
|
|||
|
|||
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. Tim Sullivan Wrote: 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 -- Tim Sorry about the duplicate post in another area -- Tim -- Morrigan ------------------------------------------------------------------------ Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094 View this thread: http://www.excelforum.com/showthread...hreadid=388021 |
#7
|
|||
|
|||
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! In article , Tim Sullivan wrote: 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 -- Tim Sorry about the duplicate post in another area |
#8
|
|||
|
|||
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. "Tim Sullivan" wrote: 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. Rank Number 1 -360 2 -250 3 75 4 50 5 22 6 9 7 4.5 8 .06 9 0 As you can see the most negative number is the highest rank. and once positve the higher the positive number the higher the rank -- Tim "bj" wrote: 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. "Tim Sullivan" wrote: 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 -- Tim Sorry about the duplicate post in another area -- Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numbers used for Ranking | Excel Worksheet Functions | |||
Ranking Using Grand Total | Excel Discussion (Misc queries) | |||
Ranking | Excel Discussion (Misc queries) | |||
ranking an row of values | Excel Worksheet Functions | |||
Problem with ranking numbers | Excel Worksheet Functions |