Multiple Logical Tests Within Array If Function

[varchar50]

I've found multiple threads answering the question about using multiple logical tests with an if function with Excel 2010, and the consensus approach is:

=IF(AND(Test1,Test2),ValueTrue,ValueFalse)

However, when I try to use this in an array formula I end up with a #VALUE! error.

I am trying to add interactive dashboard functionality to a calculation by having a small table reference a large table and make calculations based on user inputs.

I am using the following formula:

{=QUARTILE(IF(AND($I:$I=$Q4,$A:$A=Sheet1!$B$2),$N:$N,""),1)}

N is the field I want my quartile calculated on
Q4 is a static cell with a specific value I want found in I:I
Sheet1!B2 is a user selected value on a separate tab

The formula works fine before I add the AND operator and the $A:$A=Sheet1!$B$2 logical test. Are AND/OR operators not valid in array formulas?

If this approach won't work, what other option is there?

Thank you for the help.

 

[Scott Huish]

AND and OR are typically not used with array formulas because it will evaluate the AND and the OR across all the values of the array, not independently.

Try this:

=QUARTILE(IF($I:$I=$Q4,IF($A:A=Sheet1!B2,$N:$N)),1)

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

 

[varchar50]

AND and OR are typically not used with array formulas because it will evaluate the AND and the OR across all the values of the array, not independently.

Try this:

=QUARTILE(IF($I:$I=$Q4,IF($A:A=Sheet1!B2,$N:$N)),1)

Confirm with CTRL-SHIFT-ENTER rather than just Enter.

So simple. Thank you for straightening me out. I was going down all kinds of crazy paths to try and figure this out. This worked wonderfully.