LAMDA inside Structured Table - syntax to pass variables?

Update, using LET not LAMBDA:

LET(ENID,SUMIF(INDEX(CREFS[CQNO],1,0):CREFS[@CQNO],CREFS[@CQNO],INDEX(CREFS[Settled],1):CREFS[@Settled]),ENID)

Select allOpen in new window

CREFS is the Table Name.

Excel name space has accepted that as a named formula. However, when attempted without the Table Name it rejected it. So I expect LAMBDA has the same requirement.In what ways can we attempt to pass the parameter(s)?

Anthony

I can install this as a Named LAMBDA:

=LAMBDA(ENID,SUMIF(INDEX(CRefs[CQNO],1,0):CRefs[@CQNO],CRefs[@CQNO],INDEX(CRefs[Settled],1):CRefs[@Settled]))

Select allOpen in new window

but that is with no parameters enabled. A dummy parameter is required to avoid the calc! error.
I have to create this outside the Table otherwise Excel removes the Table name from each field.

If I change that first instance of CRefs to ENID, so attempting to give effect to the parameter being passed, the name will not save, throwing an error. When I reinsert the CRefs name and remove ENID from that position, it is accepted. Looks like a bug to me, or unknown required syntax; I have tried double quotes "", pair of braces [], double double braces, brackets and double brackets and all those in combinations.

I think what you are tying to do is parameterize the table name. You can't do that. The Excel calculation engine does not have the ability to pass a table name as a parameter to a function and LAMBDA is just another Excel worksheet function. Only scalar values, arrays of scalar values, and ranges can be passed to a function.

Did you know that you don't need the table name as long as the reference to the table part is made inside the table?

If you are using the LAMBDA function outside of a table and referencing parts of the table in the LAMBDA formula (last parameter to LAMBDA,) then pass the individual table parts needed/used by the formula.

Kevin

Hi Kevin, I started all this inside the Table, only started from outside when inside would’t work.

The Lamda works inside the table. It’s only when I try to enable parameters it fails.


Yes know about the table name.
Anthony

My little plan is, was, to create myself a set of short named Lamdas I could use in any table. 

The name space seems to want the table name in order to accept the formula, as soon as the formula is put in the table, Excel removes the table name anyway

Yes, as I stated above, you can only pass scalar values, arrays of scalar values, and ranges can be passed to a function and LAMBDA is just another function from a parameter perspective.

I just noticed that you may also be trying to pass column names as parameters - that also will not work. When passing a table reference as a parameter, you have to pass the table part as a range.

Kevin

ah, ok, so no table structure to be passed as parameters, ok. You mean specify the range of the table such as A1:G20 or a range name of same? What I might call the old fashioned way?

I guess that resolves that.

You can do what you want - create "table" oriented LAMBDA functions - but you have to pass in any parameters as ranges, not table part names. While LAMBDA has been presented as the coolest thing since sliced bread and the Rosetta stone of Turing completeness - my initial observations of said function is that it is, for the most part, a red herring on the path to providing a true Turing complete worksheet formula experience like VBA/JS UDFs can provide.

In some languages like C you can do the kind of monkey business you are trying to do here. But, in the Excel world, we are working in the old Excel formula parsing world and, despite the hoopla, LAMBDA is just another Excel function and parameter passing follow the same rules as with any other Excel worksheet function. Additionally, the last parameter to the LAMBDA function is just a regular old worksheet formula which has to abide by all the same rules as if the formula was put into a cell without LAMBDA.

Rather than think of LAMBDA as an open ended magical box, think of it as a way to call a regular Excel worksheet formula but with one or more parameters that are substituted into the formula where those parameters have to follow Excel parameter rules.

Kevin

Interesting, must remember the Table range does not include the headers



The above was to test if a vanilla range name set over the table range does dynamically increase with additional table rows, which it does. Interesting the Table related names in the name space are not output with the list of names/ranges.

Anthony

"so no table structure to be passed as parameters"

That is not what I said. You can't pass a table or column name as a parameter. You can pass any part of a table expressed as a range as a parameter. This is a table column expressed as a range: Table1[B] is the entire B column in Table1.

Kevin

"the Table range does not include the headers"

Correct. Because that is the most commonly used part of the table.

