Results 1 to 6 of 6

Ranking percentages

This is a discussion on Ranking percentages within the Excel Questions forums, part of the Question Forums category; I am trying to rank a set of percentages that are positive and negative. I would like to treat the ...

  1. #1
    New Member
    Join Date
    Jan 2007
    Posts
    3

    Default Ranking percentages

    I am trying to rank a set of percentages that are positive and negative. I would like to treat the negative values as positive and rank accordingly. I can not figure out which formula to use. Please help.

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,244

    Default Re: Ranking percentages

    I am trying to rank a set of percentages that are positive and negative. I would like to treat the negative values as positive and rank accordingly. I can not figure out which formula to use. Please help.
    Create a 2nd range of data with ABS...

    =ABS(A2)

    and invoke in C2...

    =RANK(B2,$B$2:$B$25)

    Or, without an additional range, invoke:

    =SUMPRODUCT((ABS($A$2:$A$15)>ABS(A2))+0)+1

  3. #3
    New Member
    Join Date
    Jan 2007
    Posts
    3

    Default Re: Ranking percentages

    The formula worked, although it put them in order from descending rather than ascending. Can you explain how the formula actually works in case I need to modify it? Thanks for your help.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,244

    Default Re: Ranking percentages

    The formula worked, although it put them in order from descending rather than ascending. Can you explain how the formula actually works in case I need to modify it? Thanks for your help.
    For an ascending result, change > to <...

    =SUMPRODUCT((ABS($A$2:$A$15)>ABS(A2))+0)+1

    is qua form the same as

    =RANK(A2,$A$2:$A$15)

    which is, written in terms of CountIf...

    =COUNTIF($A$2:$A$15,">"&A2)+1

    The latter I guess is obvious. This can be re-written in terms of SumProduct...

    =SUMPRODUCT(($A$2:$A$15)>A2)+0)+1

    Since SumProduct admits arrays and

    ABS($A$2:$A$15)

    is an array, we can introduce ABS in the latter formula, an action that gives us:

    =SUMPRODUCT((ABS($A$2:$A$15)>ABS(A2))+0)+1

    For SumProduct, see:

    http://www.mrexcel.com/board2/viewtopic.php?t=59063

    http://www.mrexcel.com/board2/viewtopic.php?t=133602

  5. #5
    New Member
    Join Date
    Jan 2007
    Posts
    3

    Default Re: Ranking percentages

    Great. Thanks. Can you tell me what the 0 and 1 do at the end of the formula?

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,244

    Default Re: Ranking percentages

    Great. Thanks. Can you tell me what the 0 and 1 do at the end of the formula?
    +0 coerces the evaluations of the conditionals like (ABS($A$2:$A$15)>ABS(A2)) to 1's and 0's (TRUE--> 1; FALSE --> 0).

    +1 takes care of counting in the compared value. Otherwise, we would get a 0 rank for one of the values in the range of question. Put otherwise: It helps us to start ranking with 1 instead of 0, that is, R = 1,2,..,N instead of R = 0,1,2,...,N-1.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com