SOLVED

Unable to use a nested IF(AND) function in an array function (Discussion)

Iron Contributor

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-ar...

 

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

5 Replies
best response confirmed by MAngosto (Iron Contributor)
Solution

@MAngosto 

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 

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).

@MAngosto 

 

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 1Array 2Crit 1 and Crit 2 met
11FALSE
22FALSE
33FALSE
44TRUE
55TRUE
66TRUE
77FALSE
88FALSE
99FALSE
1010FALSE

 

 

@joelb95 

 

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.

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.
1 best response

Accepted Solutions
best response confirmed by MAngosto (Iron Contributor)
Solution

@MAngosto 

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.

View solution in original post