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

Ok, thanks, I have used this formula and it works! However maybe I'm using it naively.

Are you suggesting that I have to manually apply this formula at each 60 second interval (changing

its interval numbers for each new minute)? I was hoping to be far more automated since I have to

 average over each minute in 48 hrs (or more eventually)! I need something which runs thru

the entire list of seconds (in 48 hrs) averaging consecutive groups of 60, leaving a new list of minutes

(in same 48 hrs) paired with corresponding averaged data numbers.

But, maybe your formula can be made to concatenate some way I dont appreciate.

Answer 3

William,

re: sequential average

The Offset function can be used to average groups of x items...

   Seconds in column B, starting in row 2

   Data numbers in column C

   Average formula in column D...   =AVERAGE(OFFSET($C$2:$C$61,60*(ROW()-2),0,60,1))

NOTE: 

60 is the group size.

The interval is determined by the "60*(ROW()-2)" portion of the formula.

It must resolve to zero in the first instance of the formula (no offset occurs).

So if the first "group" starts in row 5 that portion should read...  "60*(ROW()-5)"

Fill the formula down, but don't go too far.  1000 items in groups of 60 only requires 16 formulas.

(no book required) <grin>

'---

Jim Cone

Portland, Oregon USA

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

(free & commercial excel add-ins & workbooks)