Ranking order

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

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?


"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

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


"Peter Ellis" wrote:

> 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?
>
>
> "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

if all else fails, you can add 10000 to all the numbers first and rank THAT.
"Tim Sullivan" <TimSullivan@discussions.microsoft.com> wrote in message
news:A321356D-DD77-477F-B7BD-079076B039DC@microsoft.com...
>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

On Mon, 18 Jul 2005 08:22:02 -0700, Tim Sullivan wrote:
>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-
--
"Learning is a behavior that results from consequences."
B.F. Skinner

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
--
Excel 2002