Find the percentile of a value within a range of cells that meet a certain condition

[OD1995]

I am trying to find the percentile of a value within a range of cells that meet a certain condition.

example1.PNG

Using the image above as an example, I'd like to calculate the percentile of certain value (doesn't matter which value for this) for the range of cells where column B has the value 1.

Calculating percentile is not an issue (use the PERCENTRANK function) but turning that list of 16 numbers into an array of just the 11 with the value of 1 in column B is what I'm finding difficult.

I believe some combination of the MATCH and INDEX function could do it, but I have not found a way that works. I realise that there is possibly an easy(/easier) solution if VBA or SQL is used, but I would prefer (if possible) a solution that just uses Excel functions.

Thank you!

[MrShorty]

It seems that most use the IF() function to create a new array that replaces the unwanted values with FALSE or some text string which the PERCENTRANK() function should ignore.

To see what is going on, add a helper column for column C. Something like =IF($B2=1,$A2,FALSE) and copy down, then use this helper column in your PERCENTRANK() function.

When you are comfortable with how that works, then you can nest the IF() array function inside of your PERCENTRANK() function =PERCENTRANK(IF(B2:B17=1,A2:A17,FALSE),x) confirmed with ctrl-shift-enter (standard array entry procedure).

Does that help?

[OD1995]

Yes! This works perfectly, thank you!