Hi,
I’m trying to write a spreadsheet that I could re-use easily even when a number of rows change.
One of the cell (say, on row 6) compute the average, of, say, five other cells, using
= AVERAGE(A1:A5)
The thing is that, sometimes, I want to add, say, 10 rows, after the 5th row, because some situation change.
I’d like that cell computing the average to be automatically
moved down,
updated, so that it would compute the average of A1:A15.
Is there a way to simply do that?
Thanks.
Hello Clément, insert rows between A1 and A5 (not below A5) and watch how the formula =AVERAGE(A1:A5) increases insert by insert. Say you insert 4 rows and it will show you =AVERAGE(A1:A9). If it is a difference in your use case to insert rows above instead of below, please explain why. Average would calculate anyway, no matter where you insert rows and which order of the numbers but the ‘automatic’ update of the formula would not work, if you insert below your range.
Thank you so much!
It’s the obvious thing, sometimes…
Also, you may include an empty separator row between your data and the average; include it into the range of the average; make it hidden; and use SUBTOTAL with code 101 to exclude hidden cells. That would allow you to add rows below the last visible row above that hidden one.
There is the option Menu/Tools/Options/LIbreOffice calc/General - Expand references when new columns/rows are inserted.
With that activated when in the last row with data you can use “Insert row below” and the formula is adapted properly.
https://help.libreoffice.org/6.3/en-US/text/shared/optionen/01060300.html?DbPAR=SHARED#bm_id3151110