Named Functions - Reduce Formula complexity / Low hanging Fruit to enable functional Programming in Calc

English
,
#1

The old way
Define a custom function. That means learn python or vba and their api.

The google sheets way
Define a named function. A little Dialog pops up. You get the same Formular Editor as you would use for cells. Additionally you can add parameters which you can use inside the formula. You save and from there on you can use this named function inside the sheet. What I also like is that you have a multiline Editor where you can indent the formula.

Example
It turns this
“=INDEX(Sheet!$BX$5:$BX$9, MATCH(A1,Sheet!$BU$5:$BU$9, 0), 1)”

into this
“=MY_LOOKUP(A1)”

The function would be defined like this
Named Function: MY_LOOKUP, value
Named Function Body: “=INDEX(Sheet!$BX$5:$BX$9, MATCH(value,Sheet!$BU$5:$BU$9, 0), 1)”

Benefits

  • It is way easier to use than fiddling with vba
    • lowers the barrier of entry for custom functions significantly
    • easier access to sheet ranges (no api, just write formulas as you are used to)
  • encourage cleaner formulas, makes everything more readable and structured
    • makes functional programming feasible

This little tweak makes calc so much more powerful.

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

Why do you need so many Z’s in your nickname? Don’t you know that this letter has become a symbol of rashism (Russian fascism), “Russian swastika”, a symbol of the war of conquest? Even one Z causes irritation in civilized people, and you inserted as many as five in your nickname. Did you do it on purpose?

2 Likes
#3

So called “named ranges” are named expressions actually.
t82912.ods (16.1 KB)

3 Likes
#4

google invented another name.
Great!

#5

… and incompatible with MS Excel. What happens when you export a Google sheet with that feature to xlsx?

#6

Thanks for the tip. “Named Ranges” did confuse me. I was not aware that the formulas would be evaluated relative to the cell where you instantiated it.

But how can you define parameters like ‘value’ from my example so you can actually use it as a real function? The Dialog for adding ranges does not have anything obvious in that regard. It looks the named range does something similar to copy/paste. Sorry if I am just asking dumb questions.

#7

The Formulas could be expanded into their actual formulas as a workaround.
I am not an Expert at Calc. But the way I see it I have to do that anyway at the moment. So nothing lost in terms of complexity for the Excel Folks.
Also why wait till MS implements it. That may never happen.

#8

It is already implemented in Calc and in Excel likewise if you interprete named ranges as named expressions. A spreadsheet application trying to implement everything from all other spreadsheet applications will fall apart inevitably.

1 Like
#9

To get a “real function” capable of taking passed parameter values you will need any precisely specified “real syntax” to create it, and lots of “real semantical definitions”. Such a thing is named a “Programming Language” and you can fill complete libraries with what’s published about the topic since Lady Lovelace started it. The complexity of such a language surely isn’t sufficiently exemplified by a ", value" behind a name.

If you resort to a very simple language like Basic, you can crearte UDFs (UserDefinedFunction), and named erxpressions will accept calls to them: They follow a Calc-compatible syntax.

#10

True. But being able to define functions in some scope reduces complexity of formulas massively.

There is already IF and a lot of mathematical functions that are actual pure functions. All intermediate data is immutable. That is pretty darn close to a functional language already.

MS Excel also has LET and LAMBDA which shows they understood this as well. With these you are very close to how LISP or even more how Clojure works. Adding those named functions seems to me the next logical step.

I mean I might overlook a feature that gives the same functionality. But so far I found nothing that I could coax into doing what I want. As I state earlier, adding those Functions via VBA/Python is very cumbersome and most of the time not worth it. So People tend to create monstrosities like that here:

