Announcing LAMBDA: Turn Excel formulas into custom functions
Published Dec 03 2020 08:00 AM 435K Views

LAMBDAfunction_Concept_USEME.png

Today we are releasing to our Beta customers a new capability that will revolutionize how you build formulas in Excel. Excel formulas are the world’s most widely used programming language, yet one of the more basic principles in programming has been missing, and that is the ability to use the formula language to define your own re-usable functions.

 

=LAMBDA

Simply put, LAMBDA allows you to define your own custom functions using Excel’s formula language. Excel already allows you to define custom functions, but only by writing them in an entirely different language such as JavaScript.  In contrast, LAMBDA allows you to define a custom function in Excel’s own formula language.   Moreover, one function can call another, so there is no limit to the power you can deploy with a single function call. For folks with a computer science background, you’re probably already familiar with the concept of lambdas, and the introduction of LAMBDA makes the Excel formula language Turing Complete...

 

Reusable Custom Functions

With LAMBDA, you can take any formula you’ve built in Excel and wrap it up in a LAMBDA function and give it a name (like “MYFUNCTION”). Then anywhere in your sheet, you can refer to MYFUNCTION, re-using that custom function throughout your sheet. I’ll show a couple examples below.

 

Recursion 

Reusable functions is reason enough to start taking advantage of LAMBDA, but there’s one more thing… you can do recursion. If you create a LAMBDA called MYFUNCTION for example, you can call MYFUNCTION within the definition of MYFUNCTION. This is something that before, was only possible in Excel through script (like VBA/JavaScript). I’ll also show an example below of how you can leverage this to build things that were not possible before without writing script.

 

Reusable Custom Functions

One of the more challenging parts of working with formulas in Excel is that you often get fairly complex formulas that are re-used numerous times through the sheet (often by just copy/pasting). This can make it hard for others to read and understand what’s going on, put you more at risk of errors, and make it hard to find and fix the errors. With LAMBDA, you have re-use and composability. Create libraries for any pieces of logic you plan to use multiple times. It offers convenience and reduces the risk of errors.

 
Station IDs

For example, imagine I have a list of station IDs, where the state is encoded in the ID, and I want to pull that value out:

 

StationID_1.png

 

There are many ways to do this with Excel functions, here’s how I did it (I’m sure many of you have much more efficient ways to doing this… forgive me… I know for example the team would remind me I should take advantage of the LET function)

 

=LEFT(RIGHT(B18,LEN(B18)-FIND("-",B18)),FIND("-",RIGHT(B18,LEN(B18)-FIND("-",B18)))-1)

If I take that formula and copy it down the column, I can get the results shown in the table above.

 

There are two challenges with this approach:

  • Errors - If I find an error in my logic that I need to fix, I have to go back and update it everywhere it was used, and I might miss some. Additionally, there’s added risk whenever you have complex formulas repeated over & over again as opposed to defined just once and then referenced. If for example, there are some station IDs that look like this “105532-872332-WA-73”, my formula would not work with those. If I find this error and want to fix it, I then need to go back to every cell where I used that logic and update it.
  • Composability/Readability - If I’m not the original author, it’s hard to know what the intention of that formula is (to pull out the location). It’s also hard to use this logic in combination with other logic, like if I want to take the station ID and do a lookup based on the calculated location.

Using LAMBDA, I can create a function named GETLOCATION, and put the formula logic in the definition for that function.

 

=GETLOCATION

 

=LAMBDA(stationID, LEFT(RIGHT(stationID,LEN(stationID)-FIND("-",stationID)),FIND("-",RIGHT(stationID,LEN(stationID)-FIND("-",stationID)))-1))

Notice I specify the arguments my function will take (in this case stationID) and the logic for my function. Now in my spreadsheet, I can simply write GETLOCATION as a formula and reference the cell that has the stationID, just like any other Excel function. If I notice I have an error, I fix it in one place, and everywhere that uses that function is fixed.

 

STATIONID_GIF.gif

 

Another added benefit, is that I can now compose that function with additional logic. For example, if I had a table of tax rates for each location, I could write this simple formula to return the rate based on the stationID.

 

=XLOOKUP(GETLOCATION(B18), table1[locations], table1[tax]).

 

