Ranking

[Tim Sullivan]

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

[bj]

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

[Tim Sullivan]

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

[SVC]

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

[Tim Sullivan]

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

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

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

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

In article <[email protected]>,
Tim Sullivan <[email protected]> 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

[bj]

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