Announcing LAMBDA Helper Functions: Lambdas as arguments and more

[Chris_Gross]

Today we are releasing to our Beta customers seven new LAMBDA functions. These functions aid in the authoring of re-usable LAMBDA functions while also serving as stand-alone functions themselves. Additionally, we are sending the LAMBDA function to Current Channel Preview.

LAMBDA as arguments – One exciting addition to Excel’s formula language is that LAMBDA is now exposing the ability to be treated as an accepted value type with the addition of new functions. This is an important concept which has existed across many programming languages and is tantamount to the concept of lambda functions in general.

LAMBDA to Current Channel Preview – With this latest set of updates we are progressing the LAMBDA function to customers who are a part of the Current Channel Preview audience. In addition we are also introducing the ability to define optional arguments.

Lambdas as Values

Before diving into the new functions and how they work, it’s important to understand the concept of functions as values.

Over the past couple years, we have been teaching Excel how to understand new types of values. Some of the most recent additions have been Data Types (Wolfram, Geography, Stocks, Power BI, and even Power Query can create Data Types), and Dynamic Arrays. Lambdas continue this journey by allowing Excel to understand functions as a value. This was enabled by the introduction of LAMBDAs but not exposed to any functions.

This is exciting, because it means that things which were previously impossible, near-impossible, or arduous can now be achieved by writing a LAMBDA and passing it as a value to a function.

For example, imagine you had a constantly growing list of values and needed to generate a list of booleans which check for values that are both greater than 50 but less than 80.

You could author one function for each value which checks the condition but this is error prone and requires a lot of duplication for something pretty basic. For reference, the duplicated formulas might look like this (with one formula for each value):

=AND(A2>50, A2<80)
=AND(A3>50, A2<80)
...

This is the type of scenario where LAMBDAs can be applied and more specifically, a great example of where to use the new MAP function.

MAP

With MAP, you can easily author a LAMBDA which applies a formula to every value and returns the result. MAP’s superpower is value transformation.

There are a few new concepts which we will cover shortly, but the formula looks like this:

=MAP(Table1[Values],LAMBDA(value, AND(value>F2, value<F3)))

The result is an array of boolean values, corresponding to the values in the Table:

With just one formula, you can achieve something pretty powerful! Let’s pause to understand what’s going on.

How the functions work

The new MAP function takes one (or many) array/range references and passes each value from the supplied array/ranges as a parameter to the LAMBDA function (in this case Table1[Values]). The fact that LAMBDA expects parameters from MAP is an important concept to remember as you check out each of the new functions, since it’s different for each one.

In our case we have one array, so the LAMBDA is pretty simple, and expects only one parameter.

Focusing in on the LAMBDA itself, you can see that in this example we chose to call our single parameter “value”, but you can call it by any legal parameter name:

LAMBDA(value, AND(value>F2, value<F3))

Another key concept to understand, with these new functions, is that the power comes from the fact that Excel will do a calculation for each value supplied and then make LAMBDA do the heavy lifting. In this particular case it will return an array of results but there are other functions we are introducing today which will return only one value.

REDUCE

While MAP proves useful for transforming a list of values, let’s say that we wanted to count how many items met the condition.

This is where REDUCE comes in handy and is a perfect opportunity to talk about the second function on our list.

This time, we are going to re-use the same logic as before but wrap the calculation in an IF to do our counting. The formula is rather simple, like before, being that we only need one function call:

=REDUCE(Table1[Values], LAMBDA(accumulator, value,
     IF(AND(value>F2, value<F3), 1 + accumulator, accumulator)
))

 REDUCE does, hopefully, what it sounds like. It reduces a list of values into one value by making use of a LAMBDA.

The major difference with REDUCE is that it makes use of two parameters in its LAMBDA value:

• accumulator: The initial value returned by REDUCE and each LAMBDA call.
• value: A value from the supplied array

The other thing to note is the first, optional argument, which is the [initial value] for the accumulator. In our case we want it to be 0.

The accumulator is what allows us to write our own custom aggregation-IF function (you can even write PRODUCTIF with REDUCE) and can be seen if you focus on the calculation portion of the LAMBDA:

