Adjusting a percentile results when data is filtered

[jsparrow22]

I am using the percentile.exc formula to display the 50th, 85th and 95th percentile.
This figure is calculated from a column containing days items take to complete. The days items take to complete contains data for multiple categories but when I filter the categories I continue to get the same percentile result.
What formula do I need to ensure the percentile is adjusted based on whichever category of item I am interested in.
I still want an overall view but being able to have the data per category would be beneficial.

Thanks in advance.

 

[Domenic]

Let's say that A2:A10 contains your data, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=PERCENTILE.EXC(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1))>0,A2:A10),0.5)

To also exclude from the calculation rows that are manually hidden, replace the number 3 with 103.

Hope this helps!

 

[jsparrow22]

Let's say that A2:A10 contains your data, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=PERCENTILE.EXC(IF(SUBTOTAL(3,OFFSET(A2:A10,ROW(A2:A10)-ROW(A2),0,1))>0,A2:A10),0.5)

To also exclude from the calculation rows that are manually hidden, replace the number 3 with 103.

Hope this helps!

I couldn't get the suggested formula to work but have since found a workaround.

=AGGREGATE(18,5,F6:F220,0.5)