Aggregate/subset PercentRank() functionality

[mountainclimber11]

We use PERCENTILERANK.INC() in Excel with no issues. The columns below labeled "Pctl Inc" use this formula:

=PERCENTRANK.INC($B$2:$B$6,B2,3)

However, in other cases we are looking for subset percent rank functionality (i.e. rank a subset of the values based on some other condition/filter). We have come close, but the functionality is slightly/significantly off.

My formula to achieve this is based off this link:
Calculate Percentile and Conditional Ranking in Excel Using SUMPRODUCT

Here is the specific formula I am referring to in that link:
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">

=(SUMPRODUCT(($A$2:$A$6=A2)*(B2>=$B$2:$B$6)))/COUNTIF($A$2:$A$6,A2)

</code>(Note that the site link has a typo in the formula and mine is the one you want to use to test.)

If everything is working properly you will get ranks like this:

[TABLE="width: 307"]
<tbody>[TR]
[TD]Subset Labels[/TD]
[TD]Values to Rk[/TD]
[TD]Pctl Inc[/TD]
[TD]Pctl W/in[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.5[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


"Subset Labels" -> tells you where to subset. In this sample there are 2 categories that we want to create subsets for: "a" and "b". That is, we want a percentile rank of all the a's amongst the the a's only. And the same for the b's.
"Values to Rk" -> the values that should be ranked.
"Pctl Inc" -> is just a regular PERCENTRANK.INC() formula. See formula above.
"Pctl W/in" -> this is the column/ranks I am trying to create via a percentile rank "within" the subset. See formula above. The ranks in the table above were manually typed in and do NOT reflect the subset example formula above.

One way to easily test your formula is to change the subset labels so they are all the same and compare the resulting ranks to the a regular PERCENTRANK.INC() formula. In that case (all of the subset labels are the same) if the subset column is correct you should get values like these:

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"]Subset Labels[/TD]
[TD="width: 64"]Values to Rk[/TD]
[TD="width: 64"]Pctl Inc[/TD]
[TD="width: 64"]Pctl W/in[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"]0.4[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.6[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]-1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.2[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]0.8[/TD]
[/TR]
</tbody>[/TABLE]

So, the goal is to emulate the PERCENTRANK.INC() formula results, but on a subset/aggregated basis. Again the Pctl W/in column above was hand typed in, but it is what we want.

It's a little confusing, but hopefully it is clear enough.

For all formulas and tables in this post the first cell "Subset Labels" is A1.

 

[barry houdini]

If there is only one of the subset type (ex. only one "b") a #DIV/0 is returned

Ah, yes, well spotted!

The array formula doesn't have that flaw, it will give you 1 in any case - just for interest, why can't you use array formulas here?

 

[mountainclimber11]

Ah, yes, well spotted!

The array formula doesn't have that flaw, it will give you 1 in any case - just for interest, why can't you use array formulas here?

Well, it's complicated. It's more of a "I don't want" than a "I can't". My client is a hedge fund and the system I am working on is a legacy quant model that has a lot of code (VBA) and other systems associated with it that I have built over the last 3 years. I don't know what would break if I introduced array formulas and breaking is not an option! I tried it once about a year ago and things blew up so I have stayed away from it. Put another way, I'd rather have a little bit funky ranks than debug array formulas all day. I have limited time and I have to pick and choose what I work on. So its not really laziness....I promise!

Thanks again!

 

[mountainclimber11]

Impressive. Thank you! Even better. I thought I'd test it a bit more as well and found a minor flaw with both solutions:

If there is only one of the subset type (ex. only one "b") a #DIV/0 is returned, so I think this maybe the best formula to use:

=IFERROR(1-(COUNTIFS(A$2:A$6,A2,B$2:B$6,">="&B2)-1)/(COUNTIF(A$2:A$6,A2)-1),1)

... which is just barry's formula wrapped in IFERROR -> 1, which is what PERCENTRANK.INC() does when there is just one item to rank.

Thanks guys!

IMPORTANT!!!!! on either side of the inequality do not leave any spaces. Example this will NOT work " >= ", but this will work ">=".