OK, lot’s more to drill into here around how you can use this capability to build up a rich set of function libraries, make your sheets easier to understand and less error prone, etc. These functions can even take data types as arguments. We’ll post an example later of a custom function that takes two cities as input and calculates the distance between them using the geo coordinates and the radius of the earth to perform the calculation.

 

Let’s shift into the other big impact this will have on what you can build in Excel. This one is a bit more complex, but is pretty revolutionary for us… recursion.

 

Recursion

One of the big missing pieces in Excel formulas has been the ability to loop… to repeat over a set of logic at a dynamically defined interval. There are ways that you can manually configure the interval at which Excel recalculates to mimic this to an extent, but it’s not inherent to the formula language. That changes with LAMBDA.

 

Let’s take an example, albeit a bit contrived, but it’s a simple way to get the point across.

Imagine I have a set of strings, and I want to specify which characters should be removed from those strings dynamically:

 

StringClean_1.png

 

Because the set of characters you’re specifying are not static, there really isn’t any good way of doing this. If you knew it was always a fixed set of characters, you could do a ton of nested logic, but that would be pretty complex and error prone to author. Not to mention, if the number of characters to be removed was larger than what you’d accounted for, it would fail.

 

With LAMBDA, we can create a function called REPLACECHARS that references itself allowing you to iterate over the list of characters to be removed:

  

=REPLACECHARS

=LAMBDA(textString, illegalChars,
     IF(illegalChars="", textstring,
       REPLACECHARS(
       SUBSTITUTE(textString, LEFT(illegalChars, 1), ""),
       RIGHT(illegalChars, LEN(illegalChars)-1)
)))

Notice that in the definition of REPLACECHARS, there is a reference to REPLACECHARS. The IF statement says if there are no more illegal characters, return the input textString, and otherwise remove each occurrence of the leftmost character in illegalChars. Recursion kicks in with the request to call REPLACECHARS again with the updated string, and the rest of illegalChars. This means it will keep calling itself until it has parsed over every character to be removed, giving the desired result.

 

REPLACECHARS_GIF2.gif

 

Not just numbers & strings

If you’ve been following the Excel improvements over the past couple years, you probably noticed two significant improvements with the type of data you can work with in Excel:

  • Dynamic arrays - Rather than passing a single value into a function, you can pass an array of values, and functions can also return arrays of values. You can learn more about arrays here.
  • Data Types – The value stored in a cell is no longer just a string or a number. A single cell can contain a rich data type, with a large set of properties. You can learn more about data types here.

Functions can take data types and arrays as arguments, and they can also return results as data types and arrays. The same is true with the lambdas you build.

 

Let’s take an example... I have a list of cities, and I want to calculate the total distance I’d travel if I were to go to each city in order.

 

We’ll post the code for this one in a later post, but it’s pretty basic:

  • We have an array of City data types. The city data type has the latitude & longitude properties.
  • With latitude & longitude, we can do some basic math using the radius of the earth to approximate the distance between two points (that’s the first Lambda we call DistanceBetweenCities)
  • We create a recursive lambda, DistanceBetweenMultipleCities, to iterate over the cities in the array. In addition to calling itself, to iterate over the list of cities, it also calls the DistanceBetweenCities function to get a running total of the distance traveled.
DISTANCEBETWEENTWOCITIES_GIF2.gif

 

Try it yourself

If you are in our Beta program, you can try it now yourself. We’d love your feedback as we continue to improve on this new capability.

 

Note: The lambda function is available to members of the Insiders: Beta program running Windows and Mac builds of Excel. Learn more about how to become an Insider here

 

LAMBDA Overview

There are three key pieces of =LAMBDA to understand:

  1. LAMBDA function components
  2. Naming a lambda
  3. Calling a lambda function

 

LAMBDA function components

Let’s look at an example which creates a basic lambda function.

 

Suppose we have the following formula:

=LAMBDA(x, x+122)

In this, x is the argument you can pass in when calling the LAMBDA, and x+122 is the logic.

 

For example, suppose you called the lambda and input the value 1 for x, Excel would do the following calculation:

1 + 122

Which, as we all know:

1 + 122 = 123

But how do you use these? If you've been pasting our examples into Excel, you may have noticed some #CALC! errors. To resolve those, you'll need to learn the next step.

 