IF(AND(value>F2, value<F3), 1 + accumulator, accumulator)

The final result, in my opinion, is elegant and self-contained.

 

BYROW & BYCOL

The next two functions which we are introducing are BYROW and BYCOL. These functions take an array or range and call a lambda with all the data grouped by each row or column and then return an array of single values. Hence the name.

These two functions are great because they allow for calculations which were previously impossible because they would produce arrays of arrays.

For example, lets imagine we had some data which tracked the temperature for every day of a week. We are interested in seeing days where the average temperature for the week is greater than 85 degrees Fahrenheit.

Without BYROW, we would need to create a helper column and calculate averages using a bunch of formulas and then likely use filter UI or some other wizardry.

With BYROW, we can author a LAMBDA which meets our constraints and then pass the result to the FILTER function.

This is great because as we add new data week over week, our calculations are set and we don’t have to reauthor our workbook.

The formula looks like this:

=FILTER(Table3, BYROW(Table3, LAMBDA(week, AVERAGE(week)>85)))

And upon execution, we can quickly see which weeks were extra hot!

Now, let’s suppose, we wanted to see this data in Celsius. We can make a quick update and get results in degrees Celsius by wrapping our call with a MAP and make use of CONVERT.

=MAP(FILTER(Table3, BYROW(Table3, LAMBDA(week, AVERAGE(week)>85))), LAMBDA(value, IF(ISNUMBER(value), CONVERT(value, "F", "C"), value)))

 

Complete list of functions

While I could create examples for days, I can’t possibly include everything in one post and our documentation does a great job of explaining the specifics of each function, so be sure to check them all out!

Name	Description	Argument	Argument	Argument	LAMBDA_Param(s)
MAP	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.	array1: An array to be mapped.	lambda_or_array: A LAMBDA which must be the last argument and must have a parameter for each array passed, or another array to be mapped.	[lambda_or_array…]: A LAMBDA which must be the last argument and must have a parameter for each array passed, or another array to be mapped.	array1: A value from array1 [array2…]: A value from an additional array.
REDUCE	Reduces an array to an accumulated value by applying a LAMBDA function to each value and returning the total value in the accumulator.	[initial_value]: Sets the starting value for the accumulator.	array: An array to be reduced.	lambda: A LAMBDA that is called to reduce the array. The LAMBDA takes two parameters, accumulator and value.	accumulator: The returned value from LAMBDA value: A value from array.
SCAN	Scans an array by applying a LAMBDA  to each value and returns an array that has each intermediate value.	[initial_value]: Sets the starting value for the accumulator.	array: An array to be scanned.	lambda: A LAMBDA that is called to scan the array. The LAMBDA takes two parameters, accumulator and value.	accumulator: The returned value from LAMBDA value: A value from array.
MAKEARRAY	Returns a calculated array of a specified row and column size, by applying a LAMBDA function.	rows: The number of rows in the array. Must be greater than zero.	cols: The number of columns in the array. Must be greater than zero.	lambda: A LAMBDA that is called to create the array. The LAMBDA takes two parameters, row_index and column_index.	row_index: The index of the row. column_index: The index of the column.
BYROW	Applies a LAMBDA to each row and returns an array of the results.	array: An array to be separated by row.	[lambda]: A LAMBDA that takes a row as a single parameter and calculates one result.		value: A value from array.
BYCOL	Applies a LAMBDA to each column and returns an array of the results.	array: An array to be separated by column.	[lambda]: A LAMBDA that takes a column as a single parameter and calculates one result.		value: A value from array.
ISOMITTED	Checks whether the value is missing, and returns TRUE or FALSE.	argument: Is the value you want to test, such as a LAMBDA parameter.

LAMBDA Improvements

The last thing to cover is the set of improvements we have made to the LAMBDA feature in general. As aforementioned, we are sending LAMBDA to Current Channel Preview and have added support for Optional Parameters in both Current Channel Preview and Insiders: Beta.

Optional Parameters

LAMBDA now supports optional parameters. To make use of optional parameters, all you need to do is wrap the optional name in “[]”.

