Define relation of colums as a python program?

[KlausB]

Calc is very useful for data input and visualization, but when there is a complex relationship between columns, if quickly gets somewhat tedious to write formulas. My main complaint is the inability of formulas to have variables.

The pain can be somewhat reduced by naming ranges and using additional columns, but I’d consider it much easier, if I could just run a python script over a calc file, reading out and setting ranges by either the usual BX13:BX59 syntax or by range names.

The script would have to be embedded in the document. If I’m not mistaken, that isn’t possible for Python scripts. Basic would also be okay, basically for that very reason.

Ideally, the entries should update while editing, like they’d do for formulas.

Is it somehow possible to do that?

[ms777]

@KlausB

Ideally, the entries should update while editing, like they’d do for formulas.

If this is a must I am pretty sure that you will have to program a formula function. This can be done in Basic or Python.
Formula functions can take multiple input parameters (cells or cell ranges) and can return cells or (contiguous) ranges. Input and returned cells / ranges should not overlap.
For python install Apso.
Good luck,
ms777

[Lupp]

Concerning visualization by charts there is no satisfying remedy without enhancements to the core (and possibly to odf specifications) because charts don’t accept calculated ranges. (They take named ranges, but convert them to explicitly addressed ranges on Save,)
Concerning the problem of avoiding locked ranges and a feature to create and use named variables in Calc by formulas - much above LET() - I have a solution on the level of “Proof of Concept” in Basic. If you are interested I would prefer to deliver a template containing the code and a few examples via PM meanwhile to avoid uninformed criticism.
Tell me.
(My native language is German.)

[KlausB]

I’d be happy to learn, see my over-engineered reply Define relation of colums as a python program? - #5 by KlausB ^^’

[KlausB]

Tried a bit, and arrived at

Function Min(a, b)
	IF(a > b) Then
		Min = b
	Else
		Min = a
	EndIf
End Function
		

Function WorkingHours(startTime, breaks, endTime)
    Dim h, m, trueWork, paidBreaks as Double
    h = 1/24
    m = h/60
    trueWork = endTime - startTime - breaks
    If trueWork > 5.5*h Then
    	paidBreaks = Min(breaks, 30*m)
    ElseIf trueWork > 2.75*h Then
        paidBreaks = Min(breaks, 15*m)
    Else
    	paidBreaks = 0.0
   	EndIf
   	WorkingHours = trueWork + paidBreaks
End Function

Now this has the problem, that if I write a formula

=WorkingHours(startTime, breaks, endTime)

using named ranges, the arguments are passed as array of the whole range, instead as “value of that range in the same row”, which makes everything a lot more complicated.

Especially since apparently arrays don’t support arithmetic operations. No c = a * b here. It works if I write it as

=WORKINGHOURS(B3,C3,D3)

but that’s not all that desirable…

[Villeroy]

ask118988.ods (15.8 KB)

[fpy]

@KlausB

ability

• LET function
• Named Ranges and Expressions

[KlausB]

That definitely helps, I missed the LET function.

But even then, editing large formulas is usually still quite complicated. The editor for formulas just isn’t really great - no code folding, no parenthesis matching, not even a monospaced font.

That makes the rules quickly get complicated. For instance, from real-world business logic of my personal time-keeping:

• The total working time is end time - start time - unpaid breaks.
• If the total working time is at least 3 hours in a day, up to 15 minutes can be breaks.
• If the total working time is at least 6 hours in a day, up to 30 minutes can be breaks.
• Paid pauses count as working time, e.g. 1:30 work, 30 minutes break, 1:15 work count as 3 hours of work and 15 minutes of unpaid break.
• In order to avoid messing up the presentation while editing, the value is only calculated if “startTime” and “endTime” are set. “breaks” is allowed to be omitted.

So in python that would be something like (assuming same “1.0 = 24h” units):

def working_hours(start_time, breaks, end_time):
    h = 1/24
    m = h/60
    true_work_time = end_time - start_time - breaks
    paid_breaks = min(
        breaks,
        0.50*h if (true_work_time >= 5.50*h) else
        0.25*h if (true_work_time >= 2.25*h) else
        0.00*h,
    )
    return true_work_time + paid_breaks

In Calc format, it would be something like

=LET(
    h, 1/24, 
    m, h/60 + N("Yes, using h here works, but having a real comment syntax would be nice"),
    trueWorkTime, endTime - startTime - breaks,
    paidBreaks, MIN(
        breaks,
        IFS(
            trueWorkTime >= 5.50*h, 0.50*h,
            trueWorkTime >= 2.25*h, 0.25*h,
            0.0
        )
    ),
    trueWorkTime + paidBreaks
)

… Actually, that looks pretty fine. However, it is rather sabotaged by the formula editor The use of a not-monospaced font alone does already a lot of damage to getting that right. I guess I might in the future take to editing complicated formulas in Emacs instead

Anyway… So that’s the first ad-hoc attempt. I got:

The big difference at that point: With Python I could do simple “print statement debugging”. No such option with Formulas.

Edit. The issue seems to have been with the IFS. I wrongly assumed that it has the behavior of "if last condition has no associated value, assume the condition IS the “else” value. The corrected formula reads

=LET(
    h, 1/24, 
    m, h/60 + N("Yes, using h here works, but having a real comment syntax would be nice"),
    trueWorkTime, endTime - startTime - breaks,
    paidBreaks, MIN(
        breaks,
        IFS(
            trueWorkTime >= 5.50*h, 0.50*h,
            trueWorkTime >= 2.25*h, 0.25*h,
            TRUE, 0.0
        )
    ),
    trueWorkTime + paidBreaks
)

but that’s exactly a kind of issue that would be much easier to avoid in Python (in this case the equivalent would be a syntax error).