4 ms·

Microsoft introduces LAMBDA functions for Excel

30 points by vanburen 6y ago

rahimnathwani 6y ago
@dang the original source seems clearer: Announcing LAMBDA: Turn Excel formulas into custom functions https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambda-turn-excel-formulas-into-custom-functions/ba-p/1925546 https://techcommunity.microsoft.com/t5/excel-blog/announcing...
- rajandatta 6y ago
  Original article is much clearer. This is a long-needed and awaited step. I'd say this might be the most important change in Excel in years. The recursion hacks mean that all kinds of looping hacks can be removed.
rahimnathwani 6y ago
"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."
barryvan 6y ago
I think the biggest improvement this will have is in keeping things tidy and facilitating reuse. Yes, recursion is nice, but I suspect that it's not going to see much use compared to just replacing repeated formulas with simple functions. Of course this also means that Excel becomes even more of a slippery slope towards programming generally, which can only be a good thing.
- deleted 6y ago
  [deleted]
layer8 6y ago
It’s not clear from the documentation [0] whether this also adds lambda values (i.e. enabling higher-order functions). [0] https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67 https://support.microsoft.com/en-us/office/lambda-function-b...
- rahimnathwani 6y ago
  A cell can contain any value. Values in Excel have always been: - numbers - text, - cell references - formulae that resolve to one of the above Allowing a value to be a function would (presumably) allow a cell to contain a function (just the function, not an invocation of a function). I can't imagine that being a simple change, or one introduced without being explicitly in the docs.
  - jsmith45 6y ago
    Your list is missing "Rich Value Types" (like stocks or geographical locations). Those can be stored in a cell, and then have other cells access their fields with =FIELDVALUE(...). Indeed the complete list of cell data in the api is: boolean, double, empty, error, integer, richValue, string, unknown Further not every valid excel value (in the sense that you can pass it as a function argument) can be a cell value. For example arrays. Arrays need to spill into multiple cells if they are a result. Similarly, to my knowledge cell references cannot be the result value of a cell, as they would get dereferenced to get their underlying value as part of evaluation. On the other hand they most certainly are something that can be passed to a function without getting dereferenced first. The name manager allows naming certain values that cannot be a cell result (like arrays), which makes it seem more likely that lambdas are full fledged data types, that could be passed to other lambdas. If I had access to a beta copy of excel, it would be easy to find out by running: =LET(doubleapply, LAMBDA(x, y, x(x(y))), plusthree, LAMBDA(x, x+3), doubleapply(plusthree,5)) If that works, great lambdas are clearly a new first class data type. If it does not work, then obviously they are some form of second class citizen.

30 points by vanburen 6y ago

@dang the original source seems clearer: Announcing LAMBDA: Turn Excel formulas into custom functions https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambda-turn-excel-formulas-into-custom-functions/ba-p/1925546 https://techcommunity.microsoft.com/t5/excel-blog/announcing...

rajandatta 6y ago
Original article is much clearer. This is a long-needed and awaited step. I'd say this might be the most important change in Excel in years. The recursion hacks mean that all kinds of looping hacks can be removed.

rahimnathwani 6y ago

"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."

barryvan 6y ago

I think the biggest improvement this will have is in keeping things tidy and facilitating reuse. Yes, recursion is nice, but I suspect that it's not going to see much use compared to just replacing repeated formulas with simple functions. Of course this also means that Excel becomes even more of a slippery slope towards programming generally, which can only be a good thing.

deleted 6y ago
[deleted]

layer8 6y ago

It’s not clear from the documentation [0] whether this also adds lambda values (i.e. enabling higher-order functions). [0] https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67 https://support.microsoft.com/en-us/office/lambda-function-b...

rahimnathwani 6y ago
A cell can contain any value. Values in Excel have always been: - numbers - text, - cell references - formulae that resolve to one of the above Allowing a value to be a function would (presumably) allow a cell to contain a function (just the function, not an invocation of a function). I can't imagine that being a simple change, or one introduced without being explicitly in the docs.
- jsmith45 6y ago
  Your list is missing "Rich Value Types" (like stocks or geographical locations). Those can be stored in a cell, and then have other cells access their fields with =FIELDVALUE(...). Indeed the complete list of cell data in the api is: boolean, double, empty, error, integer, richValue, string, unknown Further not every valid excel value (in the sense that you can pass it as a function argument) can be a cell value. For example arrays. Arrays need to spill into multiple cells if they are a result. Similarly, to my knowledge cell references cannot be the result value of a cell, as they would get dereferenced to get their underlying value as part of evaluation. On the other hand they most certainly are something that can be passed to a function without getting dereferenced first. The name manager allows naming certain values that cannot be a cell result (like arrays), which makes it seem more likely that lambdas are full fledged data types, that could be passed to other lambdas. If I had access to a beta copy of excel, it would be easy to find out by running: =LET(doubleapply, LAMBDA(x, y, x(x(y))), plusthree, LAMBDA(x, x+3), doubleapply(plusthree,5)) If that works, great lambdas are clearly a new first class data type. If it does not work, then obviously they are some form of second class citizen.