Share via

EXCEL sequential averaging operation

Anonymous
2018-01-12T07:47:13+00:00

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

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

Anonymous
2018-01-19T08:45:23+00:00

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)

Was this answer helpful?

1 person found this answer helpful.
0 comments No comments

6 additional answers

Sort by: Most helpful
  1. Anonymous
    2018-01-19T07:13:14+00:00

    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.

    Was this answer helpful?

    0 comments No comments
  2. Anonymous
    2018-01-13T15:26:33+00:00

    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)

    Was this answer helpful?

    0 comments No comments