PC Review


Reply
Thread Tools Rate Thread

Ranking order

 
 
=?Utf-8?B?VGltIFN1bGxpdmFu?=
Guest
Posts: n/a
 
      18th Jul 2005
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
 
Reply With Quote
 
 
 
 
=?Utf-8?B?UGV0ZXIgRWxsaXM=?=
Guest
Posts: n/a
 
      18th Jul 2005
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

 
Reply With Quote
 
=?Utf-8?B?VGltIFN1bGxpdmFu?=
Guest
Posts: n/a
 
      18th Jul 2005
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

 
Reply With Quote
 
Bob Umlas
Guest
Posts: n/a
 
      18th Jul 2005
if all else fails, you can add 10000 to all the numbers first and rank THAT.
"Tim Sullivan" <(E-Mail Removed)> wrote in message
news:A321356D-DD77-477F-B7BD-(E-Mail Removed)...
>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



 
Reply With Quote
 
Mangus Pyke
Guest
Posts: n/a
 
      18th Jul 2005
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
 
Reply With Quote
 
=?Utf-8?B?QmVybmQgUGx1bWhvZmY=?=
Guest
Posts: n/a
 
      18th Jul 2005
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: Ranking (Correct Order) Michel Walsh Microsoft Access Queries 1 5th May 2009 02:49 PM
RE: Ranking (Correct Order) KARL DEWEY Microsoft Access Queries 0 4th May 2009 04:46 PM
Order the names by ranking Alexandra Lopes Microsoft Excel Worksheet Functions 8 17th Nov 2008 11:27 AM
Ranking order of cells. =?Utf-8?B?SnJKb3NlcGg=?= Microsoft Excel Misc 9 20th Aug 2007 12:50 AM
Ranking in order =?Utf-8?B?dHJ5bicnJycybGVhcm4=?= Microsoft Excel Misc 2 22nd May 2006 10:07 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:39 PM.