Finding the percentage if 2 values are true

[Fyrdawg]

Hello..

I am trying to figure out the 90% of a range to response times, if both Z42 and Z58 are true.. I have came up with these two formulas but I keep getting a "You've entered to many arguments for this function".

=PERCENTILE.EXC((IF(AND($C$2:$C$278=$Z$42,if($E$2:$E$278=$Z$58),$O$2:$O$278)),0.9)))

=PERCENTILE.EXC((IF($C$2:$C$278=$Z$42,AND($E$2:$E$278=$Z$58),"",$O$2:$O$278)),0.9)))

Any help is always greatly appreciated.

 

[fjns]

Hi, try this formula
=PERCENTILE.EXC(IF(AND($C$2:$C$278=$Z$42,$E$2:$E$278=$Z$58),$O$2:$O$278,""),0.9)

 

[Fyrdawg]

=PERCENTILE.EXC(IF(AND($C$2:$C$278=$Z$42,$E$2:$E$278=$Z$58),$O$2:$O$278,""),0.9)

Thank you for the suggestion. I tried this and I get the #VALUE!, error.

 

[xplainer]

=PERCENTILE.EXC(IF(AND($C$2:$C$278=$Z$42,$E$2:$E$278=$Z$58),$O$2:$O$278,""),0.9)

Thank you for the suggestion. I tried this and I get the #VALUE!, error.

Because we cannot use AND in array formulas. Try:

=PERCENTILE.EXC(IF($C$2:$C$278=$Z$42, IF($E$2:$E$278=$Z$58, $O$2:$O$278)), 0.9)

The formula must to be array-entered (press ctrl+shift+Enter instead of just Enter) in some versions of Excel. But maybe not in Office 365 Excel.

 

[Fyrdawg]

Because we cannot use AND in array formulas. Try:

=PERCENTILE.EXC(IF($C$2:$C$278=$Z$42, IF($E$2:$E$278=$Z$58, $O$2:$O$278)), 0.9)

The formula must to be array-entered (press ctrl+shift+Enter instead of just Enter) in some versions of Excel. But maybe not in Office 365 Excel.

Thank you for this suggestion. I tried this as an array and just by hitting enter and I get the #NUM!, error both ways.

 

[Fyrdawg]

Thank you for this suggestion. I tried this as an array and just by hitting enter and I get the #NUM!, error both ways.

I forgot to change to format of the cells to custom: h:mm:ss and it worked. However, it isn't computing when there are less then 10 results. This may correct itself once I get more data entered for the next few months.

Thank you again for your help.

 

[xplainer]

it isn't computing when there are less then 10 results

Less than 9, I think. In any case, it would be prudent to wrap IFERROR around the formula, just in case too few rows meet both conditions.

=IFERROR(PERCENTILE.EXC(IF($C$2:$C$278=$Z$42, IF($E$2:$E$278=$Z$58, $O$2:$O$278)), 0.9), "")

 

[Fyrdawg]

Less than 9, I think. In any case, it would be prudent to wrap IFERROR around the formula, just in case too few rows meet both conditions.

=IFERROR(PERCENTILE.EXC(IF($C$2:$C$278=$Z$42, IF($E$2:$E$278=$Z$58, $O$2:$O$278)), 0.9), "")

That cleaned it up nicely.. Thank you again for your help.. I have really struggled to figure out the formula..