Naming a lambda

To give your LAMBDA a name so it can be re-used, you will want to make use of the Name Manager.

 

The Name Manager can be found in the Ribbon by going to:

Formulas > Name Manager

chgross_38-1606849602985.png

 

Once you open the Name Manager you will see the following window

chgross_39-1606849602988.png

 

From this point you’ll want to create a new entry (New…) and fill out the associated fields

chgross_40-1606849602990.png

Name: The Name of your function

Comment: A description and associated tooltip which will be shown when calling your function

Refers to: Your lambda function definition

 

Once you’re done, you can hit “OK” to store your lambda and you should see the definition returned in the resultant window.

chgross_41-1606849602993.png

And that’s it! Now you can make use of your newly crafted custom function in the workbook by calling it by its name. 

 

Calling LAMBDA

To put it simply, you call a lambda function the same way you call native functions in Excel.

 

To illustrate this, lets revisit the previous example and show how to call MYLAMBDA with a value.

=MYLAMBDA(122)

Which of course returns the value:

123

One last thing to note, is that you can call a lambda without naming it. If we hadn’t named the previous formula, and just authored it in the grid, we could call it like this:

=LAMBDA(x, x+122)(1)

This passes in 1 for x, which returns

123

 

Just the beginning... join us for the journey

As you’ve probably noticed, we are improving the product on a regular basis. The desktop version of Excel for Windows & Mac updates monthly, and the web app much more frequently than that. We have a ton of improvements already planned for LAMBDA, but we’re looking to you for feedback along the way. One that I can tell you gets me every time is the experience of editing in the name manager... 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.

 

Office Insiders

Join the Office Insider Program and choose the Beta Channel to get early access to LAMBDA in Excel. 

 

To give feedback and suggestions, click Help > Feedback, and Add #LAMBDA in your feedback so that we can easily find input about the feature. You can also post in the Excel Tech Community.

 

Connect with us

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

 

Learn more

To learn more about LAMBDA, please check out our help article and in the meantime we are excited to hear more from you about the LAMBDA formulas you have created!

 

LAMBDA Help

227 Comments

I'm loving it! This is a game changer for all heavy users addicted to formulas!

That's very challenging, another great step in Excel development!

Silver Contributor

so if I understand correctly, you basically added the ability for our 'Name' variables to have parameters passed in.  We've been using defined names as crude functions as long as the parameter(s) could be derived from the calling cell but now we can actually pass real parameters and make them recursive.    This is awesome, thank you, a definite game changer and might I say long overdue.  Can't wait to start using it.

Absolutely brilliant Brian.

Many congratulations to Chris Gross and the team for bringing this though to Excel in what is a relatively short time since this was first mentioned to the Excel MVP's at one of the MVP Summits in Seattle.

This will be a real game changer, and of course the ability to work cross-platform makes it even more powerful.

Please pass on congratulations also to all of the Microsoft Research Team in Cambridge who helped in the development of this great new feature.

Iron Contributor

voilà !!! thank you Microsoft Excel Team and Microsoft Research Team in Cambridge, Go Team !!!

Copper Contributor

This is very neat, being able to define workbook-level functions without macros etc. is a huge win!

 

