Forum Discussion
MAngosto
Apr 16, 2024Iron Contributor
Unable to use a nested IF(AND) function in an array function (Discussion)
Hello there! I came across the following discussion on the community: https://techcommunity.microsoft.com/t5/excel/two-wrongs-don-t-make-a-right-negative-worker-values-are-turning/m-p/4114539 ...
- Apr 16, 2024
AND does not return an array of TRUE/FALSE values, but a single TRUE/FALSE value.
(logic1)*(logic2) returns an array of 1/0 values.
MAngosto
Apr 17, 2024Iron Contributor
Wow! I was initially looking for a technical explanation on why a nested AND function would not return an array when an IF-IF or an IF with "*" conditions does, but this workaround is definately interesting though.
Thanks for the contribution.
joelb95
Apr 17, 2024Brass Contributor
This is a guess, but I think “and” is an aggregate function like sum - no mater how many arguments you pass to it, it will return a single value. Think of it like a straight Boolean check (are truth values a, b, c, … z all true?) rather than like a an expanded group of truthy comparisons. “And” sees only an array of truth values, no matter how that array is composed.
But that is a guess - I have no idea about how excel works under the hood.
But that is a guess - I have no idea about how excel works under the hood.