Forum Discussion
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".
If I leave out the check for column I everything works fine, so the following works:
=MEDIAN(IF(Raw!$E:$E=Pivot!A4,Raw!$J:$J)) (array formula)
But this just returns 0:
=MEDIAN(IF(AND(Raw!$E:$E=Pivot!A4,Raw!$I:$I="No"),Raw!$J:$J)) (array formula)
The data looks fine so I figure I'm not grasping how array formulas parse. Any idea how to fix the formula? Thanks for your help!
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.
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.
Perfect! Makes sense and solved the problem. Thank you very much!