For example:

=LAMBDA(param1, [param2], IF(ISOMITTED(param2), param1, param2))

This lambda will return the value of param1 if param2 is omitted and otherwise return the value of param2.

Try it yourself

If you are in our Beta or Current Channel Preview program, you can try out LAMBDAs yourself. We’d love your feedback as we continue to improve on this new capability.

The new LAMBDA functions are available to 50% of the channel today and over the coming weeks we will increase the flight, pending no bugs or other issues.

Note: The LAMBDA function is available to members of the Current Channel Preview program running Windows and Mac builds of Excel. The LAMBDA helper functions are available to members of the Insiders: Beta program running Windows and Mac builds of Excel

Join the Office Insider Program and choose the Beta or Current Channel Preview to get early access to LAMBDA in Excel.  Send us feedback via send a smile, or post in the Excel Tech Community.

Stay connected with us.  Join our Excel Tech Community.  Let us know what you think, and we’d love to see what you build with LAMBDA!

Availability

The new LAMBDA functions are now available to Office Insiders running Beta Channel Version 2108 (Build 14312.20008) or later on Windows, or Version 16.52 (Build 21072100) or later on Mac.

The LAMBDA function is now available to Office Insiders running Current Channel Preview Version 2107 (Build 14228.20154) or later on Windows, or Version 16.51 (Build 21071100) or later on Mac.

Learn More

To learn more about LAMBDA and the new functions, check out our accompanying post on the Insiders blog which highlights some of the other functions or the help topics below.

LAMBDA Help

New LAMBDA Functions

• MAP
• REDUCE
• SCAN
• MAKEARRAY
• BYROW
• BYCOL
• ISOMITTED

[Sergei Baklan]

Thank you, supporting functions and availability on Current Preview is a great step forward!

[Govert van Drimmelen]

Would you please consider letting us use these helpers with user-defined functions in addition to the LAMBDA functions? I would love to say something elegant like =MAP(A1#, MyUDF) instead of the cumbersome =MAP(A1#, LAMBDA(a, MyUDF(a))).

For UDFs registered with the C API we can implement this in our own version of the MAP and similar UDFs already, since using the UDF name in formula without parentheses returns the internal Register ID which can be evaluated using the C API. But it would be neat to do the same with these new built-in helpers that take a LAMBDA, and even have a way to pass in UDFs made with VBA (I use the string name to call VBA functions, but that's not ideal).

[joewithoutagun]

This is awesome news, @Chris_Gross

The golden trio: FILTER, MAP, REDUCE is finally coming to the Excel.

MAKEARRAY
Maybe it's better to add lambda as an optional parameters to SEQUENCE()? If not then rename it to just ARRAY()?

MAP, REDUCE, FILTER

Maybe it's better to add Add BYCOL and BYROW as an optional parameters? If omitted than go 1 by 1 left-bottom.

ISOMITED

Maybe it's better to change the logic to ISPRESENT? Most of the time optional parameter supposed to be not provided, I think it's logically more correct to check if it's present rather than it's not.

Can optional parameter also be set to default parameter initially? LAMBDA(A = 1; B).

SCAN

Not sure if this name is sound. AFAIU the only difference between MAP and SCAN is that SCAN can work with not only current value of an array but also with a previous one.

In doc example =SCAN("",A1:C2,LAMBDA(a,b,a&b)) a,b means current and previous one.

So maybe it's better to harmonize both functions into 1: MAP.

Overall, this is really great news, well done!

[Viz]

For the reduce function, the initial value seems to mandatory and not optional. Can you please reconfirm?

If I try the exact example you have given, MS Excel throws error stating "You have entered too few arguments for the function"

I had to give the initial value as 0.

[Sergei Baklan]

@Viz , optional parameters shall be at the end of the parameters list. From that point of view documentation which marks first parameter as [initial_value] is incorrect. But, as before, first (and other) parameter could be missed.

=lambda( [x], y, x+y)(,2)  doesn't work

=lambda( [x], y, x+y)(2)   doesn't work

=lambda( x, y,   x+y)(,2)  works

=lambda( x, [y], x+y)(2)   works

=lambda( x, y,   x+y)(2,)  works same way as previous

In addition

=lambda(x, y,  x+IF(ISBLANK(y), 10, 11))(2,) and

=lambda(x,[y], x+IF(ISBLANK(y), 10, 11))(2)

return the same. You may use ISOMITTED() instead of ISBLANK(), just the latest is shorter and more familiar.

[Peter Bartholomew]

Sadly, once more I will have to curb my impatience to gain access to these new functions within the beta channel.  Hopefully not too long this time!

The functions look manageable from a user perspective and address the concerns I expressed when dynamic arrays first appeared (namely the handling of 2D arrays as an array of 1D arrays and the problem of accumulation being treated as a circular reference - no array breakup).  One thing that does concern me is that the order of the parameters in SCAN and REDUCE appears to be illogical.  In either case, the main parameter of the function is the array, the initial value represents a minor (optional) adjustment. 

A similar situation arose using Charles Williams's ACCUMULATE function

FastExcelV4VS2019 (decisionmodels.com)

in which the function was first implemented as starting with an initial value but was then changed (breaking backward compatibility) to ensure that the flow variable came first in a balance calculation.  Starting the parameters list with an omitted initial value gave rise to recurrent user-errors when the function was applied in its original form.

I believe 

=SCAN (array, [initial_value], LAMBDA(value, accumulator))

would provide a better signature for the function.

[Sergei Baklan]

@Peter Bartholomew , I fully agree, order of parameters shall be changed, now it's illogical plus we have to work with missed, not omitted parameter. Hope another usual 50% will be covered soon and you'll have access to helper functions.

IMHO, even more important that lambdas are available on Current Preview. That means GA, at least for Current, will be soon (I hope) and we may start using them in real projects.

[Bill_Bosler]

Please introduce COMMENTS in both LAMBDA and LET 

Perhaps "/* Comment ... */"

comments are GOOD :)

With Comments, we will remember what the Sophisticated Functions are doing :)

