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?


[TABLE="width: 368"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD] Column A [/TD]
[TD] Column B [/TD]
[/TR]
[TR]
[TD] Value [/TD]
[TD] Status [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] 10.00[/TD]
[TD] Active [/TD]
[/TR]
[TR]
[TD] 5.00[/TD]
[TD] Inactive [/TD]
[/TR]
[TR]
[TD] 2.00[/TD]
[TD] N/A [/TD]
[/TR]
[TR]
[TD] 8.00[/TD]
[TD] Active [/TD]
[/TR]
[TR]
[TD] 7.00[/TD]
[TD] Inactive [/TD]
[/TR]
</tbody>[/TABLE]



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.