The limitations were a little confusing to me when I gave LAMBDA a spin; it's not clear why you can't define a lambda function in one cell and then call it from another cell (this fails with the aforementioned #CALC!) error.

Silver Contributor

Congrats to you and the team!

BTW, there is a small mistake in the text. Where it says lambda(x+122) it should read lambda(x, x+122) 

@Reilly_Wood , the way you reference it from another cell is to put the lambda in the Name manager & give it a name. Then you can reference it in other cells by that name. 
Lots of opportunities for user experience improvements there obviously, but it's a good first step to get the capability out there for folks to start using. Please send feedback!

Microsoft

@Jan Karel Pieterse thanks for keeping us honest. It should be fixed now!

@Brian Jones (OFFICE) , hope you'll improve Name Manager box one day. Together with Evaluate Formula one.

Silver Contributor

quick thought related to @Sergei Baklan  comment above, could double clicking or right clicking the name box be a shortcut to the name manager?

Silver Contributor

Shift F3

Silver Contributor

@Sergei Baklan if not I guess Name Manager by Charles Williams and I will have to fill that gap once more 

Copper Contributor

Great functionality!

Steel Contributor

Great job! This is a much needed addition that enables many Excel solutions to be both self contained and scalable.

 

By coincidence, i posted a very similar idea in the 'Announcing LET' post recently. As suggested in that comment, it'd be awesome to have a complementary MAP function that iterates over the parameter values.

 

Addendum: a few simple tests show LAMBDA can be used outside of defined names if desired.

For example both formulas below return the spilled array {1,4,9,16,25}

 

=LAMBDA(x,x^2)({1,2,3,4,5})
=LET(square, LAMBDA(x,x^2), square({1,2,3,4,5}))

 

Thanks for this gem, it is a game changer and I know we can expect so much more with the new excel calculation engine.


It will be fun exploring how all these super efficient parameterized approach to building excel solutions will change the world of financial modeling and Optimisations. For one all the complex Macro driven loops will now run via native Excel LAMBDA functions. Another reason to get IT to move everyone in organizations to Office 365. 

great job. 

Congratulations to the Team Excel for delivering this one-of-its-kind game-changer LAMBDA Function

Steel Contributor

Congratulations...

Iron Contributor

OMF (O My Formula !!!!)  - This is simply brilliant - The Excel Formula team has delivered massive improvements for the last two years - I only hope some marketing genius does not spoil this by saying it is only available to E5 users only

 

Copper Contributor

Awesome!!! Guys, please add options to format formulas using tabs.
For example, if it would be as it is in dax formulas field edit in Power BI would be enough!

Copper Contributor

This is fantastic news!

 

@Chris_Gross @Brian Jones (OFFICE) Thank you, guys! I've a couple of questions if you don't mind

1. I didn't had a chance to test it yet but are lambdas first-class citizens, e.g can i pass a function as a function argument? :)

1. Why LAMBDA formula has been introduced instead of using ()? I'm sure you've think of it but wondering what was the obstacles prevented it.

2. Excel formula languages is increasingly moving towards a fully fledged functional programming language with LET() and LAMBDA(). But tools which are available to "real" developers (IDE, code analysis tools, etc) are simply non-existing in the Excel world.

For instance LET() function was shipped without ability to debug names with F9 key...

 

Do you have plans in foreseeable future to bring those features? Formula formatting, debugging (at least with F9), code navigation (jump to function definition, etc) and so on.

 

Thank you!

Copper Contributor

It would be wonderful if LAMBDA calls, cell contents and defined names could interact with user-defined functions through the C API. This would enable add-ins integration with add-ins developed on top of the C API in .NET (Excel-DNA), Python (PyXLL) or C/C++.

 

Currently, UDFs registered with the C API get a RegisterId (a number), which can be passed to the xlfUDF call to evaluate the function. The RegisterId of a UDF is returned when the UDF name is used without parentheses, e.g. as "=MYFUNC" in a cell. The combination of the RegisterId and xlUDF call means we are able to write functions like =FINDMIN(MYFUNC) and then evaluate MYFUNC internally in the UDF. However, this only works MYFUNC is a UDF defined with the C API.

For LAMBDA functions to work with this would be great. Then we can have the low-level functional library defined in Python or F#, and the user makes the LAMBDA functions in Excel which are passed to the functional library this way.

Then you could say:

=FINDMIN(LAMBDA(x, x^2))
=FOLD(LAMBDA(x, y, x+y), 0, A1#)

Where FINDMIN and FOLD are low-level UDFs taking LAMBDAs from the sheet as input, and using it in .NET or Pythong. This would be very neat, but needs a little hook through the C API, though it probably does not need any changes (it might even work already!?)

Then as a basic question when interacting with UDFs from the C API:

* What is the data type of a cell or name that contains a LAMBDA, when evaluated from the C API?

* Is there a way to return a lambda from a function? (e.g. could one implement currying in a user-defined function?)

 

The feature looks neat, but can be super-powered if it interacts in a friendly way with those of us building the extension tools for Excel.

Copper Contributor

1) It Is very good step ahead.

2) i must suggest to introduce c# instead of JavaScript, or VB.net (because of vba). By this way, We can fully use our Microsoft skills.

Microsoft

