Calc: add a new row when using array functions

[koopa]

I have one “table” on a sheet. Many columns, many rows. Back when I had simple formulas in each cell, it was easy to add a new row. I had many options:
A) Selecting the entire bottom row of the table’s cells, then dragging the handle down. This can create one or many new rows at once.
B) Selecting the row below the table (Shift+space, or on the Edit menu), then fill cells down (Ctrl+D, or on the Sheet menu). This creates one new row. A proper selection can create multiple rows.
C) Copy the last row and Paste below. Again, adding one or many new rows is easy.

My primary question. As my dataset has grown, I was talked into using array functions (CSE, or Ctrl+Shift+Enter style functions). More efficient calculations and smaller file sizes I was told. The problem for me has been adding a new row. None of the above three options seem to work for array functions. The only option I know is:

1. Select the entire array range (Ctrl+/).
2. Grow the range by dragging the handle down.
3. Manually update the formula to include the larger range.
   That this is three steps isn’t too bad. But it seemingly must be done for each column individually. For 40 columns, this is unworkable.

My second, possibly related question. All three options update the references WITHIN the table correctly. References TO the table do not update as it grows. What options do I have for solving this? Can a SUM or LOOKUP function (on another sheet) have it’s range update automatically? Can the table’s named range update automatically? Can a second table based on this table grow with it automatically? All pointers in the right direction are appreciated!

Inserting new rows above the final row may be an option. Then I can cut & paste the last row up. Leaving a blank row at the bottom may work too, but causes other problems. Neither of these is an option if there is even one column contains an array function, because inserting rows becomes disabled.

My first question here. Help clarifying my post is also appreciated.

[erAck]

I don’t understand your second question (btw, it’s usually better to not ask multiple questions in one question). What are “references TO the table” (as opposed to “WITHIN”)? Please edit your question and provide an example.

[jimk]

Array formulas can be difficult to work with. It sounds like you know how to solve both questions by not using them. If speed is such a problem, perhaps Base would be better than Calc.

[koopa]

Yes, I think using arrays has not been worth the trouble. Arrays are easy to create, but unworkably difficult to update. Using arrays halved my file size (but space is cheap) and halved my recalculate time. The one thing I did like about arrays was the guarantee that all formulas in a column are identical.

Yes, Base is becoming more appropriate for this task. Or Python using numpy or pandas, especially since I was already wanting Matplotlib or seaborn to chart the output.

I don’t think there is an answer to this array problem short of macros.

[jimk]

If you’re thinking of a Python macro on WIndows, there may be a problem.

C:\Program Files\LibreOffice\program\python.exe
>>> import numpy
ModuleNotFoundError: No module named 'numpy'
>>> import pandas
ModuleNotFoundError: No module named 'pandas'

On Linux, however, there should be no problem because those libraries can be installed.

[erAck]

No, you can’t insert or delete rows or columns in an area that is covered by an array formula. It would destroy the formula hence is prevented.

“More efficient calculations and smaller file sizes” is an over-generalization and without inspecting the individual case might even be wrong.