CALC addressing addition

[SMac]

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

Anybody see advantages with this new operator?

[sokol92]

What about

INDEX(MyRange;row;column)

[anon73440385]

Aside and generally speaking: If you really are looking for a new feature, you are wrong here. Feature requests must be addressed to developers at https://bugs.documentfoundation.org/

[Lupp]

Anybody see advantages with this new operator?

Not really. Well, advantages probably, but disadvantages, too. (And the suggested syntax may not work out well. It also is specialized to single columns. Jutification?).
If you actually do rowwise calculations, the ordinary intrersection mode does as you want.
If you need to refer to different rows based on variable indices (or offset values), that’s “slightly” against the grain in spreadsheets. The fundamental machinery has to worry about the maintenance of recalculation trees. A cell containing references based on calculated values is a problem if it needs to be marked “dirty”.
Array-evaluation (iterated mode) also comes with serious disadvantages, but if a sheet is finally scaled it can be efficient.
You may expect only a single cell of a range to be a precedent to a specific cell, but the complete range will be treated as such in one.
(This is not based on definite knowledge, but on experiences from usage.)

[SMac]

Thanks sokol92, have submitted there

[SMac]

@Lupp

1. if named range is single column or single row could be generalised to index within col/row.
2. the point is clarity, I took up sokol92’s suggestion of using INDEX but look at this equation
   =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)) )
   It really doesn’t shout clarity does it ?
3. It got a whole lot more complicated when I tried to use the same named_ranges in the data area and forecast sections as the LINEST which generated those coefficients (DMOD_*) complained of self-reference even though there was none. Problem solved by using a duplicate named_range with an _F suffix. You were right on your la

[SMac]

@anon73440385 Thanks have submitted there

[Lupp]

From my experinence I can’t generally confirm the often repeated claim that named ranges are very helpful concerning clarity.
Clarity, imo, is best improved by reducing the complexity of formulas wherever possible. In many cases reduction of complexity can be achieved by introducing helpers (auxiliary columns mostly) for the calculation of expressions otherwise occurring as subexpressions of formulas - and sometimes even the same subexpression more than once there. .

[SMac]

@Lupp Yes and No.
Clarity with a mathematical formula is best achieved when it is naturally readable as a whole item, not split into smaller parts. But debugging is best done by checking on each part separately.

Have now solved my problem by using named ranges containing absolute column references and relative row references, from a suggestion elsewhere. This seems not to be documented in the CALC Guide. A pity as it makes a powerful tool. The equation above now looks like
=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 ) )
Where L1, L2 suffixes denote offset rows by 1 and 2. This to me is perfectly readable now, no unnecessary clutter. Thanks for your comments.

[SMac]

The search for clarity is best solved by using named ranges with absolute column references and relative row references.

For example, to refer to the column Cases. Select anywhere in the Cases column and Ctrl-F3 to add a named range Cases, select the current position you chose as the reference and remove the $ before the row number. This can be used to refer to the same row in the Cases column as the one currently being evaluated, rather than just one cell as with absolute referencing.

To refer to an offset row to the current one, create a new named range, say Cases_B1, this time select the row in the Cases column up one from the current cell called the Base Reference). Cases_B1 can be used to refer to Cases one cell up from the row in the spreadsheet being evaluated.

This provides a major step-up in legibility and clarity in spreadsheet workings.It is particularly useful when working across sheets. This example refers to row-wise workings but can be easily adapted for column-wise working using relative column refs and absolute row refs.