@johnsnow35 

These are all great questions! Here are a few quick answers.

 

1. I didn't had a chance to test it yet but are lambdas first-class citizens, e.g can i pass a function as a function argument?

 

Yes, these are indeed first-class citizens and in fact all new data types. These make use of the tech behind data types and mean that lambdas are indeed values that can be passed around. It wouldn't be fair to call this a lambda if it wasn't a function as a value!! To reiterate, just like we introduced new data types (geo, stocks, the wolfram entities, power bi types, power query types...), lambdas can be thought of as another type of value calc is aware of and can be passed around like anything else. I think this is pretty cool because it means we can unleash even more powerful use-cases for lambdas in the future. The limitation with returning them in the grid was one we had to introduce due to some challenges with formula adjust.

 

2. Why LAMBDA formula has been introduced instead of using ()? I'm sure you've think of it but wondering what was the obstacles prevented it.

 

Another great question! We introduced this as a formula because it makes our lives a bit easier when it comes to backwards compatibility. Functions are more resilient when parsing and evaluating them in different versions.

 

2. Excel formula languages is increasingly moving towards a fully fledged functional programming language with LET() and LAMBDA(). But tools which are available to "real" developers (IDE, code analysis tools, etc) are simply non-existing in the Excel world.

For instance LET() function was shipped without ability to debug names with F9 key...

Do you have plans in foreseeable future to bring those features? Formula formatting, debugging (at least with F9), code navigation (jump to function definition, etc) and so on.

 

I completely hear you on this one! I can't share more about what we are doing in the future but I will say that I definitely share your sentiment. I would love to see us add much needed tools for debugging and authoring formulas. Akin to what you get with great IDEs.

Steel Contributor

@johnsnow35 Yes, tests show one can pass functions as arguments to functions!

As an example to solve the equation f(x)=0 one could define a function of the form

Newton(guess, iterations, f(x), Df(x))

which could be implemented using the named formula,

Newton
=LAMBDA(x,n,f,Df, IF(n>0, Newton(x-f(x)/Df(x),n-1,f,Df), x))

eg with f(x) = x^2-2=0 and Df(x) = 2*x the following formula returns sqrt(2) = 1.414214....

=Newton(1,10,LAMBDA(x,x^2-2),LAMBDA(x,2*x))
Microsoft

@sameer bhide I can say for now this is not something we are looking to gate behind a specific SKU. If you have access to a 365 license that gives you excel, you're good to go! Also, love the expression "OMF"... Gonna have to use that one from now on : )

Copper Contributor

I'm really wanting to try this feature, but I'm getting a #NAME? error when I try.  I'm on the latest build of 13530.20000, but no joy.  Am I missing something?

Microsoft

@lori_m 

 

"By coincidence, i posted a very similar idea in the 'Announcing LET' post recently. As suggested in that comment, it'd be awesome to have a complementary MAP function that iterates over the parameter values."

 

I remember when you mentioned this! We actually discussed this amongst ourselves because we obviously knew what we had going on behind the scenes but couldn't unveil our plans just yet. I must say you have a way with looking into the future  : )

As for a map function, that is another great suggestion and I will leave it at that...

 

Addendum: a few simple tests show LAMBDA can be used outside of defined names if desired.

For example both formulas below return the spilled array {1,4,9,16,25}

 

=LAMBDA(x,x^2)({1,2,3,4,5})
=LET(square, LAMBDA(x,x^2), square({1,2,3,4,5}))

 

Yes! As I mentioned in a previous response, lambdas are indeed new values which excel understands, like we introduced with data types. Your proof above clearly showcases this! 

Microsoft

@DesigningKnights Is autocomplete kicking in when you type = LAMB? If not, It's likely you aren't included in the first group of people who got flighted with the feature on. You can also read more about specific builds here: https://insider.office.com/blog/lambda-excel-custom-functions  

Microsoft

 

Thanks Roger!! I am happy we were able to make good on our promise and can now publicly talk about this! I will pass on your kind words to the team, we couldn't have done this without the help of MSR Cambridge.

Copper Contributor

@Chris_Gross No, autocomplete isn't kicking in.  So apparently my update didn't get it.  But I am on Insider beta channel, 13530.20000. That's what's confusing me.

