Percentile.Inc (Quintile) Function with IF conditional statement

[excelforwork1]

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!

 

[AhoyNC]

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.