5 ms·

=LAMBDA(...) does return an anonymous function - and then you use the Names Manager to name it. It is a bit weird and confusing because you currently can’t rea

=LAMBDA(...) does return an anonymous function - and then you use the Names Manager to name it. It is a bit weird and confusing because you currently can’t really use the anonymous functions without naming them, but maybe they’re going to relax that restriction eventually?

MegaDeKay 6y ago
The article also says this is valid within the grid: 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) Edit: typo
- ripley12 6y ago
  Yeah, that’s why I added a qualifier. I can’t see that being much use outside of testing/debugging functions, since it would usually be simpler to just write that without the LAMBDA call.
  - int_19h 6y ago
    They can also be referenced via the cells. You could argue that cells are themselves named, but those names are really indices into a 2D array, you can have relative references or do offset-based math if needed, and there's indirection (i.e. you can use an arbitrary value as an index to read/write another value). Given all this, an index of a cell containing a lambda is really a lot like a function reference, and can be used in much the same way - passed around etc. So, these are anonymous functions. The more interesting question is whether they're closures - that is, whether a LAMBDA nested in another LAMBDA can reference the latter's parameters, and how it interacts with LET (https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 https://support.microsoft.com/en-us/office/let-function-3484...).
    - ripley12 6y ago
      Huh, are you sure? I don’t think you can (currently) define a lambda in a cell without also calling it in that cell (which means the cell now evaluates to the function’s return value, not a function). The LAMBDA docs: “If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.” https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67?ui=en-US&rs=en-CA&ad=CA https://support.microsoft.com/en-us/office/lambda-function-b...
      - int_19h 6y ago
        It will show #CALC! for that cell because, well, it has to show something for the result of the formula - but the formula didn't evaluate to a "displayable" value.
        ripley12 6y ago
        Yeah that wasn't the best example on my part. But I have tried it and confirmed that calling lambdas defined in another grid cell doesn't work. Specifically: 1) Put =LAMBDA(x, x+1) in A1 2) Try calling =A1(1) from any other cell - it will return #REF! I'm on the Office Insiders beta track and LAMBDA does work otherwise.
daxfohl 6y ago
Yeah, I was expecting you to be able to define a lambda in a cell, so like `B1` could be `=LAMBDA(x => $A1 + x + 1)`. Then you could assign another cell say `D1` to like `$B1($C1)`. Drag-filling B1 down would make say `B2` equal to `=LAMBDA(x => $A2 + x + 2)` or something, so that it would work like people would think Excel Lambdas should. That would be more along the lines of lambdas in the traditional sense of anonymous functions, closure captures, higher order functions(?), etc. Could be cool, but IDK if it would be useful? Can't think of any specific use cases right now, but perhaps once people get used to it, there would be tons of them.
- int_19h 6y ago
  It allows for exactly that - if B1 defines a lambda, then you can do B1(x) elsewhere.
deleted 6y ago
[deleted]
contravariant 6y ago
Can you do LAMBDA(...)(x)? That would make it an anonymous function. Of course the real fun starts when you can do: =LAMBDA(f, LAMBDA(x, f(x(x)))(LAMBDA(x, f(x(x)))))
- ripley12 6y ago
  You can, but you can't (currently?) do =A1(x) where A1 is a LAMBDA function. I think LAMBDA(...)(x) is only useful for testing/development, but maybe I'm missing some good use cases.
  - contravariant 6y ago
    If need be you could always just define a lisp interpreter.

MegaDeKay 6y ago

The article also says this is valid within the grid: 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) Edit: typo

ripley12 6y ago
Yeah, that’s why I added a qualifier. I can’t see that being much use outside of testing/debugging functions, since it would usually be simpler to just write that without the LAMBDA call.
- int_19h 6y ago
  They can also be referenced via the cells. You could argue that cells are themselves named, but those names are really indices into a 2D array, you can have relative references or do offset-based math if needed, and there's indirection (i.e. you can use an arbitrary value as an index to read/write another value). Given all this, an index of a cell containing a lambda is really a lot like a function reference, and can be used in much the same way - passed around etc. So, these are anonymous functions. The more interesting question is whether they're closures - that is, whether a LAMBDA nested in another LAMBDA can reference the latter's parameters, and how it interacts with LET (https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 https://support.microsoft.com/en-us/office/let-function-3484...).
  - ripley12 6y ago
    Huh, are you sure? I don’t think you can (currently) define a lambda in a cell without also calling it in that cell (which means the cell now evaluates to the function’s return value, not a function). The LAMBDA docs: “If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.” https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67?ui=en-US&rs=en-CA&ad=CA https://support.microsoft.com/en-us/office/lambda-function-b...
    - int_19h 6y ago
      It will show #CALC! for that cell because, well, it has to show something for the result of the formula - but the formula didn't evaluate to a "displayable" value.
      - ripley12 6y ago
        Yeah that wasn't the best example on my part. But I have tried it and confirmed that calling lambdas defined in another grid cell doesn't work. Specifically: 1) Put =LAMBDA(x, x+1) in A1 2) Try calling =A1(1) from any other cell - it will return #REF! I'm on the Office Insiders beta track and LAMBDA does work otherwise.

daxfohl 6y ago

Yeah, I was expecting you to be able to define a lambda in a cell, so like `B1` could be `=LAMBDA(x => $A1 + x + 1)`. Then you could assign another cell say `D1` to like `$B1($C1)`. Drag-filling B1 down would make say `B2` equal to `=LAMBDA(x => $A2 + x + 2)` or something, so that it would work like people would think Excel Lambdas should. That would be more along the lines of lambdas in the traditional sense of anonymous functions, closure captures, higher order functions(?), etc. Could be cool, but IDK if it would be useful? Can't think of any specific use cases right now, but perhaps once people get used to it, there would be tons of them.

int_19h 6y ago
It allows for exactly that - if B1 defines a lambda, then you can do B1(x) elsewhere.

deleted 6y ago

[deleted]

contravariant 6y ago

Can you do LAMBDA(...)(x)? That would make it an anonymous function. Of course the real fun starts when you can do: =LAMBDA(f, LAMBDA(x, f(x(x)))(LAMBDA(x, f(x(x)))))

ripley12 6y ago
You can, but you can't (currently?) do =A1(x) where A1 is a LAMBDA function. I think LAMBDA(...)(x) is only useful for testing/development, but maybe I'm missing some good use cases.
- contravariant 6y ago
  If need be you could always just define a lisp interpreter.