PERCENTILE with IFs?

**DSwartz** · 11-21-2013, 12:09 PM

I'm trying to determine a certain percentile of an array of numbers using an PERCENTILE function. However, I only want to include certain numbers within this array in the calculation. The numbers I want to include all have the same value in an adjacent column, so I'm thinking I can do something similar to a SUMIF where the adjacent column will be checked to see if the correct value is present before the number is added to the array. Is there a way I can accomplish this within a PERCENTILE function? Thanks in advance for any assistance.

**alansidman** · 11-21-2013, 12:16 PM

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**ragulduy** · 11-21-2013, 12:16 PM

=PERCENTILE(IF(B1:B3="y",A1:A3,""),0.5)

I'd think that would work. It's an array formula so confirm with ctrl+shift+enter

**daddylonglegs** · 11-21-2013, 12:19 PM

You can use an array formula, e.g. this gives you the 75th percentile of B2:B100 wwhen A2:A100 = "x"

=PERCENTILE(IF(A2:A100="x",B2:B100),75%)

confirm with CTRL+SHIFT+ENTER so that the formula is wrapped in curly braces like { and }