PERCENTILE IF using ARRAYFORMULA for a set of conditions
I need to calculate the percentile using an if condition to calculate it by group of conditions, but Google Sheets doesn't provide PERCENTILEIF function. A nonarray solution is possible:
=ARRAYFORMULA(PERCENTILE(if(range=value,values),percentile))
but in my case value
should be an array of possible values.
Here is the sample data with the expected result highlighted:
I tried several options to use an array of possible values, but in all cases, I get the wrong result:
Using JOIN
in G2
:
=arrayformula(if(len(E2:E3),percentile(split(regexreplace(join(",",
Arrayformula(A2:A12 & "_" & B2:B12)),E2:E3 & "_(\d+).",",$1"),","),D2),))
Using MATCH
in H2
:
=ARRAYFORMULA(if(len(E2:E3),
PERCENTILE(IFNA((match(A2:A12,E2:E3,0) > 0) * B2:B12,),D2),))
here is the Spreadsheet file: https://docs.google.com/spreadsheets/d/1VDJIYvmOC46DI_9u4zSEfmxSan5R5VKK772C_kP5rxA/edit?usp=sharing
1 answer

Just as an exercise I tried working it out from first principles based on the quantiles formula. The Excel or Google Sheets Percentile and Percentile.inc functions use the (N − 1)p + 1 variation shown in the last table under Excel in the reference above.
So for the first group,
(N − 1)p + 1 = 3 * 0.8 + 1 = 3.4
This means you interpolate 0.4 of the way from the third point (10) to the fourth point (30), giving you
10 + 0.4 * (30  10) = 18.
The array formula is
=ArrayFormula(vlookup(vlookup(E2:E3,{sort(A2:B,1,1,2,1),sequence(ROWS(A2:A))},3,false)+floor((countif(A2:A,E2:E3)1)*D2),{sequence(ROWS(A2:A)),sort(A2:B,1,1,2,1)},3,false) +(vlookup(vlookup(E2:E3,{sort(A2:B,1,1,2,1),sequence(ROWS(A2:A))},3,false)+ceiling((countif(A2:A,E2:E3)1)*D2),{sequence(ROWS(A2:A)),sort(A2:B,1,1,2,1)},3,false) vlookup(vlookup(E2:E3,{sort(A2:B,1,1,2,1),sequence(ROWS(A2:A))},3,false)+floor((countif(A2:A,E2:E3)1)*D2),{sequence(ROWS(A2:A)),sort(A2:B,1,1,2,1)},3,false))*mod((countif(A2:A,E2:E3)1)*D2,1))
I believe you can also do it by manipulating the values of the second argument to the Percentile function  it would go like this:
=ArrayFormula(percentile(if(A2:A="",,B2:B+A2:A*1000), D2*(countif(A2:A,E2:E3)1)/(count(A2:A)1)+(countif(A2:A,"<"&E2:E3))/(count(A2:A)1))E2:E3*1000)
do you know?
how many words do you know