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