[Sergei Baklan]

@Bill_Bosler , that's not about the lambdas, that's about the editor. I don't think they will be available in formula bar editor (on the other hand why not, we have them in Power Pivot). Most probably they will come with right pane editor (both /*..*/ and //...) prototype of which you may see in the middle of this video Microsoft Research sponsors POPL 2021 - Microsoft Research. It's coming.

[ron S.]

You say "beta users".

That is so 1980.

The MS terminology is "Office Insider".  Get on the terminology train, stop confusing your readers!

PS: changing the name from Office 365 to "Microsoft 365" is stupid. It is just confusing. So, Windows is not part of "Microsoft", well it's not part of the "MIcrosoft 365" bundle ...  What about Visio and Project? They are not part of any "Microsoft 365" bundle (although I hear a few business bundles may be getting Visio online ... maybe).

PS Bill Bosler: comments are a very good idea. Submit a feedback to MS. Did you know can add comments to formulas using the N() function.  Maybe that will work for Lambda and Let? I haven't tested yet (not an Office insider).

Leave a comment in a formula             N
https://exceljet.net/formula/leave-a-comment-in-a-formula
To leave a comment in a formula that returns a numeric result, you can use the N function.
=formula+N("comment")
.

[mtarler]

@Bill_Bosler  Actually for Lambda you already have the Comment field in the name manager you can use.  i know it isn't the same but you could re-create the formula there with the comments.  as for LET and actually other things (i.e. I use this technique now for conditional formatting rules) you can add 'comments' using 'dummy statements'. For example in conditional formatting I add the following so i have a hint what that formula is for:

=OR("Mark special cases"="", [put actual formula here] )

I know it is far from ideal and not efficient but at least a work around.

[ron S.]

PS: here is a link to a feedback I submitted. Please add comments and votes

https://aka.ms/AAdb3tf

[Peter Bartholomew]

As @mtarler suggests, there is some scope for comments both within defined names and the LET function, e.g.

[lori_m]

Great news! This is the much needed combinators release enabling power users to extend formula solutions dynamically and developers to port functional code to worlsheets. To reiterate some earlier comments,