@DesigningKnights , first wave of new functionality deployment usually covers only 50% of insiders, sometimes even less. Kind of lottery. Thus only wait. You may receive it without updating on another build, it's switching on/off without that as soon as you are on build which includes the code responsible for the functionality.

Copper Contributor

I don't see anything about volatility here. Any details you can provide around recalculation?

Hope it's as volatile as native functions it uses 

Copper Contributor

Love it ... in concept.  Unfortunately the implementation of this inside of named ranges means no autocomplete of functions with help on their parameters and such and a very limited input/entry box making it virtually impossible to develop long formulas without creating bugs.

 

Is is possible to create a LAMBA formula inside a cell and then name that cell as a named range.  So cell A1 = LAMBDA(x, x+1) and then name cell A1 MyLambda.  Thus allowing the formula to be created using autocomplete and validation.  If not I dread having to write complex formulas/lambda functions inside the name manager. What a pain.

 

Brass Contributor

Cool!  :suprised: :happyface:

 

While I doubt anyone remembers, we first discussed "parameterized named formulas" at MVP summits 10-12 years ago.  Of course, when Microsoft introduced auto-filling a table column with a formula, it reduced the need for this capability. 

 

Nonetheless, LAMBDA has tremendous potential - for improved maintenance, documentation, its recursive capability, and use with functions and dynamic arrays. 

 

Really liked the ReplaceChars example as well as those from @lori_m.

Iron Contributor

@Brian Jones (OFFICE)  & @Chris_Gross 

I tried XLOOKUPMULT (1)