=B$6*(MAX(0,MIN(1-1+1,B$2-$A10-1))+MAX(0,MIN(19-2+1,B$2-9-$A10-2)))/20+B$5*(MAX(0,MIN(1-1+1,MAX(0,MIN(1-1+1,(1+1)-B$2+$A10))-MAX(0,MIN(1-1+1,(1+1)-(B$2+10)+$A10))))+MAX(0,MIN(19-2+1,MAX(0,MIN(19-2+1,(19+1)-(B$2-9)+$A10))-MAX(0,MIN(19-2+1,(19+1)-B$2+$A10))))+MAX(0,MIN(20-20+1,B$2-9-$A10-20)))/20+B$4*(MAX(0,MIN(1-1+1,(1+1)-(B$2+10)+$A10))+MAX(0,MIN(19-2+1,MAX(0,MIN(19-2+1,(19+1)-B$2+$A10))-MAX(0,MIN(19-2+1,(19+1)-(B$2+10)+$A10))))+MAX(0,MIN(20-20+1,MAX(0,MIN(20-20+1,(20+1)-(B$2-9)+$A10))-MAX(0,MIN(20-20+1,(20+1)-B$2+$A10)))))/20+B$3*(MAX(0,MIN(19-2+1,-B$2+10+$A10))+MAX(0,MIN(20-20+1,(20+1)-B$2+$A10)))/20

Taken from a real sheet.

Sure one could spread this out over multiple cells and then hide the intermediate cells. But named functions would be much more elegant and also express what the author actually wanted to do here.

#11

WT*&%^?? :confounded: :hot_face: :cold_face: :persevere:

#12

An old friend from a different forum once made me read a very short poem by a Frenchman writing in English:
I am a sundial. Ordinary words
Cannot express my thoughts on birds.

Can ordinary words express your thoughts on

=B$6*(MAX(0,MIN(1-1+1,B$2-$A10-1))+MAX(0,MIN(19-2+1,B$2-9-$A10-2)))/20+B$5*(MAX(0,MIN(1-1+1,MAX(0,MIN(1-1+1,(1+1)-B$2+$A10))-MAX(0,MIN(1-1+1,(1+1)-(B$2+10)+$A10))))+MAX(0,MIN(19-2+1,MAX(0,MIN(19-2+1,(19+1)-(B$2-9)+$A10))-MAX(0,MIN(19-2+1,(19+1)-B$2+$A10))))+MAX(0,MIN(20-20+1,B$2-9-$A10-20)))/20+B$4*(MAX(0,MIN(1-1+1,(1+1)-(B$2+10)+$A10))+MAX(0,MIN(19-2+1,MAX(0,MIN(19-2+1,(19+1)-B$2+$A10))-MAX(0,MIN(19-2+1,(19+1)-(B$2+10)+$A10))))+MAX(0,MIN(20-20+1,MAX(0,MIN(20-20+1,(20+1)-(B$2-9)+$A10))-MAX(0,MIN(20-20+1,(20+1)-B$2+$A10)))))/20+B$3*(MAX(0,MIN(19-2+1,-B$2+10+$A10))+MAX(0,MIN(20-20+1,(20+1)-B$2+$A10)))/20

?
My thoughts on that formula so far: A
rea l spreadsheet needn’t always be a
rea sonable thing.
But surely there are worse examples. Human inventiveness seems unlimited insofar.
Concerning Calc generally: It’s neither perfect nor finished, but further “enhancements” always need to be checked for sufficient consistency, usability, relevance, …
Simply trying to implement everything … : See @Villeroy above.
A different thing can -in rare cases- be add-ins / Extensions.
Such an extension might be (functionally) a bridge to any different tool implementing some aspects of programming without any co-usage of the LibO API. You may think of some dialog of Lisp, of a CAS like Maxima (surely itself using some Lisp, needing itself add-ons) , of CAD … No end!
Since I studied ALGOL to some depth in the mid-1960es, I had in mind the idea of “compile-and-run-nested-code on the fly”… I went completely different ways of profession, and nobody implemented that “great idea” the way I had in mind. Interpreters? Interpilers, Compreters?
This topic really is extensible. I will no longer extend it.

#13

Dear JohnSUN.

I am very sorry for what you and your people go through right now. Nobody should ever have to endure what you have to face.

The nickname is not meant politically in any way. It is just the name I used for decades now. I am sorry that it creates such bad associations for you.