- Function arguments in MAP, etc. should allow UDFs in addition to LAMBDA
- Order of parameters in REDUCE and SCAN should be consistent with Power Query List.Accumulate and accept arrays as accumulators

[Sameer_Bhide]

@Chris_Gross 

Great work by the Excel team -  This is a nice improvement to improve the usability of LAMBDA,

However it would have been nice if the Examples provide would demonstrate something that was not possible earlier

For the MAP Ex : =(T[Values]>F2)*(T[Values]<F3) - produces the same Boolean array

For the REDCUCE Ex : =SUM((T[Values]>F2)*(T[Values]<F3)) produces 3

For BYROW Ex :  =FILTER(D,MMULT(WK,SEQUENCE(COLUMNS(WK),,1,0))/7>85) produces the same weeks

Cheers Sam

[Peter Bartholomew]

@lori_m 

Hi Lori, it is curious that PQ should have Accumulate as a list operator whereas, to date, such capability has been missing from Excel.  That is, Scan only really makes sense in the context or arrays and not lists, since the result depends on sort order.

[by a list, I refer to a set of objects in which order is unimportant - normally accessed by key versus an array, by which I mean an ordered list - normally accessed by index or a combination of such].  Fortunately, it appears that list.accumulate does order the parameters is the order we are requesting.

Since I am unable to try the functions at present, can you tell by inspection of the syntax, whether the SCAN combinator is applicable to amortisation and depreciation schedules in which there is both a percentage change based upon the prior value and an additive change (loan repayment or capital purchase).  I am hopeful but find it difficult to find my way through the calculation as an exercise in mental arithmetic, you can at least revert to the Python equivalent. 

If I wanted to apply the SCAN function to a ballistics calculation, the state needs to be represented by a vector of position and velocity, so the accumulator needs to be a vector, as you propose.

There have been two mentions of UDFs.  So far they have been carried through within the context of dynamic arrays but it is one of the hopes that I have of Lambda functions that the need for UDFs is reduced dramatically.  My main use for VBA is not for writing UDFs but, rather, for writing event handlers (sadly not possible in TypeScript?).

[lori_m]

@Peter Bartholomew 

Examples of amortisation and vector accumulations are given here and here, the python code should easily adapt to Excel syntax. My suggestion would be to infer direction of accumulation in REDUCE and SCAN from the input vector shape: vertically for column vector, horizontally for row vector or reading order for a matrix.

[tboulden]

@lori_m @Peter Bartholomew I'm working through my examples spreadsheet from Peter's last thread , having some success and having to change some things. Notably, I can replicate SCAN with REDUCE and a init array if I define a stacking function. I have the amortization example working with SCAN, and for running totals and SCAN by row/col, I've got versions working with MAKEARRAY and SCAN/REDUCE since BYROW/BYCOL return values and not arrays. More to come!!

[lori_m]

@tboulden 
Great you're able to test these new features. Can you confirm if performance is now comparable with filling down single cell formulas? It looks like BYROW and BYCOL are essentially the same as the vmap/hmap functions from that thread, is that correct?

To clarify the suggestion above, I was hoping that SCAN could replicate those vscan/hscan functions by using a row or column vector as initial_value (a matrix input would need to subdivide the array and would most likely overcomplicate things). It also hadn't occurred to me that one could use REDUCE to solve equations as in the logistic map example.

[Viz]

@Sergei Baklan, thanks for that illustrations. It was really useful.

However, I was referring to @Chris_Gross example on the Reduce function.  In his example, he did not specify the initial value in the Reduce function and he mentions that it is optional. I, however, got an error stating that it is too few parameter. I had to provide the initial value.

=REDUCE(Table1[Values], LAMBDA(accumulator, value,
     IF(AND(value>F2, value<F3), 1 + accumulator, accumulator)
))

For some reason, I had access to these new function yesterday but it is now gone. So, unable to share the screenshot.

[Sergei Baklan]

@Viz , perhaps Chris used even more modern lambdas, perhaps just skipped comma with copy/pasting. What works is

=REDUCE(,Table1[Values], LAMBDA(accumulator,value,
     IF(AND(value>F2, value<F3), 1 + accumulator, accumulator)
))

