[Libreoffice-bugs] [Bug 142954] CALC addressing Enhancement

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=142954

--- Comment #7 from Simon <simon2 at smcalister.me.uk> ---
(In reply to m.a.riosv from comment #1)
> Do you known about Menu/Tools/Options/LibreOffice calc/Formula/Formula
> syntax - Excel R1C1.
> 
> And maybe named ranges with relative address, not absolute, can help also.

Just an update spent yesterday applying relative named ranges to my models. I
did not understand them fully at first, and did not realise that the reference
was relative to the base address (which is current selection). I think this is
largely undocumented in CALC - I didn't find it anyway. Only when my models
blew up with circular ref error did I twig that the row references could be
relative to the current selection. This discovery obviates the need for
additional columns of lagged data I mentioned in my first reply. 

So overall, the mixed absolute and relative referencing in named ranges has
solved my problem. The shorter equation now looks like this
=EXP( DMOD_0    + DMOD_1 * LN( CasesSw_L1  /  CasesSw_L2 ) * (1 + Variant_DEA2
*  Variant_DEA) +    DMOD_2 * LN( CasesSw_L2 ) +    DMOD_3 * LN(MAX(1%, 
HospLoad_L2))  +    DMOD_4 * LN(MAX(1,  Deaths_L1 )) *(1 + Variant_DEA1 * 
Variant_DEA) +     DMOD_5 * LN( Vac_Delayed ) +     DMOD_6 * LN(  Health_Trend
) )

Which to me, at least, is now perfectly readable.

This is a powerful way to structure your spreadsheets, a pity that almost
no-one knows about it. What it needs is a tutorial in the guide written by a
non-geek. I don't mean that offensively, but by someone who is interested in
the application of a spreadsheet to solve a problem, rather than an IT outlook.

THANK YOU for the suggestion once again.
I could mark this as Resolved - what do you think?

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20210622/de10b46b/attachment.htm>