I hope this war over soon. May you and your loved ones be safe.

4 Likes
#14

This is about absolute, relative and mixed references. A “named range” is a named range actually when it refers to something like $Sheet1.$A$1:$A$99. All 5 tokens with $ prefix gives an unambiguous reference to a distinct range on a distinct sheet

My named formula
INDEX($Sheet2.$BX$5:$BX$9; MATCH($A1;Sheet2.$BU$5:$BU$9; 0); 1)
should have one more $ in front of Sheet2.$BU$5:$BU$9; Calc knows relative and absolute sheet references.
INDEX($Sheet2.$BX$5:$BX$9; MATCH($A1;$Sheet2.$BU$5:$BU$9; 0); 1)
has all tokens absolutized except $A1 which refers to this row in column $A on this sheet. When you copy =MyLookup anywhere across the sheet, it will always look up this row’s value in column A in BU5:BU9 of Sheet2 and return the corresponding value from BX5:BX9.
If you would change $A1 to A1, the reference would refer to the left neighbour cell and MyLookup would always look up the value of the left neighbour cell.

Relative sheet references are specific to Calc. Sheet2.$A$1 on Sheet1 refers to the next sheets cell A1.
When you copy the formula to the subsequent sheet tabs, you get Sheet3.$A$1, Sheet4.$A$1, etc.

#15

Let’s go one step further, assuming there are 4 sheets in the workbook and we’ve copied the formula onto Sheet4.
In this case, as a result of the formula, we will get the value of cell A1 of sheet … Sheet1. At the same time, with the current Sheet4 sheet, the “Manage Names” dialog shows the value #REF!.$A$1 in the “Range or formula expression” field.

LO 7.4.2.3

#16

A reference to the next sheet on the last sheet gives =#REF!.$A$1
A reference to the previous column on the first column gives: =$Sheet2.#REF!$1
A reference to the previous row on the first row gives: =$Sheet2.$A#REF!

#17

Named Functions are lambdas, not just Named Expressions. If you export a Google MYFUNC(a,b) to ODS you get the unusable Named Expression lambda(a,b,...definition using a & b). That might work in Excel, actually–I don’t have Excel.

I also like the sidebar editor, too, because it lets me easily copy an existing function, paste it into the sidebar editor, and then “parameterize” it as a Named Function. It is a great rapid development tool, but I don’t know if it would really lower the bar for programming or not, since programming using built-in spreadsheet functions is an order of magnitude more difficult than VBA and two orders of magnitude more difficult than Python in the first place.

I, personally, would love to see it in LO Calc, implemented as Google suggests, with a Lambda wrapper under Named Expressions.

2 Likes
#18

I would say so. It becomes basically a bare bones LISP at that point.
What do you need to get a lisp:

  • [x] Some basic math operators: + - * /
  • [x] Functions for control flow: IF
  • [x] Lists: Arrays work like lists and can be manipulated in much the same way
  • [ ] Lambda: just implement the version excel or google sheets has
  • [ ] an equivalent of defun: The named functions google sheets has would be that (now when thinking about it, named expressions with lambdas in them would do the same)
#19

But you really don’t have list, head, and tail management the way you would in Lisp. You don’t have those underlying generalized functional capabilities in spreadsheet functions. Also, in spreadsheets full set management is done through array formulas or a few array aware formulas like Sumproduct(), and it is not generalized. It looks functional in that you really don’t use side effects, but it is very limited. In Google sheets, write a named function that extracts the five largest numbers from a set (range) of 22 unique numbers and can insert them in exactly 5 cells without also spilling 17 blank cells below them.

#20

I don’t think another kind of language will help people to have an easier approach, even as I know Lisp/Scheme.

I’d like less Problems wits migration between Excel, GoogleSheets and Calc on the other side.

But somebody should mention:

Feature requests
Feature requests should go directly to Bugzilla as Enhancements
https://bugs.documentfoundation.org/enter_bug.cgi?product=LibreOffice&bug_status=UNCONFIRMED&bug_severity=enhancement

cited from:

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