0

I thought that a lambda function defined for scalar input values would automatically iterate through an array when faced with an array input, but it seems that it's not universally true. I want to understand why, because I'm not happy with the definition I ended up with to make it work.

There is a range of values and I want to test each cell for multiple conditions. E.g. check whether an integer values is greater than min and less then max.

Using the AND function I ended up with the following lambda:

=LAMBDA(x;AND(x>2;x<10))

however, this definition won't work when used as an array function, only when applied on each individual cell separately.

I have tried a different approach and defined the lambda purely as a numerical expression:

=LAMBDA(x;(x>2)*(x<10)=1)

this definition works on an array, but I find it generally less readable.

Why does the definition with AND not work?

both versions of lambda applied to a sample range

sample workbook

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
vladi
  • 1
  • 1
  • 2
    It's as simple as `AND` doesn't work with arrays. Nothing to do with `LAMBDA`. – VBasic2008 Jun 04 '23 at 16:38
  • 3
    Actually, AND does "work" with arrays, it just doesn't work the way the OP expects. It returns the AND of all the elements of all the arrays. [See this](https://stackoverflow.com/a/10131070/445425). – chris neilsen Jun 04 '23 at 23:16
  • 1
    Why do you want to use with `AND()` when the `*` makes it work, that said this should work for your need `=LAMBDA(x;(x>2)*(x<10)=1)(A2:A6)` while the one won't. And this is the same logic applied when used with `FILTER()` function. **[Here](https://stackoverflow.com/questions/75944184/excel-filter-function-not-returning-expected-results-for-date-range-how-to-filt/75945402#75945402)** – Mayukh Bhattacharya Jun 05 '23 at 04:33
  • @MayukhBhattacharya The post you reference actually answers my question. I thought of the solution myself, but I wanted to try and make AND work, because it's hard to explain to my coworkers without abstracting from semantic boolean logic to arithmetic. – vladi Jun 06 '23 at 08:39
  • As @chrisneilsen sir has already said, `AND()` doesn't work the way you are expecting, instead it returns all the elements of the arrays, and I have tested many ways could n't make it work, unless you are using helper function like `BYROW()` or without helper functions with the one you are using `*` operator. – Mayukh Bhattacharya Jun 06 '23 at 09:48

1 Answers1

2

I think that your problem stems from the misunderstanding on how LAMBDA works. Lambda is a custom function that is used by predefined function. That predefined function iterate thru an array and applies the LAMBDA function to each element of iteration. For example, if you have a 2D array, that predefined function can take each column to get a result per each column. Or it can take each element of the table to get another table as a result.

In your case, you'd want to iterate thru each row of your table to get a column of results. That predefined function in your case is BYROW.

Therefore, the correct LAMBDA formula for your situation is:

=BYROW(A2:A6,LAMBDA(x,
    AND(x>2,x<10)))

1

The function AND evaluates the array of all individual results. To get the function applied to each element, in this case, a row, you have to use another LAMBDA function.

I suggest watching this video. Is the best LAMBDA tutorial I know:
Excel is Fun: All Important Excel Lookup Formulas: Excel Worksheet, Power Query & DAX – 28 Examples!

Enjoy!

Florin
  • 375
  • 3
  • 13
  • Thanks, I didn't know byrow, though it seems just a weaker map, because it allows only a single argument. I actually could use map, but only if all the arguments are arrays. It doesn't work when there are scalar arguments mixed in. I just want a readable way of testing multiple conditions on a bunch of arrays, with the conditions sometimes consisting in testing against a scalar argument. – vladi Jun 06 '23 at 08:33
  • Now, that you know what `LAMBDA` ca do, ask a new question with an example closer to your actual task. In this way you'll learn more `LAMBDA` tricks. – Florin Jun 06 '23 at 18:22
  • `BYROW` is a weaker `MAP` in the same way `LEFT` is a weaker `MID`, but everybody would use `LEFT(A1)` than `MID(A1,1,1)`. It's better to use the right tool for the right application. – Florin Jun 07 '23 at 12:27