Percentile with multiple conditions in google sheets

Question

I have a sheet called "main" with 4 columns like this :

I want to make a percentile of column N, with condition of column A, C, and J

I tried with function PERCENTILE(IF((x)*(y)*(z),aa),0.5) and works for

queue_name = "idecbi-prod",

grass_week = "2022-09-05",

and task_user = "di_scheduler",

Desired Answer :

it works just fine with these conditions,

But when I tried to use another condition such as "idecbi-dev" or "ad-hoc" or another grass_week, it become error like this

Whereas the only thing i change is only O4 to O5 and N4 to N5

The data seems fine and we have all the conditions as shown in the first picture (main sheet).

Is there anything that I miss ?

Here's dummy data that I've created for better understanding.

https://docs.google.com/spreadsheets/d/1ejhxVTayWjrUAZ6NE78AXx2pENvlhGDstMXwrDfqv_0/edit?usp=sharing

I'm trying to make cell D4 works as fine as D3

Answer 1

wrap it into ARRAYFORMULA:

=ArrayFormula(PERCENTILE(IF((main_week=A3)*(main_queue=C4)*(main_task_usage=B4),main_avg_exectime),0.5))