LAMBDA(lookupVal, lookupArray, returnArray, InstanceNumber, IfNotFound,

INDEX(FILTER(returnArray,lookupArray=lookupVal,ifNotFound),InstanceNumber)

 

Works is a normal workbook - but when stored in a adddin - we cant seem to access it from another workbook

Please make the names defined using the LAMDA to work when defined inside a file saved as an Add in - This will allow us to distribute these functions across teams / org

 

Cheers

Sam

(1) - The XLOOKUP  with the Parameter that that MS Missed

 

@Sameer_Bhide , IMHO, taking into account that lambda works (okay, will work) on Excel Online as well, it shall be some another mechanism of lambda management.

Copper Contributor

Any progress updating Excel for the 21st Century is welcome,  but this has a bit of the back to the future about it, and reminisce of the XLM sheets before Office '95.  A better alternative would to support objects within Cells so that you could add A1: =Station(“105532-872332-WA-732).Location or A1:  =Station(“105532-872332-WA-732) B1: =A1.Location.

 

It is common for complex (financial) addins to use RTD to fake having objects in cells (returning a handle).. to get around the XLOPER limitation of only really supporting numbers and text

Silver Contributor

 

@Kevin Osborn 

<I dread having to write complex formulas/lambda functions inside the name manager>

Sadly, Lambda functions have yet to appear for me (who do I bribe :smile:) but, from years of practice building solutions from named formulas, it is usually possible to build the formula on the grid (nowadays using LET if it is anything more than a one-liner) and, once it is debugged, upload it to the 'refers to' box of Name Manager (as opposed to applying a name to the cell).  It is fairly simple to use a macro for this if the process starts to get tedious.

 

The names and their formulas that remain on the scratch area of the worksheet can provide a basis for documentation or an audit trail outlining the development route.

Brass Contributor

Creating a custom function without having to write a macro!! This is awesome!

 

However, despite being on the latest beta channel, I don't have it. Not sure, why :(

Iron Contributor

The below crashes excel

CellColor

=LAMBDA(Rng, GET.CELL(63,Rng))

 

=CellColor(A1) - crashes excel

 

I know, I know XLM is dead and gone - but there is nothing in Excel that does this even today without resorting to VBA

Cheers 

Sam

Copper Contributor

Great, Excelent Solution!

Copper Contributor

The article has =MYLAMBDA(122) = 123, but it should be =MYLAMBDA(1).

Copper Contributor

This is really exciting.

Is there a LAMBDA function for VBA?

Copper Contributor

Amazing. It had to be done like 10+ years ago, but still amazing, thanks for that. If only Python/C# or any other modern language and/or modern IDE would be implemented in Excel, I would be the happiest man on earth :)

Copper Contributor

Sorry, I think this new lambda function could a bad idea :(

 

It allows to do complex things without the possibility of  :

- putting break point, debug-mode, step-by-step

- having autocomplete when your write it, automatic syntax check, syntax color.

- possibility of organizing your functions

- commenting your code

- organized units tests

 

Actually, it seems to allows the richchness of code, without the possibility of doing a clean code. Without lambda, it's already the biggest drawback of Excel : when your sheet become a nightmare to understand for someone who doesn't create it.

 

I think Formulas should be kept simple. Maybe we need a better native solution to transform "10003487-WA-73" into "WA".

Suggestion : "=INDEXSPLIT("10003487-WA-73", "-", 2). And so on, for each problem of that kind.

 

If you want to do complex thing, simply make multiples columns, split your calculus. That way, it's will be easier to detect error as it show up into the corresponding column. This is crucial for users ; developpers enjoy debugging, users really don't.

 

I already see a lot of developper being entousiastics : "Oh yes, recursion, lambda, such a power !".

I hope they will enjoy the power of debugging the 30 ugly lambdas functions, written by Georges the accountant, without any serious developping tools.

 

Nevertheless, it's nice to see some moves on Excel :)

 

Best Regards.

Silver Contributor

@JackIsJack 

I think you are right to draw attention to the risks inherent in complex calculations hidden behind a name, but I wouldn't agree that makes it a bad idea.  At the extreme, one has the chef's masterpiece, excessive presentation and style and almost impossible to deconstruct.  At the other, there is the typical spreadsheet creation, more like something the cat has regurgitated; all the pieces are visible but I certainly wouldn't care to sort through them.

 

Putting culinary metaphors aside for the moment, I agree that Name Manager offers a poor user experience (though not quite as bad as conditional formatting and validation).  On the other hand, there is nothing to say that you shouldn't use the grid for development and testing.  Now as the nightmare of relative referencing is largely behind us, you can use any scratch space within the workbook for testing array formulas without affecting the result.

 

One of the plusses of Names is that they can capture the intent of the operation in business terms, so can provide a level of documentation in their own right.  There is also the comment box that provides a possibility for further explanation.  On the subject of helper ranges, they can be useful for checking detail but they add sheet clutter and make it difficult to determine the overall direction of travel.  Very often it is simpler to check the output than it is to check steps of the calculation.

Copper Contributor

@Peter Bartholomew 

 

"there is nothing to say that you shouldn't use the grid for development and testing. "

 

Yes, it would be a good practice to dedicate sheets to test your lambda functions.

But I thinks it is a pity : worksheets should stay "Business Oriented", do you agree ? A sheet is a free space where a lot things can happen, and programming construction requires some stability. Users don't want to be trouble with technical stuffs during their business tasks.

 

If you want to do programming, use the adequates tools : VBA or JS or .NET. It's away from common users : this distance provides security for both part.

Giving the "modularity" and "recursivity" to Excel Formula will not make the cat's vomit better, it will make it more acid than ever.

Maybe I am pessimistist, the future will tell.

 

"One of the plusses of Names is that they can capture the intent of the operation in business terms"

 

Again I am sorry but in practice, Georges will use the lambda function "GetLocation()" to extract "Amanda" from "123-Amanda-01" because...it works technically, regardless of whether "Amanda" is not a Location. Georges is not a programmer, he does what it works fast ; he may not know how to build those lambda functions so he will use existing ones. Do you imagine what kind of problem we are facing here ? Sharing a bad name for technical purpose.... The choice between building a new lambda or modify one and trying to guess the impact.... These are common programming problems, but just in face of a end user, and without the adequate tools to solve it. Brrr.

 

I hope you are right and I am too pessimistic :) Have a nice day.

 

 

 

Gold Contributor

Many thanks

It's really great, now I can use it to build my own world in Excel steps and put it together like a Lego game.
Thus, in a certain sense, the Excel formulas are conveyed into a programming language.


"LAMBDA" It is what it is ... a dream!

"LAMBDA"... Excel's tomorrow :)


I (we all) have to deal with it intensively 

 

Nikolino

I know I don't know anything (Socrates)

Version history
Last update:
‎Dec 03 2020 12:41 PM
Updated by: