array formula using percentiles

[midsession]

Hello MrExcel, my knowledge of array formulae isn't all it should be so I need some help.

I'm trying to find the tenth and ninetieth percentiles in two combined datasets

[

Book1
	A	B	C	D	E	F
1				86.66667		0
2	91.7	1
3	83.3	1
4	83.3	0
5	83.3	1
6	91.7	0
7	66.7	1
8	75.0	1
9	58.3	1
10	75.0	0
11	83.3	0
12	75.0	1
13	58.3	0
14	75.0	0
15	83.3	0
16	75.0	0
17	75.0	0
18	100.0	0
19	75.0	1
20	83.3	1
21	91.7	1
22	66.7	1
23	58.3	0
24	83.3	1
25	50.0	0
26	83.3	0
Sheet1

Cell Formulas
Range	Formula
D1	{=PERCENTILE.EXC(((A$2:A$26)*($B$2:$B$26=1)), 0.9)}
F1	{=PERCENTILE.EXC(((A$2:A$26)*($B$2:$B$26=0)), 0.1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

]

Find the 90th percentile for the items in col A where col B is 1
and
Find the 10th percentile for the items in col A where col B is 0

I had thought the answer was
=PERCENTILE.EXC(((A$2:A$26)*($B$2:$B$26=1)), 0.9)
and
=PERCENTILE.EXC(((A$2:A$26)*($B$2:$B$26=0)), 0.1)

however there's no way F1 should be zero in that case

When I disentangle the datasets to check, they give different results (which I assume are correct)

[

Book1
	I	J	K	L	M	N
1		91.66667				53.33333
2	91.7				83.3
3	83.3				91.7
4	83.3				75.0
5	66.7				83.3
6	75.0				58.3
7	58.3				75.0
8	75.0				83.3
9	75.0				75.0
10	83.3				75.0
11	91.7				100.0
12	66.7				58.3
13	83.3				50.0
14					83.3
Sheet1

Cell Formulas
Range	Formula
J1	{=PERCENTILE.EXC((I$2:I$13), 0.9)}
N1	{=PERCENTILE.EXC((M$2:M$14), 0.1)}
Press CTRL+SHIFT+ENTER to enter array formulas.

]

So now I realise my understanding of how array formulas work is nonsense - can anyone help with the original formulae though?

Thanks in advance
midsession

 

[Canapone]

Hi,

an attempt:

=PERCENTILE.EXC(IF(B2:B26=1,A2:A26),0.9)

=PERCENTILE.EXC(IF(B2:B26=0,A2:A26),0.1)

Array entered

Regards

 

[midsession]

That seems to do the trick, many thanks

 

[Canapone]

Ciao,

you are welcome.

Thanks for sharing feedback.