Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 25 Aug 2019 14:00:49 +0200Re-usable spreadsheet (or "when you don't know in advance the number of rows")https://ask.libreoffice.org/en/question/205836/re-usable-spreadsheet-or-when-you-dont-know-in-advance-the-number-of-rows/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
1) moved down,
2) updated, so that it would compute the average of A1:A15.
Is there a way to simply do that?
Thanks.Fri, 23 Aug 2019 14:50:03 +0200https://ask.libreoffice.org/en/question/205836/re-usable-spreadsheet-or-when-you-dont-know-in-advance-the-number-of-rows/Answer by Cookievore for <p>Hi,</p>
<p>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</p>
<pre><code>= AVERAGE(A1:A5)
</code></pre>
<p>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 </p>
<p>1) moved down,</p>
<p>2) updated, so that it would compute the average of A1:A15.</p>
<p>Is there a way to simply do that?</p>
<p>Thanks.</p>
https://ask.libreoffice.org/en/question/205836/re-usable-spreadsheet-or-when-you-dont-know-in-advance-the-number-of-rows/?answer=205870#post-id-205870Hello 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.Fri, 23 Aug 2019 19:34:59 +0200https://ask.libreoffice.org/en/question/205836/re-usable-spreadsheet-or-when-you-dont-know-in-advance-the-number-of-rows/?answer=205870#post-id-205870Comment by Mike Kaganski for <p>Hello Clément, insert rows between A1 and A5 (not below A5) and watch how the formula <code>=AVERAGE(A1:A5)</code> increases insert by insert. Say you insert 4 rows and it will show you <code>=AVERAGE(A1:A9)</code>. 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.</p>
https://ask.libreoffice.org/en/question/205836/re-usable-spreadsheet-or-when-you-dont-know-in-advance-the-number-of-rows/?comment=205996#post-id-205996Also, 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](https://help.libreoffice.org/6.3/en-US/text/scalc/01/04060106.html?DbPAR=CALC#bm_id3165633) with code 101 to exclude hidden cells. That would allow you to add rows below the last visible row above that hidden one.Sun, 25 Aug 2019 14:00:49 +0200https://ask.libreoffice.org/en/question/205836/re-usable-spreadsheet-or-when-you-dont-know-in-advance-the-number-of-rows/?comment=205996#post-id-205996Comment by Clément for <p>Hello Clément, insert rows between A1 and A5 (not below A5) and watch how the formula <code>=AVERAGE(A1:A5)</code> increases insert by insert. Say you insert 4 rows and it will show you <code>=AVERAGE(A1:A9)</code>. 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.</p>
https://ask.libreoffice.org/en/question/205836/re-usable-spreadsheet-or-when-you-dont-know-in-advance-the-number-of-rows/?comment=205876#post-id-205876Thank you so much!
It's the obvious thing, sometimes…Fri, 23 Aug 2019 20:06:11 +0200https://ask.libreoffice.org/en/question/205836/re-usable-spreadsheet-or-when-you-dont-know-in-advance-the-number-of-rows/?comment=205876#post-id-205876Answer by m.a.riosv for <p>Hi,</p>
<p>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</p>
<pre><code>= AVERAGE(A1:A5)
</code></pre>
<p>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 </p>
<p>1) moved down,</p>
<p>2) updated, so that it would compute the average of A1:A15.</p>
<p>Is there a way to simply do that?</p>
<p>Thanks.</p>
https://ask.libreoffice.org/en/question/205836/re-usable-spreadsheet-or-when-you-dont-know-in-advance-the-number-of-rows/?answer=205885#post-id-205885There 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_id3151110Fri, 23 Aug 2019 21:57:56 +0200https://ask.libreoffice.org/en/question/205836/re-usable-spreadsheet-or-when-you-dont-know-in-advance-the-number-of-rows/?answer=205885#post-id-205885