[tboulden]

@VizREDUCE's first parameter is optional in that it can be left blank, but function call still needs to have a comma to preserve the parameter order. Example provided should have a zero so that it counts entries between the criteria; if leave first parameter blank, we're counting entries between criteria and adding them to first entry without checking first entry against criteria.

@Chris_GrossMight update formula example for clarity above.

[tboulden]

SCAN example to clarify REDUCE example.

[Sergei Baklan]

@tboulden , I still think that first parameter is not optional. But yes, it could take blank argument.

[Peter Bartholomew]

I have bad experience of parameters that are optional in the sense that they may be omitted entirely, as opposed to merely left blank (the normal situation, I think).  It is possible to use a function of the form

= VSTACK(array1,array2,[array3],...)

without realising that the true signature is

= VSTACK([fillCharacter],array1,array2,[array3],...)

It all goes so well, up to the point at which array1 just happens to be a scalar.  At that point array1 simply vanishes from the output with no apparent reason.  The optional lead parameter is very much a strategy of last resort, to be avoided if at all possible.

[Sergei Baklan]

@Peter Bartholomew , in general function shall know how to pass arguments to parameters, it shall be some logic for that. I know 3 ways

- named arguments. That's not our case, they are not used here.

- based on order of arguments. When optional arguments are at the end of the arguments list, if they are omitted nothing is passed to parameter and default value is used.

- based on argument type. Perhaps that is used in VSTACK() and perhaps that was assumed in REDUCE(). For them if first argument is not a scalar type it considered as omitted and default value is taken for the parameter.

Yes, lambdas, as well as some other functions, e.g. =XMATCH(,range), from very beginning support missed arguments having blank as the default value for the parameter (which is transformed to zero or empty string depends on type). But that's not exactly the same as optional argument.

[tboulden]

Interestingly, the recursion limits demonstrated here are still in place as outlined, however if I perform the same using pair() within REDUCE, I max out at 1734; 1735 crashes Excel.

=REDUCE(,SEQUENCE(1734),LAMBDA(acc,val,pair(val)(acc)))(first)

[Sameer_Bhide]

@Chris_Gross 

LAMDBA's are still not accessible when stored inside an Add-in. How do we distribute them until this happens ?

Cheers

Sam

[ACook84]

I only wish, as a member of the Office Insiders Beta Channel, that you wouldn't have separate release rings within the channel. Beta member's signed up to purposely get and test the newest of the new Excel features. I implore you to use the 'Current Channel (preview)' if you want to segregate further - that way, if someone is unhappy having to wait, they can always switch to Beta. 

[Sergei Baklan]

@ACook84 , that's a good point discussing for years. Beta shall be beta for everyone. If technology requires gradual deployment it shall be within 1-2 weeks, not within few months.

[lori_m]

I predict these new functions will become a mainstream approach to the development of models in the coming years once their power and flexibility becomes widely appreciated.  Some minor updates might help users adjust to the functional approach too such as

- Default to SUM if LAMBDA is omitted

- Allow function_num parameters (1-11) in lambda arguments like in SUBTOTAL and AGGREGATE

Then one could use friendlier formulas like SCAN(,Array) for a running sum or BYCOL(Table,4) to find the maximum in each column.

[Peter Bartholomew]

@Chris_Gross 

Thank you for expediting the Beta Channel rollout this time.  You would have had some very unhappy users if it had dragged out!

cc @ACook84 , @Sergei Baklan 

@lori_m 

You are a very brave man to make any predictions as to how this experiment will go!

I woke up to MAP and couldn't resist trying it.

Anonymous
= MAP(month,
    LAMBDA(m,
      AVERAGE( 
        FILTER(Temp, MONTH(date)=m)
  ) ) )

Named
= MAP(month, AVERAGETEMPλ)

The joy for me is that I was able to dispense with the concept of relative referencing, which I have despised for a number of years, regarding it rather as 'the poor man's array'.

