Ranking percentages

[mandyfsu]

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.

 

[Aladin Akyurek]

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

 

[mandyfsu]

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.

 

[Aladin Akyurek]

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

 

[mandyfsu]

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

 

[Aladin Akyurek]

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.