Forum Discussion
Excel sequential cell averaging
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 cells contain 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.).
3 Replies
- You can esily do that using a pivottable.
- Select your data
- Insert, PivotTable
- Place your tie in the rowfield
- your numbers in the sigma area (bottom-right box)
- right-click the numbers and select "Summarize Values by", Average
- right-click the time and select Group.
- Select Minutes.Jan,
Thanks so much for this clear and simple instruction.
In fact I was able to perform minute averaging of my "second" data table
by this method. However, I found that this procedure would only return one
hour's worth of averaging (that is 60 one minute new values). This result was the
same (only one hour returned) for whatever value I entered for "ending at" in the
grouping menu. The pivot table I generated had about 3 hours of [second] data,
which was already way truncated from my original data (48 hours) entered into and
displayed ok as an Excel column. Is there anyway to keep/average the entire 48 hrs?
- Make sure your pivottable's sourcedata is pointing to the right cells
