Forum Discussion
AND() Function in CSE Formula (MEDIAN)
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!
