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

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. ## Re: Ranking percentages

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

6. ## 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.

#### Posting Permissions

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