Ask Your Question

Re-usable spreadsheet (or "when you don't know in advance the number of rows")

asked 2019-08-23 14:50:03 +0100

Clément gravatar image


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


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

1) moved down,

2) updated, so that it would compute the average of A1:A15.

Is there a way to simply do that?


edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2019-08-23 19:34:59 +0100

Cookievore gravatar image

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.

edit flag offensive delete link more


Thank you so much! It's the obvious thing, sometimes…

Clément gravatar imageClément ( 2019-08-23 20:06:11 +0100 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-08-25 14:00:49 +0100 )edit

answered 2019-08-23 21:57:56 +0100

m.a.riosv gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-08-23 14:50:03 +0100

Seen: 90 times

Last updated: Aug 23 '19