Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 10 May 2017 12:32:48 +0200How to fill empty cells with average?https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/I have a set of data where some columns have empty cells. How can I set those empty cells the value that is the mean of the rest of the numbers in the same column?Wed, 10 May 2017 07:50:55 +0200https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/Comment by Lupp for <p>I have a set of data where some columns have empty cells. How can I set those empty cells the value that is the mean of the rest of the numbers in the same column?</p>
https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94697#post-id-94697Bad idea! Don't mix up raw data and evaluation if you want to get reliable results.Wed, 10 May 2017 11:49:16 +0200https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94697#post-id-94697Answer by Mike Kaganski for <p>I have a set of data where some columns have empty cells. How can I set those empty cells the value that is the mean of the rest of the numbers in the same column?</p>
https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?answer=94675#post-id-94675Use [`AGGREGATE`](https://help.libreoffice.org/index.php?title=Calc/AGGREGATE_function) function, which ignores itself if it happens to be in its own range.
So, if you are working with, say, column C, and the range is C2:C10, and C4 is empty, then you may put there this:
`=AGGREGATE(1, 0, C2:C10)`Wed, 10 May 2017 07:56:12 +0200https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?answer=94675#post-id-94675Comment by Mike Kaganski for <p>Use <a href="https://help.libreoffice.org/index.php?title=Calc/AGGREGATE_function"><code>AGGREGATE</code></a> function, which ignores itself if it happens to be in its own range.</p>
<p>So, if you are working with, say, column C, and the range is C2:C10, and C4 is empty, then you may put there this:</p>
<p><code>=AGGREGATE(1, 0, C2:C10)</code></p>
https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94700#post-id-94700I see your point. However, I see this as a matter of taste. The function has documented and well-defined behavior, that makes it fit in this case. The case itself, however, is somewhat ... not good, but I'm in no position to tell user how to do their job.Wed, 10 May 2017 12:14:21 +0200https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94700#post-id-94700Comment by Mike Kaganski for <p>Use <a href="https://help.libreoffice.org/index.php?title=Calc/AGGREGATE_function"><code>AGGREGATE</code></a> function, which ignores itself if it happens to be in its own range.</p>
<p>So, if you are working with, say, column C, and the range is C2:C10, and C4 is empty, then you may put there this:</p>
<p><code>=AGGREGATE(1, 0, C2:C10)</code></p>
https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94678#post-id-94678This means "[Invalid Character](https://help.libreoffice.org/Calc/Error_Codes_in_Calc)". Make sure you use proper argument delimiters (I used "**,**" as this seems to be expected on this forum; my system, e.g., uses "**;**").Wed, 10 May 2017 08:17:33 +0200https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94678#post-id-94678Comment by LearningLibre for <p>Use <a href="https://help.libreoffice.org/index.php?title=Calc/AGGREGATE_function"><code>AGGREGATE</code></a> function, which ignores itself if it happens to be in its own range.</p>
<p>So, if you are working with, say, column C, and the range is C2:C10, and C4 is empty, then you may put there this:</p>
<p><code>=AGGREGATE(1, 0, C2:C10)</code></p>
https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94676#post-id-94676Hmm. I wrote =aggregate(1,0,Q1:Q153) when Q7 was activated but the result was Error:501.Wed, 10 May 2017 08:11:07 +0200https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94676#post-id-94676Comment by Lupp for <p>Use <a href="https://help.libreoffice.org/index.php?title=Calc/AGGREGATE_function"><code>AGGREGATE</code></a> function, which ignores itself if it happens to be in its own range.</p>
<p>So, if you are working with, say, column C, and the range is C2:C10, and C4 is empty, then you may put there this:</p>
<p><code>=AGGREGATE(1, 0, C2:C10)</code></p>
https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94698#post-id-94698I would hesitate to suggest the usage of functions like AGGREGATE and SUBTOTAL. They are outside, imo, of the range of reasonable spreadsheet usage. They are aliens anyway, not even accepting the ways functions are identified generally in spreadsheets and also when called via the api ('FunctionAccess').
May I suppose these functions were introduced just for "compatibility" once? Who knows?Wed, 10 May 2017 11:59:53 +0200https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94698#post-id-94698Comment by Lupp for <p>Use <a href="https://help.libreoffice.org/index.php?title=Calc/AGGREGATE_function"><code>AGGREGATE</code></a> function, which ignores itself if it happens to be in its own range.</p>
<p>So, if you are working with, say, column C, and the range is C2:C10, and C4 is empty, then you may put there this:</p>
<p><code>=AGGREGATE(1, 0, C2:C10)</code></p>
https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94704#post-id-94704Quoting @Mike Kaganski: "...but I'm in no position to tell user how to do their job."
That's an honorable statement and sounding smoothly modest.
Being an old teacher I cannot simply agree in every case. And this is more about "intermediary rows" than about the "alien" functions.
To get accepted in my teacheresque habits I am ready to gladly accept being teached in turn.
( @LearningLibre may have chosen his user name consciously.)Wed, 10 May 2017 12:32:48 +0200https://ask.libreoffice.org/en/question/94674/how-to-fill-empty-cells-with-average/?comment=94704#post-id-94704