We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How can I reduce the number of cell in a sheet by taking the average of a set number of cells [closed]

asked 2017-09-22 18:46:56 +0200

Vigilance gravatar image

updated 2020-10-16 00:20:22 +0200

Alex Kemp gravatar image

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.

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)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-16 00:20:43.988693

1 Answer

Sort by » oldest newest most voted

answered 2017-09-22 20:34:43 +0200

JohnSUN gravatar image

Of course it possible. For example, like this file - C:\fakepath\Average of thousands.ods

edit flag offensive delete link more


Sorry 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.


I renamed the first sheet to "Main" to make it simple. Thanks again so much for helping me!

Vigilance gravatar imageVigilance ( 2017-09-22 21:52:14 +0200 )edit

So 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)))

JohnSUN gravatar imageJohnSUN ( 2017-09-22 22:03:08 +0200 )edit

I 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)


You suggested I use this code for the cell


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.

Vigilance gravatar imageVigilance ( 2017-09-29 17:20:13 +0200 )edit

Yes, 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

JohnSUN gravatar imageJohnSUN ( 2017-09-29 22:12:50 +0200 )edit

Question Tools

1 follower


Asked: 2017-09-22 18:46:56 +0200

Seen: 375 times

Last updated: Oct 16 '20