It may have been a moment of joy for me, but I can't see the majority of spreadsheet users changing the practice of years anytime soon.  I rather expect to see a very long period in which there are two Excel communities, one embedded within traditional working practices and the other embracing the new.  Add to that, the increasing use of AI to support program development which may mask the differences, and it will be an interesting challenge knowing how to answer any given user question on the various Excel forums.

[Sergei Baklan]

@Peter Bartholomew , most complex for me with this approach is to generate understandable for people names. Includes me few month later. Lot of them even for simple cases.

months = 12;
month = SEQUENCE(months);
getMonth = MAP(date, lambda(a, MONTH(a)));
average4month = lambda(m, AVERAGE(FILTER(temp, getMonth=m)));
averageByMonth = MAKEARRAY(months,1, LAMBDA(r,c, average4month(r)));
result = CHOOSE({1,2}, month, averageByMonth);

[lori_m]

@Peter Bartholomew 

Thanks for posting these screenshots, I doubt I'll have access to this functionality for a while from current channel so will follow your posts with interest.

My optimism follows other approaches to modelling including computational notebooks and scripting languages that have been gaining acceptance in recent years and represents a growing community who are willing to move with the times - admittedly still a minority of users.

The suggestions above were targetted more at a typical office worker who may well want to use these functions but without necessarily including LAMBDA which may be a little daunting initially. For an indication of potential interest, a uservoice request for SUMROWS and SUMCOLS equivalent array functions has 300+ votes.

[Sergei Baklan]

@lori_m , after lambda is in GA for few years we will have much more votes. It's hard to expect thousands of voters at this stage.

I'm not sure about typical office worker. From this point of view lambda is in between regular formulae and VBA/Office Scripts. Don't think typical office worker will use recursion and like.

[lori_m]

@Sergei Baklan 

One shouldn't expect typical users to work with LAMBDA expressions as you say. My thought was that functions like BYCOL and SCAN could support basic aggregation needs via function names or numbers in a future update as a simpler alternative to LAMBDA. 

[Sergei Baklan]

@lori_m , yes, that will be nice. Afraid for that aggregation functions like SUM, which billion of users use every day, shall be re-written. Or new SUML or like are to be introduced.

Another related point here is conversion range to array. For example, =MAP(A1:B2, lambda(v, v) ) doesn't work, but =MAP(A1:B2, lambda(v, --v) ) works if we speak about range with numbers.

[lori_m]

@Sergei Baklan 

The suggestion further up was to use the same function_num input as SUBTOTAL / AGGREGATE i.e. 9=SUM. Then it could be possible to write a LAMBDA function for this purpose by checking type of the input: TYPE(LAMBDA(..))=128 vs TYPE(number)=1.

I agree that more significant changes may be needed to use SUM as an argument. Perhaps a _xlfn. prefix could be a possibility with minor adjustments to the function code as this is the reserved name used in the file contents [=_xlfn.SUM(1,2) -> =SUM(1,2)], that is pure speculation however.

And strange that you have to convert ranges to arrays for use in MAP,  a unary + sign might work for numbers or text but that is not intuitive.

[Sergei Baklan]

@lori_m , yes, parameter could be a solution.  As for the converting the question is not how to convert, the question is why we shall to use explicit conversion. 

[Peter Bartholomew]

@Sergei Baklan 

I agree that both planning meaningful names and knowing their significance at a later date can be challenging.  As far as I can see, though, there are no better alternatives.  If one uses deeply nested formula one is worse off; then one only has the partial formula.  Using LET, there is also a clue as to the significance of the calculation within the name which is a bonus. 

The sheet or workbook scoped names have the opportunity to insert a comment.  I try to cover:

1. the business significance of the named object

2. how it is calculated

3. why it was calculated and its intended use

For a named Lambda function, I keep the description short and focus on describing the function signature in terms of its parameters.

Yes, as Dan Bricklin suggested, it can be tedious, but I believe it is worth the effort if the workbook is important.  As an aside, I enclose a LET formula with a multitude of names.  To find out what is going on one just has to work through line by line.  It is an effort but the formula is doing many things.

= MAP(ValuationDate,
      LAMBDA(vDate,
         LET(
  \1, "Select Investment",
           InvestmentAmounts, XLOOKUP(@Investment, Investment, InvestmentTable),
  \2, "Set up filter",
           nonBlank?, ISNUMBER(InvestmentAmounts),
           cashTransaction?, LEFT(EntryType, 4)="Cash",
           withinDate?, TransactionDate<vDate,
           valuationDate?, TransactionDate=vDate,
           combined?, nonBlank? * (cashTransaction?*withinDate? + NOT(cashTransaction?)*valuationDate?),
  \3, "Apply filter",
           filteredDates, FILTER(TransactionDate, combined?),
           InvestmentFiltered, FILTER(InvestmentAmounts, combined?),
  \4, "Evaluate IRR",
           XIRR(InvestmentFiltered, filteredDates)
  ) ) )

[Sergei Baklan]

@Peter Bartholomew , any systematic approach is okay, perhaps in few year we will have common coding style for lambdas, who knows. Just my personal preferences are bit different, in particular

- prefer one block of comments in the heard of the block rather than commenting each step;

- prefer pronounceable names, e.g. nonBlank? is not  pronounceable, but IsNonBlank is pronounceable

But these are minor things compare to other issues like manageability.

[Peter Bartholomew]

@Sergei Baklan 

I am perfectly happy to concede that point.

The current challenge is that most spreadsheet users would not recognise the formula as an Excel worksheet formula; I have received apologies from users that they are unable to understand the PowerQuery or do not know VBA.  I have some sympathy in that for someone schooled in the traditional techniques of spreadsheet authoring, such formulas will be utterly alien.

[Bill_Bosler]

@Peter Bartholomew, LAMBDA and LET really need an IDE that shows the intermediate values, etc.

My thought is to put a second icon for the IDE beside the Formula Bar icon.

That IDE should provide for comments, like /* Comment */.

Then, the LAMBDA / LET can be "well documented" :)

[Peter Bartholomew]

@Bill_Bosler 

Agreed.  Just as rich data types support a floating form to provide the detailed properties, so an array formula could do with a popup grid to show the current values of a selected name.  That would of great assistance when debugging a formula or for ongoing development.  @tboulden  has explored some ideas along these lines for LET.

A syntax for comment lines would be useful (my use of \1, \2 etc. just builds on the fact that they are legal names that I have not otherwise used).

[jmhiggin]

These new functions are fantastic.Absolutely

However, it would be extremely useful to have the helper functions supply the lambda with the current column and row as parameters of the range for functions like map.

Something like:

MAP(array, array2,..., LAMBDA(current_row,current_column,value1,value2,...,LAMBDA))

I have played with a few scenarios where being able to perform conditional logic based on the column number would be extremely useful. I can imagine that there could be scenarios where conditional logic based on the row number would be useful as well.

Thanks again for all the great work.

[Teoxengineer2301]

Hello averybody,

My question is that I'm Office 365 user and can use Lambda function and its helper functions like Map, Reduce and Scan. However, I'm not able to use Makearray and isomitted functions. 

What is the main reason and how can I find or activate them?

Thank you

[Sergei Baklan]

@Teoxengineer2301 , looks strange. What exactly "not able to use" means - you don't see these functions at all (e.g. if start typing =isom) or something else.

By the way, if within lambda ISOMITTED() and ISBLANK() work the same way (not outside the lambda)

[Peter Bartholomew]

@jmhiggin 

When you say current row and current column, do you have the row and column array indices in mind, or is it the sheet row and column numbers that you wish to bring in to the calculation?  If it is the former, MAKEARRAY might meet the requirement.

[jmhiggin]

@Peter Bartholomew,

My inclination is the row/column array index. I think your suggestion of using MAKEARRAY should work. I will play around a bit more with this and see if it functions completely for my uses cases and if I can come up with any scenarios where it does not suffice.

Thank you for the suggestion.

[Peter Bartholomew]

@Sergei Baklan 

Have you noticed that we didn't get our wish that the optional initialisation of SCAN should follow the array being scanned, not precede it?  Using the function every day has convinced me that we were right.

Still, I am now making a special effort to remember and have now got my error rate down to 80% !