Inclusive Percentile Calculation with IF statement containing logical AND

Nate2188

New Member
Joined
Sep 30, 2014
Messages
1
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!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,406
Try...

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

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,758
Members
414,171
Latest member
12Rev79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top