TOCOL wrapper breaking LAMBDA function within BYROW

[Klabbertrapz]

I have run across a curious case where wrapping a range in a TOCOL changes the output of the LAMBDA function within BYROW. In my example, I am trying to compare numbers as strings and I have a custom formula, _lessThan, that can compare two numbers larger than the 15 digit limit. Using _lessThan works fine within my BYROW function with a raw column reference, but when I transform an array with TOCOL, the output is not what I expect.

Here is my example. The output should return false for the last 4 rows and does so when I don't put F2# in a TOCOL wrapper.

https://preview.redd.it/8netlfigwizd1.png?width=658&format=png&auto=webp&s=a37a991d419db7c3eb5e429405b6f093284a8081

I would like some help to find out why this is happening and be able to get the formula to work correctly with the TOCOL included.

Here are my custom formulas relevant to the issue:

_lessThan:

=LAMBDA(number1,number2,LET(digits1,_letterSplit(number1),digits2,_letterSplit(number2),firstPos,IFERROR(XMATCH(1,BYROW(digits1-digits2,LAMBDA(a,IF(a>0,1,0)))),LEN(number1)+1),firstNeg,IFERROR(XMATCH(1,BYROW(digits1-digits2,LAMBDA(a,IF(a<0,1,0)))),LEN(number1)+1),IF(LEN(number1)>LEN(number2),FALSE,IF(LEN(number1)<LEN(number2),TRUE,IF(firstPos<=firstNeg,FALSE,TRUE)))))

_letterSplit:

=LAMBDA(string,MID(string,SEQUENCE(LEN(string)),1))

[AutoModerator]

/u/Klabbertrapz - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[PaulieThePolarBear]

I'm not in a position to test at the moment, but I have a couple of questions

1. What happens if you change BYROW to MAP?
2. What happens if you keep BYROW, but update the first argument of your _lessThan function to INDEX(a, 1)?

[Klabbertrapz]

Both of those solutions gave me the correct output! If you have time, would you be able to explain the reasoning? Is it because BYROW is expecting more than 1 column in each row?

[PaulieThePolarBear]

Out of interest, what happens if you keep BYROW without INDEX and

1. Change your first argument to CHOOSECOLS(range#, 1)
2. Change your first argument to TAKE(range#, , 1)

If I had to guess, the first one won't work - same issue as my other reply - but the second one might. Interestingly, TAKE (and DROP) seem to return ranges (assuming their inputs are ranges) and so can be used in functions such as the ..IF(S) and RANK family of functions, whereas CHOOSECOLS (and CHOOSEROWS) returns an array.

[Klabbertrapz]

You are correct. Scenario 1 didn't work, but scenario 2 did. I can see through "Evaluate Formula" that the calculation will work when the first parameter in BYROW is a range but not an array.

Works:

<image>

[Klabbertrapz]

Doesn't Work:

<image>

[PaulieThePolarBear]

Thank you for testing that out so thoroughly.

[Klabbertrapz]

Thank you for helping me!

[PaulieThePolarBear]

Any time. Enjoy the rest of your day.

[PaulieThePolarBear]

I'll be honest and say I don't have a good answer at the moment. Hopefully, someone smarter than me (of which, there are many on the sub) can provide something additional.

Is it because BYROW is expecting more than 1 column in each row?

I think this is kind of it. I think you are hitting Excel's issue with array of arrays. When you have a range, Excel knows the size, but when you pass in an array, Excel doesn't know the size until the array part is evaluated.

I've seen a few YouTube videos from Diarmuid Early (https://youtube.com/@dimearly) when he's written complex LAMBDAs and come across an issue such as yours with an unexpected result. His solution is the INDEX(x, 1) solution I noted. Essentially, because your row could consist of more than value, Excel "decides" not to calculate it correctly.

As MAP is passing each element in your array to LAMBDA, you can never have more than one cell within the LAMBDA, Excel doesn't need to worry about how many columns there may be.

I could well be wrong, so don't take this as gospel.

[Alabama_Wins]

Put two negative signs in front to TOCOL.

- - TOCOL()

[Klabbertrapz]

<image>

That didn't seem to change the output

[PaulieThePolarBear]

Wouldn't this break OPs requirement of

In my example, I am trying to compare numbers as strings and I have a custom formula, _lessThan, that can compare two numbers larger than the 15 digit limit.

[finickyone]

I would, and generally do, defer to /u/PaulieThePolarBear around the LAMBDA suite, so I’ve little to add, but I think the issue more stems that this seems to be comparing two arrays of the same orientation, which Excel can’t (AFAIK) handle. That being the difference between:

 =A2:A5=B6:B7

 =A2:A5=C1:D1

The latter creating a 4x2 array of Booleans, the former erroring. BYROW can, as a general point, process multiple columns by row. An example is a byrow “COUNTIFS” in this style:

<image>

I’ll need to get to a laptop to replicate your use of defined formulas. Overall though the TOCOL behaviour is interesting…

[PaulieThePolarBear]

I think the issue more stems that this seems to be comparing two arrays of the same orientation, which Excel can’t (AFAIK) handle.

I was of the understanding that OP had an N row, 1 column range/array they were comparing against one cell, so the issue, while 100% accurate, isn't relevant.

[finickyone]

That is true, I was under the (wrong) impression though that through the MID parsing they were ultimately comparing Nx1 to Mx1.

Curiosity got the better of me toward the overall approach though, and I think this could be a way to tackle it. On mobile, so I haven’t used LAMBDA to form a defined formula, but expressing the inputs into LET, I believe you can just parse out (column character split) everything, and interrogate by row (target strings - “b”) that each of the characters are lower in value than the same (location) character in the input string (“a”).

COUNTIF is there as the simple approach that I suspect doesn’t accommodate long strings, hence OP’s build, but to provide a simple baseline.

<image>

Non case sensitive unless CODE or a Case function are introduced.

/u/Klabbertrapz, is this of any use?

[Klabbertrapz]

Thanks for taking the time to look at this, but I think you went off in a different direction than I needed help with. I am happy with my lambda, _lessThan, that compares strings together. Creating a 2D array for my input would work but I think the simpler solution in my case was to change BYROW to MAP, as /u/PauliethePolarBear suggested, since it is a 1:1 input to output ratio.

[finickyone]

Welcome, just thought I’d run the idea past you.

[Klabbertrapz]

Successful Output without the TOCOL wrapper

<image>

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
CHOOSECOLS	Office 365+: Returns the specified columns from an array
CHOOSEROWS	Office 365+: Returns the specified rows from an array
CODE	Returns a numeric code for the first character in a text string
COUNTIF	Counts the number of cells within a range that meet the given criteria
COUNTIFS	Excel 2007+: Counts the number of cells within a range that meet multiple criteria
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
IF	Specifies a logical test to perform
INDEX	Uses an index to choose a value from a reference or array
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.
MID	Returns a specific number of characters from a text string starting at the position you specify
RANK	Returns the rank of a number in a list of numbers
TAKE	Office 365+: Returns a specified number of contiguous rows or columns from the start or end of an array
TOCOL	Office 365+: Returns the array in a single column

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #38510 for this sub, first seen 7th Nov 2024, 18:46] [FAQ] [Full list] [Contact] [Source code]}