EXCEL sequential averaging operation

Question

I have successfully imported a simple (but long!)  .CSV list into an EXCEL sheet. It consists of one column of times, one row per second.

The next contiguous column is corresponding [integer] data numbers. There are several thousand rows (seconds)! These second time

intervals are too fine for my use. I want to average the data cell numbers over many seconds (say 60, a minute) sequentially. That is, end

up with one column of minute time values with a next contiguous column whose cells have the average of the original preceding 60 

cells of second data. This list is too long to do this "manually" (averaging individual groups of 60 and inserting that result as a new cell, etc.).

Answer 1

Willaim,

re:  formula use

Do you follow this portion of my directions...  "Fill the formula down"  ?

  https://www.techrepublic.com/blog/five-apps/five-tips-for-working-efficiently-with-excels-fill-handle/

  https://www.keynotesupport.com/excel-basics/excel-auto-fill-handle-numbers-dates.shtml

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

Answer 2

Excel 2010/2013/2016 Power Query (aka Get & Transform)

Group() is a function in Power Query

and found in its "M" programming language.

See the book "Power Query for Power BI and Excel" by Chris Webb.

At 440 pages, it's rather verbose.

PowerPivot is a good alternative,

but that's another book.

Answer 3

The results you show are exactly what I want to do. Thanks!

However how did you do this (Table1-----> Table 2)? Could you

be more verbose, I'm at a very elementary level on Excel. "Group()"

is unknown to me.

Answer 4

Excel 2010/2013/2016 Power Query (aka Get & Transform)

With Group()

http://www.mediafire.com/file/ldnc8dd53hh3szm/01_12_18.xlsx

http://www.mediafire.com/file/r3p714t99348w7f/01_12_18.pdf

Answer 5

Use a pivot table.

Select both columns and use Insert / Pivot Table.  Then drag the header with times to the row area, and the header with your data to the data area. Set the data field option to Average, and the pivot table will show averages by hour (probably). Expand the Hours, and you will see averages by minute. Copy the pivot table and paste values elsewhere, then you can delete your original data and the pivot table.

If Excel does not auto-group the values, you may need to do it manually.