LAMBDA and LET works and no works, same logic, why?

For debugging purposes, I put the LET expression in a worksheet cell formula, then used the Formulas...Evaluate ribbon item to step through how it was evaluated. Alas, the Mac version of Excel doesn't have that feature. So please consider voting for the suggestion to add it in the Mac Excel UserVoice webpage: https://excel.uservoice.com/forums/304933-excel-for-mac?query=evaluate%20formula Microsoft prioritizes what it works on based on UserVoice votes, and has addressed over half of the Mac Excel UserVoice suggestions so far.

When I look at the LAMBDA with the LET function, I notice:

• Your LET echos ALLOCS, REFS and LEDGER. The LET will use those values from the LAMBDA, and there is no need to mention them as parameters (and values) in the LET.
•  You have SUM(MID(... as the value returned. Since MID returns text, SUM will always return 0.
• =LAMBDA(ALLOCS,REFS,LEDGER,...)(C:C,B2,A2) assigns FREDDYFIVE to LEDGER. Perhaps you meant to pass (C:C,A2,B2) to the LAMBDA?
• You didn't fully populate the column B with values, so I'm not understanding how you expect to add things up
• It appears that column C always allows 11 characters for the amount. Is there really a need to calculate LEN(TEXT(LEDGER,"00000000.00;-0000000.00"))?

I don't know if the sample workbook is oversimplified or not. For example, might column C contain more than one value pair per cell in the future? If there is only one value pair, there are easier ways of building the LAMBDA.

Also, my tests show that LET does iterate through an array.

=LET(ref,SEQUENCE(3),x,1,y,2,(x+y)*ref)  'Returns the array 3, 6, 9
 
=LET(ref,SEQUENCE(3),x,1,y,2,SUM((x+y)*ref/ref))  'Returns 9

Select allOpen in new window

Alas, the Mac version of Excel doesn't have that feature. 

What feature?

When you are writing or debugging a gnarly formula, the Evaluate Formula tool is invaluable. Each time you click "Step In" another piece of the formula is evaluated (starting with the most basic parts), so you can see where the formula goes astray by returning an error value. That's why I added the two minus signs in front of the MID.

oh I see. Yes done as you suggest.

Since MID returns text, SUM will always return 0.

I solve the label problem result by multiplying (or dividing) by 1, this generates a correctly numeric result; as per the example in D4, which is the working LAMBDA example.

Will the LET work with no declared names and values, or at least only the two needed for value calc? Save a lot of space.

Yes I cheated by by hard typing into the Reference column C what would have been picked up by the Col C formula which concatenates columns A and B.  So that 7000 in C3 would be picked up by the formula in C3 from Ledger column B as well as the invoice reference from column A. In this way Col C is a database of allocations of payments to invoices, since Col C can contain multiple such pairs of data. Basically another dimension, or array of data that can be read only with text manipulation, at this time (something you observed in another Answer).

Yes the REFERENCES Column B could be 900 etc rows long, as could they all. I just shortened it to 4 so as to demonstrate the working and non working versions of my formulae in column D. This use of those references has been the thrust of many of my questions in here. There is no other way I am interested in using at this time (such as VBA and extra Sheets) and it seems reading static arrays {1,2,3,4) is not yet possible except with text manipulation as we are doing here.

Also: that text in Column C can be comma delimited and then text-to-columns parsed and pivot tabled after putting all in one pair of columns, which need might only be once per year if ever, hence no desire to use another sheet or VBA.

Yes the Column C value from LEDGER is always 11 characters because the formula not there in the example includes the value from column B and forces it to 11 characters so that all values have a predictable length for text manipulations.

• =LAMBDA(ALLOCS,REFS,LEDGER,...)(C:C,B2,A2) assigns FREDDYFIVE to LEDGER. Perhaps you meant to pass (C:C,A2,B2) to the LAMBDA?

I need to check - struggled with that
be back to edit shortly
argh!! I found the (my) error - AND YOU ARE CORRECT ABOUT a AND b BEING THE WRONG WAY ROUND, but the result is still zero 0 - I did originally have them the correct way. 

In fact my comment about your comment about MID always evaluates to text is the problem and solution:
I have rejigged all the NAMES in what follows so the names match the column names in the spreadsheet rather than how I think about them, which made it more confusing:

So here is the original (names rejigged) LET formula:

=LAMBDA(REFERENCE,INVNO,LEDGER,LET(REFERENCE,REFERENCE,INVNO,INVNO,LEDGER,LEDGER,GetThese,LEN(TEXT(LEDGER,"00000000.00;-0000000.00")),StartHere,FIND(INVNO,REFERENCE)+LEN(INVNO),SUM(IFERROR(MID(REFERENCE,StartHere,GetThese),0))))(C:C,A2,B2)

Select allOpen in new window

which still evaluates to zero.

and here it is with *1 inserted see after " ,GetThese). " which converts the text result to a value :

=LAMBDA(REFERENCE,INVNO,LEDGER,LET(REFERENCE,REFERENCE,INVNO,INVNO,LEDGER,LEDGER,GetThese,LEN(TEXT(LEDGER,"00000000.00;-0000000.00")),StartHere,FIND(INVNO,REFERENCE)+LEN(INVNO),SUM(IFERROR(MID(REFERENCE,StartHere,GetThese)*1,0))))(C:C,A2,B2)

Select allOpen in new window

here's the rejigged file
!LEDGERSDEV-17EXTRACT.xlsx

Edit: I see that the above examples with the MID text to value mod differ in that one uses *1 and the other uses /1 . They both have the same effect.

Just seen this:

That's why I added the two minus signs in front of the MID.

doesn't that convert  true/false output to 1 or 0 ? Or more directly is that doing the same as my *1 or /1 ?
and I see that it does.

if you would like to write a short reply with the correct answer extracted from all the above  I'll mark it as the solution for future readers, with my thanks.

will that work for multiple pairs in Col C?

LOOKS LIKE THAT IS A YES. Thanks

!LEDGERSDEV-18EXTRACT.xlsx

So what's NUMCHARS bringing to my party?
Oh I see, you have used it as name and assumed I do not need to evaluate the length of the value in LEDGER Column B

It worked for two pairs of values in one cell in cell C2 in my limited testing.

It is worth noting that FREDDYFIVE-0007500.25 will match both both FREDDYFIVE and REDDYFIVE. A separator character between each ordered pair would be a desirable feature.

Yes, been wondering about that, so my thinking about adding a comma in between for text-to-columns purposes might have also this additional advantage?

in which case using the new TEXTJOIN function would also be useful. Is "function" the right word or should that be "formula"?)

It worked for two pairs of values in one cell in cell C2 in my limited testing.

my assumption is that each  INVNO Col A entry in Col C will be mutually exclusive - because it's true, you never get two allocations to the same invoice from the same payment, this will only ever be one.

I like to use the unary operator (two minus signs) to coerce text into a number. I could have used the VALUE function for that purpose. Using the text value in an arithmetic expression (as you did with *1 or /1) also works. The unary operator looks strange enough that novices trying to apply one of my formulas don't dare to change it. In contrast, +0, *1 or /1 look like something the novice could delete to make the formula simpler--and I hate getting "didn't work" posts when they try it.

I LIKE "your" unary operator.

now I am contemplating whether addition of comma delimiters might make this process simpler in some way; certainly it makes the manual typing of the contents of column A more human, though not adding spaces is not so easy... I suppose we could TRIM those out .. but that's only start and end.. so SUBSTITUTE I guess

If you separate the values in your ordered pairs with a comma, you need to begin the embedded arrays in column C with a comma, and sandwich that comma around the value of REFS. In other words, the formula needs to look for ,REDDYFIVE,  With a unique separator before and after REFS, a substring that matches a longer string in the REFS column won't cause conflation.

but a shorter string will?

In reality these are invoice numbers, either sales or purchase, so the sales numbers will be consistent INV001 INV002 etc, but the purchase invoice numbers could be anything if we use the suppliers' numbers, which for small systems I tend to do. I suppose we could add say the first three chars of the supplier name to the purchase inv numbers. I think there will always be a risk in here, but low, and we will trap any error when reconciling.

Just to be clear, I don't think you need to be adding separator characters to the values in column A. Put them instead in the LAMBDA & LET formulas.

Also, you expressed an interest in passing the entire of column C to the formula. I didn't see an annoying recalc delay in my testing, but I wasn't applying the formula to 900 rows of data, either. You don't need to match the data range exactly, but limiting the formula to say 1500 rows wouldn't be out of place.

With concatenation of a separator character in the LAMBDA, ,REDDYFIVE, will not match ,FREDDYFIVE,

Might one of the values in your column A include a comma? For example: HARCOURT, BRACE

If so, you might want to use a different character as the separator like \, % or | (pipe). 

Using commas in A might actually make it easier to type those entries, more natural for me.

Yes using C:C means I don't risk missing any allocations, but to balance this only invoice entries in column LEDGER actually have this formula entered, others are left blank - this choice is automatic when inserting an invoice type of transaction as it's already in the template for the row. And even 900 is a huge number to what I tend to see which is rarely 300. I suppose also we could value ise all invoice balances that drop to nil every now and then if it were an issue. This is in a structured table come to think of it, but I guess it won't keep trying to replicate when it sees gaps in the column.

With concatenation of a separator character in the LAMBDA, ,REDDYFIVE, will not match ,FREDDYFIVE,

erm. thinking: oh yes of course. 

Might one of the values in your column A include a comma? For example: HARCOURT, BRACE

No, A is an invoice reference (i.e. invoice number) column, the supplier name is another column. They look a bit odd in my test choices because of trying to capture errors caused by differences in presentation.

continued here:
https://www.experts-exchange.com/questions/29212590/Data-capture-of-multiple-text-and-value-pairs-LAMDA-content-too.html#questionAdd