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:


but in my case value should be an array of possible values.

Here is the sample data with the expected result highlighted: sample

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(A2:A12 & "_" & B2:B12)),E2:E3  & "_(\d+)|.",",$1"),","),D2),))

Using MATCH in H2:

   PERCENTILE(IFNA(--(match(A2:A12,E2:E3,0) > 0) * B2:B12,),D2),))

here is the Spreadsheet file:

1 answer

  • answered 2022-05-07 11:41 Tom Sharpe

    Just as an exercise I tried working it out from first principles based on the quantiles formula. The Excel or Google Sheets Percentile and 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


    enter image description here

    I believe you can also do it by manipulating the values of the second argument to the Percentile function - it would go like this:


    enter image description here

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum