Inclusive Percentile Calculation with IF statement containing logical AND

[Nate2188]

I'm having difficulties with a PERCENTILE.INC function calculating from a table in Excel 2013. My formula is this:

{=PERCENTILE.INC(IF(AND(Table1[Priority]=0,Table1[Shift]="A"),Table1[Time],""),0.9)}

This formula produces a #VALUE error message. I have not been able to figure out what is wrong with this formula.

I tried changing to a logical OR function and the error goes away. So the following function calculates correctly, though it's not what I'm looking for:

{=PERCENTILE.INC(IF(OR(Table1[Priority]=0,Table1[Shift]="A"),Table1[Time],""),0.9)}

I also tried entering just the IF function into an empty cell to verify syntax is correct:

=IF(AND(Table1[Priority]=0,Table1[Shift]="A"),Table1[Time],"")

This function also returns no errors and results in what I would expect.

Any help with the first PERCENTILE.INC formula would be greatly appreciated.

Thanks!

 

[Domenic]

Try...

=PERCENTILE.INC(IF(Table1[Priority]=0,IF(Table1[Shift]="A",Table1[Time])),0.9)

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!