Bug 142954

Summary:	CALC addressing Enhancement
Product:	LibreOffice	Reporter:	Simon <simon2>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED NOTABUG
Severity:	enhancement
Priority:	medium
Version:	6.4.7.2 release
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:

Description Simon 2021-06-20 09:25:13 UTC

Description:
This suggestion would make life so much easier for me. I am working with about 10 sheets each with 15-20 columns of time-series data. I am creating some complex forecasting equations which refer across sheets. Although parameterised I have to change the structure frequently. They look unintelligible in CALC notation.

Imagine if you could use named ranges to refer to the columns the equations would be intelligible again. Well I can do this in the data range using named ranges as arrays - simple.

However, where results are calculated for forecasting on a row by row basis (as time increases) I have found arrays are unsuitable and generate weird error codes for no discernable reason. So back to single cell formulas (arrays are probably inefficient anyway since they need to be reevaluated for each new row).

My ideal would be a notation like column_name@row# example: apples@34 OR perhaps more generally the @ operator would be the index to a cell within the named range. Either would be good, but the row/index would need to be increased/amended as you extend or copy the cell. You can do something similar with OFFSET( apples, $B34, 0, 1, 1) where column B contains the index you want to use, but it is clumsy.

Its a simple change but would make a world of difference to power-users in terms of legibility.

Actual Results:
none - enhancement request

Expected Results:
none - enhancement request

Reproducible: Always

User Profile Reset: No

Additional Info:
none - enhancement request

Comment 1 m_a_riosv 2021-06-20 12:19:25 UTC

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.

Comment 2 [REDACTED] 2021-06-20 15:36:32 UTC Comment hidden (obsolete)

I can't see the benefit of "=apples@34" over "=INDEX(apples;34)"

Comment 3 Simon 2021-06-21 00:43:20 UTC

(In reply to Uwe Auer from comment #2)
> I can't see the benefit of "=apples@34" over "=INDEX(apples;34)"

No you can't because you know nothing of what I am doing. Why don't you tell me that this equation (one of the shorter ones) is simple and clear and then I can safely ignore you forever.


=EXP( DMOD_0  +   DMOD_1 * LN(INDEX( CaseSw1_F, $B7) / INDEX( CaseSw1_F, $B7 -1)) * (1 + Variant_DEA2 * INDEX( Variant_DEA_F, $B7)) +    DMOD_2 * LN(INDEX( CaseSw1_F, $B7 -1)) +    DMOD_3 * LN(MAX(1%, INDEX( HospLoad_F, $B7 -2)))  +    DMOD_4 * LN(MAX(1, INDEX( Deaths_F, $B7 -1))) *(1 + Variant_DEA1 * INDEX( Variant_DEA_F, $B7)) +     DMOD_5 * LN(INDEX( Vac_Delayed_F, $B7)) +     DMOD_6 * LN(INDEX( Health_Trend_F, $B7)) )

Comment 4 Simon 2021-06-21 01:11:01 UTC Comment hidden (obsolete)

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


OK never used R1C1
I'm not seeing how that would help. The point is to introduce names for columns so it is legible.

Named ranges with fixed column and relative row enables me to address the same row with a named column - halfway there. I also need to refer back a few rows too - don't see how to do that? 

I am using INDEX( myRange, $B7 ) where column B contains the index for the range. Still messy though.

Comment 5 Simon 2021-06-21 06:44:51 UTC

(In reply to Simon from comment #4)
> (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.
> 
> 
> OK never used R1C1
> I'm not seeing how that would help. The point is to introduce names for
> columns so it is legible.
> 
> Named ranges with fixed column and relative row enables me to address the
> same row with a named column - halfway there. I also need to refer back a
> few rows too - don't see how to do that? 
> 
> I am using INDEX( myRange, $B7 ) where column B contains the index for the
> range. Still messy though.

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


Actually though, the mixed addressing in a named range is 75% of a solution

THANK YOU. 

Just need to set up hidden columns of lags (delayed rows) as necessary and name them - a worthwhile trade-off for the added simplicity in expression. 

It'll be great if I can use the same named ranges in Data and Forecast sections too - at the moment with absolute addressing in ranges CALC complains about self referencing (522) because of overlap of named ranges between the two areas. Hopefully with mixed addressing CALC will default to single cell referencing, but we will see.

Comment 6 [REDACTED] 2021-06-21 12:49:35 UTC

(In reply to Simon from comment #3)
> (In reply to Uwe Auer from comment #2)
> > I can't see the benefit of "=apples@34" over "=INDEX(apples;34)"
> 
> No you can't because you know nothing of what I am doing. Why don't you tell
> me that this equation (one of the shorter ones) is simple and clear and then
> I can safely ignore you forever.

I'm deeply impressed by the complexity of your formulas, but why did you not provide that as an example (and I still can't see the benefit). So please ignore me forever...

Comment 7 Simon 2021-06-22 14:12:52 UTC

(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?

Comment 8 Timur 2022-08-01 10:21:37 UTC

I understand we may mark as NotABug meaning that LO can do it with existing features. 

>I think this is largely undocumented in CALC - I didn't find it anyway
You may submit a bug for Help. 

> What it needs is a tutorial in the guide written by a non-geek.
Here is a place for that, if you are willing to do it. 
https://wiki.documentfoundation.org/Faq/Calc