This references the entire table: =Table1[#All]

Kevin

To include the header in a column reference:

=Table1[[#Headers],[#Data],[B]]

Kevin

I'm not particularly put out by this constraint as I am finding Structured Table methods useful in their own right, though having to use range names seem like an unfortunate layer of complexity, albeit traditional complexity. Not to worry though, better we have SEGMENT than this aspect explored.

Thanks

Anthony

"The above was to test if a vanilla range name set over the table range does dynamically increase with additional table rows, which it does."

Yes, that is one of the cool things about tables and structured references. They are covered extensively in this most excellent book on Excel tables:

https://www.amazon.com/Excel-Tables-Complete-Creating-Automating-ebook/dp/B017QL637E/ref=sr_1_2

Kevin

This is a table column expressed as a range: Table1[B] is the entire B column in Table1

 Is Table1 the table name? Getting confused here.. What's the difference between the Table Name In my example CRefs and CRefs[B].  
Need to reread your text I think

Anthony

just downloading that book, looks like a very engaging read.
Here is the UK Amazon link as the USA one won't let me buy from there:
https://www.amazon.co.uk/Excel-Tables-Complete-Creating-Automating-ebook

To include the header in a column reference:

=Table1[[#Headers],[#Data],[B]]

I thought the above was/is a structured table reference?

"Table1" is the name of the table in my examples. Same as "CRefs".

Kevin

"so no table structure to be passed as parameters"

That is not what I said. You can't pass a table or column name as a parameter. You can pass any part of a table expressed as a range as a parameter. This is a table column expressed as a range: Table1[B] is the entire B column in Table1.

So instead of using the header name, use.. what? The column letters?

can you give me a comparison of these two distinguished?

table column expressed as a range
and
table column expressed as a structured reference

please?

or a chapter since I now have the book ..

I needed this weeks ago:

=SUM(tblRegister[[#Headers],[Amount]]:[@Amount])

Barresse, Zack; Jones, Kevin. Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables (p. 70). Holy Macro! Books. Kindle Edition.

perfect

All tables. All the time

Zackly.

right then, read most of it. Will we be seeing a second edition?

Taking my op problem formula 1 and comparing it to the version that now works 2:

=LAMBDA(ifthis,          thenthat,            SUMIF(INDEX(CRefs[[#All],[CQNO]],1,0):[@CQNO],[@CQNO],    INDEX(CRefs[[#All],[Settled]],1):[@Settled]))(CRefs,CQNO,Settled)
=LAMBDA(ifthis,ifthisrow,thenthat,thenthatrow,SUMIF(INDEX(ifthis,1,0).             :ifthisrow,ifthisrow,INDEX(thenthat,1).              :thenthatrow))

Select allOpen in new window

Where:
INTABLE (see op) is deleted and these (clumsily named) parameters are passed:
 
Giving it the name "Cumulate" (now possible without table fields etc included) this works:

=Cumulate((CRefs[[#All],[CQNO]]),[@CQNO],(CRefs[[#All],[Settled]]),[@Settled])

Select allOpen in new window

Here is the test file:


EE-LAMBDA-IN-TABLE-ANS.xlsx

So, while I have a certain satisfaction in getting this to work, the original non Lambda formula is easier to read and shorter, which is an advantage of using a structured table in the first place:

=SUMIF(INDEX([CQNO],1):[@CQNO],[@CQNO],INDEX([Settled],1):[@Settled])

Select allOpen in new window

=LAMBDA(ifthis,ifthisrow,thenthat,thenthatrow,SUMIF(INDEX(ifthis,1,0):ifthisrow,ifthisrow,INDEX(thenthat,1):thenthatrow))

Select allOpen in new window

Anthony

And thus we begin to see the limited gains and unwanted complexities of the LAMBDA function. It is far from what it was originally hyped and proposed to be. It's a start but it's trying to shoehorn a new concept into an existing framework that is just not built to support it. Remember that Excel is a derivative of 1-2-3 which was the first real spreadsheet back in the early 1980's. Excel - still to this day - has a reference to Lotus in the advanced settings. The formula/grid structure continues to be the backbone of the Excel worksheet just as it was when Lotus first introduced 1-2-3 four decades ago.

Refactoring VB into Excel as VBA was truly revolutionary. LAMBDA is a joke by comparison. Maybe Microsoft should revisit the old Excel 4.0 Macro system - which is also still supported and which provides pretty much everything a Turing system requires except the ability to call a macro with parameters and return a result.

The entire Excel automation ecosystem at the moment is a bit of a mess. We have Excel 4.0 Macros (unsupported), workbook add-ins, VBA (mostly unsupported), JavaScript/HTML Apps/add-ins (more difficult than VBA and incomplete functionality), .net add-ins, and now LAMBDA. Not to mention all the false starts to replace VBA that never saw the light of day.

If Microsoft really wanted to provide automation or reusable "functions" without VBA or any of the other automation technologies mentioned above, they would provide a real UI/UX experience to support it and not just another function that requires named formulas to realize it's hyped potential and can't be debugged.

Kevin

Lotus 123. From 1984 V1, a 6 inch green crt screen, I had over 100,000 (edit: prolly half that!) hours driving 123 if my sums are right. 15,000 developing one app in 1988/90 etc or so, all using LDL a.k.a. 123 Macros, which compared to VBA were a dream. Those macros were also known as one of the most "hostile" languages on the planet, granted I had a complier that added 150 functions. I could certainly pass parameters, create subroutines, menus and probably a lot more I have forgotten. I still have my app here running under XP; it's a bit quicker than on the original 8088! Most of my spreadsheeting in the present is simply implementing what I did back then, because these days the computing power no longer forces us to hard code formulas into code so as to save calc time in addition to manual calc and display updates off etc. As regards those parameters I can't remember if returned results were always delivered to a cell or unseen; the former methinks. I do recall I had 2,500 range names and over 12,000 cells of (much dynamic) macros, all then compiled to exe(cutable).
These days that is all pre-history and happy memories. Thanks for nudging me down memory lane. :-)

VBA and all the others shut out users. They are programming of the first order and disempowered the user-base creating an elite many of whom know all about the engine and not much about where to drive it. Present company excepted! I have well seen that for myself and distinctly noted your recognition of debits and credits in your book.

I did not know most of those things existed. Maybe I should have a dekko at Excel 4 macros; I seem to recall they may have been 123 macro like, but they still require the file be exposed to VBA vulnerability.

My fingers still know all the 123 slash commands... and of course I dipped in to Multiplan and its R1C1 system which a i u i became Excel.

{R 3}{D 2} right three down two ;-)

Edit: and of course we had {LET....}

Anthony


Click Macro Settings, and then select the Enable Excel 4.0 macros when VBA macros are enabled check box. You can then select Disable VBA macros with notification or Enable all macros (not recommended; potentially dangerous code can run).

Don't go to Excel 4.0 Macros just yet. They are really just macros or scripts that are triggered with an action. There is absolutely no support for functions or parameters to routines.

My comment was that if Microsoft was serious about providing the ability to create parameterized functions using built-in functions ala LAMBDA then they should really look at reviving Excel 4.0 Macros and implement a way to define a function there versus shoehorning LAMBDA into the mix and letting us spin down the rabbit hole of recursive logic that, bless their demented little hearts, they limited to a fixed number of calls and then reduced that by the number of parameters passed. It's an irresponsible tease that, while a fun exercise in how NOT to implement a Turing machine, has really only made us aware of Excel's limitations from a programmatic perspective and now want more. I learned a long time ago that if I can't provide a product that doesn't meet 80% of the user's expectations, then don't do it until I can. And the expectations that make up that 100% is driven mostly by what I do provide. In other words, without LAMBDA, Excel worksheet life would be just peachy and no one would be pining for the unavailable. Toss us a grossly unpolished and limited LAMBDA function and look what it has done to you!

Kevin

aye, but I've had a lot of fun (thanks :-) ). Picked up loads of useful improvements to my spreadsheeting, and counting. Especially Tables. Granted I have had adroit help. I can completely see your frustration with MS' struggles from a developer's point of view.

I was surprised finding myself learning about recursion having assiduously avoided it for so long. Also surprised to find myself spending many happy hours understanding what you and Brad come up with. It's always a humbling experience in here, a healthy mental workout for me, no bad thing.

I think the attraction to LAMBDA for me is merely the ability to convert what may be hundreds of repeated complex formulae (e.g. copied down) into only one simple (looking) one. Also readability.

Is the original presentation by Microsoft published anywhere?

Must abed, one day I will be writing to you when not falling asleep (2:45am here)

Anthony

The LAMBDA announcement: https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambda-turn-excel-formulas-into-custom-functions/ba-p/1925546

Kevin

what was this about?

definitely lots of room for improvement there. We’ll also add some more array manipulation functions in the coming months that will help you build even more powerful lambdas taking more advantage of dynamic arrays

Who knows. Maybe we'll get SEGMENT or something like it.

Kevin

they just need a dinosaur on their team.

Anthony