Ask Your Question
0

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

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

Vigilance 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. https://imgur.com/a/Hw3u0

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 retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

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

Comments

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.

https://imgur.com/a/gK3G1

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)

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.

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 152 times

Last updated: Sep 22 '17