Percentile.Inc (Quintile) Function with IF conditional statement

excelforwork1

New Member
Hi,

I'm trying to calculate from a dataset a =Percentile.Inc function with a condition. For example, I want the 20th, 40th, 80th percentiles from the dataset for ONLY "Active" status in column B, using the values in column A. My formula for 80th percentile is =Percentile.Inc(If(\$B\$2:\$B\$6="Active",\$A\$2:\$A\$6),.8); however this formula is pulling all values, no matter the column B status. Is there a way to fix this so the condition is that for ONLY "Active" pull values in column A?

 Column A Column B Value Status 10.00 Active 5.00 Inactive 2.00 N/A 8.00 Active 7.00 Inactive

<colgroup><col><col></colgroup><tbody>
</tbody>

Thanks!

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Your formula is correct, but it is an array formula and must be entered with CTRL-SHIFT-ENTER. If done right Excel will but {} around your formula.

Put your cursor in the cell with the formula.
Press F2 key for edit
then press CTRL-SHIFT-ENTER.

Replies
5
Views
299
Replies
3
Views
240
Replies
10
Views
351
Replies
3
Views
468
Replies
0
Views
473

1,203,608
Messages
6,056,286
Members
444,855
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back