Is there a way to create what might be called a paramterized named range

English
#1

This topic was addressed to some extent in Named Functions - Reduce Formula complexity / Low hanging Fruit to enable functional Programming in Calc, but not directly.

LibreCalc uses the term Macro in a slightly different way than many programming languages, eg C. LibreCalc “Macros” allow one to write VBA functions, using function in the same way as writing a function in C. C also has what it causes a “macro” processor which does not define functions but defines names for textual substitutition. In that sense, it is similar to a named range in LibreCalc. The #define statement in C can be used to define the “macro” called ‘YEAR’ that represents the value ‘2023’ just as in LibreCalc one can define a Macro called ‘YEAR’ that represents the value ‘2023’.

C also allows parameterized “macros”. So for example, I could define the following C macro.

#define LOAD(name) IF(VLOOKUP(name, $‘data-table’.$A$1:$‘data-table’.$B$50, 2, 0) %3 = 1, VLOOKUP(name, $‘data-table’.$A$1:$‘data-table’.$B$50, 2, 0), 0)

Is there a way to do something like that in LibreCalc without going into writing VBA code? The goal is to be able to tke complex equations and express them more simply in the spreadsheet cells. So instead of have a series of cells of this form.

IF(VLOOKUP(“Green”, $‘data-table’.$A$1:$‘data-table’.$B$50, 2, 0) % 3 = 1, VLOOKUP(“Green”, $‘data-table’.$A$1:$‘data-table’.$B$50, 2, 0), 1)

The cell could contain this.

LOAD (“Green”)

Not only does this allow for creating more readable code, it allows one to modify the functionality in one place instead of trying to find hundreds of cells that might be using the LOAD functionality (for example, changing the “remainder” value from 1 to 2). And unlike a VBA, it is just textual substitutions so there is no problem with the values not being available when the spreadhseet is first loaded.

I fnd myself wanting to do this in spreadsheets frequently. I do not see how it can be done with named ranges (in some situations I can create another cell and have the range make a relative reference to that cell but that is not suitable in all situations).

Any ideas on if this is possible in some way? If not, is this a worth candidate for a new feature (I would think not a very burdensome one to implement)?

#2

https://help.libreoffice.org/7.6/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3158430

#3

I think we are talking about analogue of LAMBDA Excel function (since 2021?). See also tdf#138749.

#4

Thanks sokol92, that is exactly what I need. Given the request to add it was made in 2020, it does not look like that feature will be forthcoming. :frowning_face:

MULTIPLE.OPERATIONS does not solve the problem because it requires adding more cell(s) to hold the “parameters”. So for example, is $A$2 contins a formula and $A$1 has the parameter it works on, MULTIPLE.OPERATIONS($A$2, $A$1, B3) would work if I added a cell B3 to hold the parameter value. But using a literal like “Green” instead of B3 in MULTIPLE.OPERATIONS($A$2, $A$1, “Green”) gives an error 504 because “MULTIPLE.OPERATIONS cannot take literal values as arguments, e.g. a constant number”.

(That said, I was not aware of the MULTIPLE.OPERATIONS function before and can see how it might be useful in some situations. So that was also a useful response. :slightly_smiling_face:)

#5

Well, a “parameterized named range” is =OFFSET(named_range;A1;A2;A3;A4).

#6

I am dealing with concept, not a name. As I indicated in my OP, names have different meanings depending on the enviroment. What C calls a Macro is a different thing than what LibreCalc calls a Macro. I have never seen the term “parameterized named range” and just now googled it and got no hits. My title said might be called a parameterized named range, to indicate I did not care if the name existed or, like “macro”, my use of it my refer to a different concept. In C the, the concept would be called a parameter Macro and since the closest thing to a C macro in LibreCalc is a named range in LibreCalc, in my OP, I called the concept a parameterized named range.

The generic situation I am dealing with there are complex (and not too readable) formulas being used in many scattered places in a spreadsheet. A such formula may be used numerous time (and again, in scattered places). I want a way to make a clear cut statement that a particular function is being applied to the parameter in question (without making a VBA function, which has a number of its own disadvantages). The Lambda function in Excel (which I just learned about today thanks to SOKOL92) appers do do that. OFFSET does something completely different.

If I could only figure a kludgey way of implementing LAMBDA in LibreCalc. :thinking:

#8

I’m not an expert in any field… but I’m superficially informed in some.
Since you (@azalea4va) now informed to some detail about the concepts you are interested in, I will try to explain how I see the matter.
The LAMDBA function (lambda calculus) as implemented in some programming environments (list processing functional languages?) is also known as a means for the “definition of anonymous functions”.
Afaik neither Calc (LibO) nor Excel nor their subsystems used to get API functionality know variables accepting anonymous functions as their values. Therefore I would assume they aren’t well prepared for something like the known LAMBDA(). An explanation concerning Excels’s implementation of LAMBDA() explicitly states using a trivial example as a last necessary(?) step to assign a name to the resulting construct. Yes this looks strange. It’s “the other way round”.
Again emphasizing that I’m not an expert, I would suppose that the LAMBDA() partly is kind of a fake label for what Excel supplies.
Anyway: What might be usable in Calc in a similar way should be a Named Expression which is applicable much more flexible than just a ‘parameterized named range’. If you use named expressions calling UserDefined code, you may get along with your project partly. But I’m afraid you will need to program a lot of additional framework (for the handling of passed parameter names e.g.) and you may get stuck at the point where you would either need an EVALUATE() function or a “private” formula parser/translator/evaluator.

#9

In my opinion, LAMBDA in Excel is a powerful tool, let’s hope to implement something similar in Calc.
LAMBDA allows you to create complex functions without writing LO Basic programs (Python, …). In addition, LAMBDA supports recursive calls, which will undoubtedly give a new impulse to Calc formula writers (after LAMBDA is implemented in Calc).

#10

You seem to know much better than I do.
I have no longer access to any Excel, and can’t try out the innovation.
Even if not working in Calc, some more realistic and relevant examples concernig the power of the new tool might help to understand better.

#11

At this point in time, this function is implemented only in Excel for Microsoft 365, it is not even in MS Office 2021.
There is a good article by N. Pavlov (there should be no problem with translation).

#12

So… in practice it extends →Sheets→Named Ranges and Expressions →Define…
in such a way that placeholders for arguments are possible?!
or in other words: it provides the opportunity to define Functions without using a dedicated IDE.

#13

Yes (in my opinion). And Named expressions are a natural place for LAMBDA functions.
In Excel, if you define a LAMBDA function in a named expression, then this function will be shown in the proposed list of functions when entering formulas (this can be seen in the figure in the above article by N. Pavlov).
Another advantage over macros is that no special security-related permissions are needed.

#14

Named expressions support relative addressing.
When you define “MySUM” as A1+B1 while C1 is the active cell, then =MySUM sums up the 2 cell values left of the active cell, wherever that is.
Or SUM($A1:B1) to sum up everything left of the active cell.

#7

May be helpful:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.