PERCENTILE IF using ARRAYFORMULA for a set of conditions-CodePudding

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:

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)

CodePudding user response：

You can get the percentile for each value as follows

=sort(arrayformula(iferror(
{A2:A,B2:B,
(VLOOKUP(row(A2:A),{sort({row(A2:A),A2:B},2,1,3,1),row(A2:A)},4,false)-MATCH(A2:A,QUERY({sort({A2:B},1,1,2,0)},"select Col1"),0))/countif(A2:A,A2:A)}
)),1,1,3,1)

then apply (or not) an interpolation, it is up to you to do it by linear formula as Tom Sharpe did or according to a statistical distribution (