AND() Function in CSE Formula (MEDIAN)

I am trying to calculate the MEDIAN of a column in the "Raw" tab (J) when the value of one column (A) matches the value in a pivot table (Pivot!A4) and when the value of another column (I) equals "No...

Formulas & Functions

Need Help

IngeborgHawighorst
Jan 25, 2018
Hello,

in an array formula you can't use AND in the condition. Instead, wrap each condition into its own set of brackets and multiply the conditions (for AND use *, for OR use +)

=MEDIAN(IF((raw!$E:$E=pivot!A4)*(raw!$I:$I="No"),raw!$J:$J)) (array entered)

Why? Each condition will end up as a list of TRUE or FALSE values. When two sets are multiplied, only the TRUE in both sets will result in TRUE. That is the AND logic. When the two sets are added, any TRUE will result in a TRUE in the result. That is the OR logic.

Step through it with the Formula Evaluation tool and a small data set of only a few rows to see how it works.

IngeborgHawighorst

MVP

Jan 25, 2018

Hello,

in an array formula you can't use AND in the condition. Instead, wrap each condition into its own set of brackets and multiply the conditions (for AND use *, for OR use +)

=MEDIAN(IF((raw!$E:$E=pivot!A4)*(raw!$I:$I="No"),raw!$J:$J)) (array entered)

Why? Each condition will end up as a list of TRUE or FALSE values. When two sets are multiplied, only the TRUE in both sets will result in TRUE. That is the AND logic. When the two sets are added, any TRUE will result in a TRUE in the result. That is the OR logic.

Step through it with the Formula Evaluation tool and a small data set of only a few rows to see how it works.

Arthur King

Copper Contributor

Jan 25, 2018

Perfect! Makes sense and solved the problem. Thank you very much!

Forum Discussion

AND() Function in CSE Formula (MEDIAN)