Forum Discussion

MAngosto's avatar
MAngosto
Iron Contributor
Apr 16, 2024

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  ...
Excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 16, 2024

    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.

joelb95's avatar
joelb95
Brass Contributor

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

 

 

MAngosto's avatar
MAngosto
Iron Contributor
Apr 17, 2024

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.

  • joelb95's avatar
    joelb95
    Brass Contributor
    Apr 17, 2024
    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.

Resources