Percentile IF entire column ignoring blanks and zeros

[JLopez123]

Hello, I'm trying to calculate percentiles for different groups in column A using random value data in column B, but ignoring blanks and zeros. I've searched around, but everywhere I've seen shows ranges of data, and the formula can't quite get me what I'm looking for. And while my example only shows three groups of five, my real world example will have multiple groups with each group having a random number of rows.

So far I have the following, assuming I'm calculating the 80th percentile for each group:

=PERCENTILE(IF(A:A=A2,B:B),0.8)

It works, but it doesn't ignore blanks and zeroes.

Any help on getting me the rest of the way there?

Thanks!

 

[AhoyNC]

The PERCENTILE function will ignore blanks.
Since you have Excel 365 with the FILTER function try.

Book1
	A	B	C	D
1	group	Val	80th	60th
2	A	30	105.6	87
3	A	94	105.6	87
4	A		105.6	87
5	A	123	105.6	87
6	A	59	105.6	87
7	B	78	87.6	81.2
8	B	0	87.6	81.2
9	B		87.6	81.2
10	B	94	87.6	81.2
11	B	52	87.6	81.2
12	C	35	65.4	43.4
13	C	56	65.4	43.4
14	C	7	65.4	43.4
15	C	103	65.4	43.4
16	C	25	65.4	43.4
Sheet4

Cell Formulas
Range	Formula
C2:C16	C2	=PERCENTILE(FILTER($B$2:$B$16,($A$2:$A$16=$A2)*($B$2:$B$16<>0)),0.8)
D2:D16	D2	=PERCENTILE(FILTER($B$2:$B$16,($A$2:$A$16=$A2)*($B$2:$B$16<>0)),0.6)

 

[JLopez123]

Never used the FILTER function before, so thanks for calling that out. It worked perfectly!

 

[AhoyNC]

You're welcome. Thanks for the feedback.