Forum Discussion
Unable to use a nested IF(AND) function in an array function (Discussion)
Hello there!
I came across the following discussion on the community:
Looking at the response by Patrick2788, I was trying to play around with his proposed function. It was rather more intuitive to me to use a nested IF(AND) function and I was surprised it does not work for an array function.
I was just wondering if anyone knew why this happens and the logic behind this. Tested the IF(IF) function as well as a single IF(("logic1")*("logic2")) and both worked efficiently. Why wouldn't a nested IF(AND) function serve here?
Appreciate any kind response.
Martin
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.
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.
The and() returning a single value is annoying when dealing with arrays, but it can be worked around.
If it is a problem you have, here is one possible approach using a functional programming style (it allows chaining together multiple logical relationships, but that chain is not necessary here). The important bit is that the two arrays entered must already be boolean values (TRUE or FALSE) or otherwise "truthy" (Excel will evaluate them as TRUE or FALSE).
=LET( and_base, LAMBDA(array1_bool, LAMBDA(array2_bool, IF(array1_bool * array2_bool, TRUE, FALSE))), and_func, LAMBDA(array1_bool, array2_bool, and_base(array1_bool)(array2_bool)), array1_bool, {TRUE; TRUE; FALSE; FALSE}, array2_bool, {TRUE; FALSE; TRUE; FALSE}, and_func(array1_bool, array2_bool) )The easiest way to make arrays into booleans is to simply apply your criteria to the array, e.g.
=LET( and_base, LAMBDA(array1_bool, LAMBDA(array2_bool, IF(array1_bool * array2_bool, TRUE, FALSE))), and_func, LAMBDA(array1_bool, array2_bool, and_base(array1_bool)(array2_bool)), array1, SEQUENCE(10), array2, SEQUENCE(10), array1_bool, array1 <= 6, array2_bool, array2 >= 4, and_array, and_func(array1_bool, array2_bool), headers, {"Array 1", "Array 2", "Crit 1 and Crit 2 met"}, VSTACK(headers, HSTACK(array1, array2, and_array)) )output should be:
Array 1 Array 2 Crit 1 and Crit 2 met 1 1 FALSE 2 2 FALSE 3 3 FALSE 4 4 TRUE 5 5 TRUE 6 6 TRUE 7 7 FALSE 8 8 FALSE 9 9 FALSE 10 10 FALSE - 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.
It's exactly as HansVogelaar explained. I purposely wrote a nested IF because IF-AND won't spill (Unless you have some help from BYROW but nothing that sophisticated was needed for that task).
