Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 29 Sep 2017 22:12:50 +0200How can I reduce the number of cell in a sheet by taking the average of a set number of cellshttps://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/I have a spreadsheet with about half a million rows in it. For the sake of having the data t calculate Min, max, and average, this is helpful. However this is really hard to read. I want to know how I can take the average of like, a thousand cells at a time and output that to another page. Here is a screenshot of my data.
![](https://i.imgur.com/y7VqtkO.png)
In the screenshot data is logged multiple times a second, I'd like to reduce that to maybe, 50 times a second.
Any help is greatly appreciated!
(*edit:* activated screenshot)Fri, 22 Sep 2017 18:46:56 +0200https://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/Answer by JohnSUN for <p>I have a spreadsheet with about half a million rows in it. For the sake of having the data t calculate Min, max, and average, this is helpful. However this is really hard to read. I want to know how I can take the average of like, a thousand cells at a time and output that to another page. Here is a screenshot of my data.
<img src="https://i.imgur.com/y7VqtkO.png" alt=""></p>
<p>In the screenshot data is logged multiple times a second, I'd like to reduce that to maybe, 50 times a second.
Any help is greatly appreciated!</p>
<p>(<em>edit:</em> activated screenshot)</p>
https://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/?answer=132248#post-id-132248Of course it possible. For example, like this file - [C:\fakepath\Average of thousands.ods](/upfiles/15061052767390183.ods)Fri, 22 Sep 2017 20:34:43 +0200https://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/?answer=132248#post-id-132248Comment by JohnSUN for <p>Of course it possible. For example, like this file - <a href="/upfiles/15061052767390183.ods">C:\fakepath\Average of thousands.ods</a></p>
https://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/?comment=132260#post-id-132260So replace string "Trial5_100coil_smallest" (old sheet name) with word "Main" and get work formula in column A like as `=IF(((ROW()-2)*1000+1)>COUNT(Main!A:A);"";"Main.B"&((ROW()-2)*1000+1)&":B"&(MIN((ROW()-1)*1000;COUNT(Main!A:A)+1)))`Fri, 22 Sep 2017 22:03:08 +0200https://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/?comment=132260#post-id-132260Comment by Vigilance for <p>Of course it possible. For example, like this file - <a href="/upfiles/15061052767390183.ods">C:\fakepath\Average of thousands.ods</a></p>
https://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/?comment=132259#post-id-132259Sorry to bother you again. I really appreciate you answering my question, but I'm trying to figure out exactly how you did this. I've looked up all the functions you used and have a general feeling for how it all works. I was trying to implement a very similar function in my spreadsheet but the cell won't fill in. Here is what it looks like.
https://imgur.com/a/gK3G1
I renamed the first sheet to "Main" to make it simple. Thanks again so much for helping me!Fri, 22 Sep 2017 21:52:14 +0200https://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/?comment=132259#post-id-132259Comment by Vigilance for <p>Of course it possible. For example, like this file - <a href="/upfiles/15061052767390183.ods">C:\fakepath\Average of thousands.ods</a></p>
https://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/?comment=133281#post-id-133281I know it's been awhile since I commented, but I was hoping you could help me one last time. Here is a link to my calc sheet. (Google Drive link)
https://goo.gl/gvDWdJ
You suggested I use this code for the cell
=IF(((ROW()-2)*1000+1)>COUNT(Main!A:A);"";"Main.B"&((ROW()-2)*1000+1)&":B"&(MIN((ROW()-1)*1000;COUNT(Main!A:A)+1)))
I changed the "!" in front of Main to a "." because I'm pretty sure that's what it is suppose to be (I could be wrong), I'm still getting an empty white cell.Fri, 29 Sep 2017 17:20:13 +0200https://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/?comment=133281#post-id-133281Comment by JohnSUN for <p>Of course it possible. For example, like this file - <a href="/upfiles/15061052767390183.ods">C:\fakepath\Average of thousands.ods</a></p>
https://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/?comment=133294#post-id-133294Yes, you are right - must be dot, not "!" Just answering someone's question, I had to go to Tools-Options-Calc-Formula and change Formula syntax from Calc to Excel. I forgot to fix it. Thank you for your comment!
Your problem is in the values of time - it's not numbers, it's lines. Count() does not consider these cells. See [solution](https://drive.google.com/file/d/0B94gAFPvcES-WDV0MGdXVkhHWkU/view?usp=sharing)Fri, 29 Sep 2017 22:12:50 +0200https://ask.libreoffice.org/en/question/132236/how-can-i-reduce-the-number-of-cell-in-a-sheet-by-taking-the-average-of-a-set-number-of-cells/?comment=133294#post-id-133294