Microsoft has announced a new function - LAMBDA(), permitting users to define their own hyper-functions by selecting and naming their own regular sequence of existing function calls/formulae. It would be nice for those with no Java skills. Here's a link to the release notes; https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambda-turn-excel-formulas-into-custom-functions/ba-p/1925546
Opening an Excel document with LAMBDAS on my side, causes LibreOffice to spit out Err: 508 errors everywhere.
It would be a huge step forward if the LAMBDA function were to be implemented in Calc. I don’t need to tell you how fundamental this is from a theory of computing perspective. Or to point out how this can improve formulas so drastically in terms of readability, as complex sub-formulas for intermediate values that would otherwise have to be repeated in a formula can be assigned a name and then used repeatedly in a lambda expression. Presumably this would also avoid having to calculate these complex expressions multiple times. It also makes calculations feasible that would otherwise be solved via macros, which of course are to be avoided where possible for several reasons. For these reasons, I am sure the number of Excel documents using LAMBDA will be increasing significantly. But even for development just in LibreOffice Calc it would be a fantastic improvement to have LAMBDA available (that is actually my reason for being interested in this).
Google sheets also has LAMBDA and array-processing functions that use LAMBDA, such as MAP, REDUCE, SCAN, MAKEARRAY, BYCOL, and BYROW. I believe these are the same as in Excel. There are also related functions for arrays, such as VSTACK and HSTACK. https://support.google.com/docs/answer/12508718 If you export a spreadsheet that uses these functions, from Google Sheets to an .ODS file, you get named formula expression, but it doesn't work in LO Calc. You get Err:509 Missing operator. For example, in Google Sheets I have this named function: name: FIND_PEAKS parameters: x, y Definition: =REDUCE({0,0}, y, LAMBDA(accum, curr, IF(AND(offset(curr, -1, 0)<curr, curr>offset(curr, 1, 0)), VSTACK(accum, {INDEX(x, ROW(curr)-ROW(x)+1, 1),curr}) , accum ) )) In the .ODS file I get this named expression: lambda(x, y, reduce({0,0}, y, lambda(accum, curr, IF(AND(OFFSET(curr, -1, 0)<curr, curr>OFFSET(curr, 1, 0)), vstack(accum, {INDEX(x, ROW(curr)-ROW(x)+1, 1),curr}))))) Which results in the 509 error. It seems like the major spreadsheets are supporting this now. I think it's time for LO Calc to do so too.
Is anyone working on this feature? I am working on a process that makes use of a load bearing spreadsheet. At this point, we will have to migrate to MSO or Google Sheets if LAMBDA and LET are not supported. If no one is working on this feature, but the maintainers are open to it, I'd like to work on supporting LAMBDA and LET. I will also leave a comment on the corresponding bug for let (https://bugs.documentfoundation.org/show_bug.cgi?id=137543).
(In reply to me from comment #4) > Is anyone working on this feature? I am working on a process that makes use > of a load bearing spreadsheet. At this point, we will have to migrate to MSO > or Google Sheets if LAMBDA and LET are not supported. > > If no one is working on this feature, but the maintainers are open to it, > I'd like to work on supporting LAMBDA and LET. I will also leave a comment > on the corresponding bug for let > (https://bugs.documentfoundation.org/show_bug.cgi?id=137543). No one is working on this feature as the Assignee field states "Not Assigned". Please add your name (by clicking on take) next to the Assignee field and work on adding the LAMBDA function. Thank you
Should ISOMITTED (IsOmitted) https://bettersolutions.com/excel/functions/isomitted-function.htm be added with LAMBDA, BYCOL (ByCol), BYROW (MyRow), MAKEARRAY (MakeArray), MAP, REDUCE, SCAN https://bettersolutions.com/excel/functions/lambda-functions.htm Thank you
"eta lambda" (short for "eta reduced lambda") functions [1] https://exceljet.net/glossary/eta-lambda [2] https://techcommunity.microsoft.com/t5/excel/on-the-bleeding-edge-with-eta-reduced-lambda-functions/m-p/4023901 [3] https://www.icaew.com/technical/technology/excel-community/excel-community-articles/2024/excel-new-functions-might-change-data-analysis-forever [4] Excel LAMBDA, LAMBDA Helper Functions & Eta-LAMBDAs: the Complete Story – Updated 2024: 365 MECS 10 https://www.youtube.com/watch?v=OxV-F0vXj8I
There is a good article [1] by N. Pavlov (there should be no problem with translation). [1] https://www.planetaexcel.ru/techniques/25/20915/ https://ask.libreoffice.org/t/is-there-a-way-to-create-what-might-be-called-a-paramterized-named-range/99238/11
How to write recursive LAMBDA function in Excel with examples ============================================================= https://www.ablebits.com/office-addins-blog/write-recursive-lambda-function-excel/ Example 1. Remove unwanted characters and trim extra spaces ----------------------------------------------------------- =LAMBDA(data, chars, TRIM(IF(chars<>"", RemoveTrim(SUBSTITUTE(data, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), data))) =RemoveTrim(A2:A10, D2) Example 2. Replace multiple characters with the same character -------------------------------------------------------------- =LAMBDA(data, chars, new_char, IF(chars<>"", ReplaceChars(SUBSTITUTE(data, LEFT(chars), new_char), RIGHT(chars, LEN(chars)-1), new_char), data)) =ReplaceChars(A2:A6, E1, E2) Example 3. Replace multiple values with other values at once ------------------------------------------------------------ =LAMBDA(data, old, new, IF(old<>"", ReplaceAll(SUBSTITUTE(data, old, new), OFFSET(old, 1, 0), OFFSET(new, 1, 0) ), data)) =ReplaceAll(A2:A10, D2, E2)
Created attachment 196390 [details] Fibonacci sequence with Recursive Lambda hyperfunction Generate the Fibonacci sequence in Excel with the SEQUENCE function =================================================================== Attached spreadsheet has the following tabs: 1. Fibo Iter Offset Manual 2. Fibo Iter Offset CSE 3. Fibo Iter Reduce 4. Fibo Matrix 5. Fibo Recursive 6. Golden Ratio 7. Fibo Closed Form 8. Overflow https://medium.com/@the1howie/generate-the-fibonacci-sequence-in-excel-with-the-sequence-function-9480af2752fb https://github.com/the1howie/Excel-Formulas/blob/main/Fun_with_SEQUENCE_Generate_Fibonacci.xlsm
What's new in Office 2024 and Office LTSC 2024 ============================================== https://support.microsoft.com/office/what-s-new-in-excel-2024-for-windows-and-mac-faee26b6-ad74-40a8-9304-aa6db716553f New LAMBDA function ------------------- The LAMBDA function has been added to Excel 2024 and Excel 2024 for Mac, which allows you to create a function for a commonly used formula, eliminating the need to copy and paste this formula, and effectively adding your own custom functions to the Excel function library. Lambda() - LAMBDA function https://support.microsoft.com/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67