Reduction of Massive Dataset

[jkharmer]

Hi there,

I have an excel document which has rows and rows of data, however, I want to average the data every 12 rows (the data is taken every 5 seconds and I want to convert it to 60 second averages). Is there any way to write a macro to generate a new column by averaging the 12 data points and then putting it into one uninterrupted column?

Thanks very much, James

 

[pgc01]

Hi James

You don't need a macro. A Pivot Table will do it automatically, just group the time by minute.

So:

- create a PT. Use your data as source
- right-click on the time, Group->Minutes
- on the data, choose Average

 

[jkharmer]

Thanks for trying to help, but I do not understand these pivot tables! I have selected the times and data, but it average all of the data - how do I tell it to just average 12 cells, then another 12, then another 12 etc?

 

[Dougie1]

You will have to ensure that your data column containing the time is formatted as Time.

Then in your Pivot Table > right click anywhere in the Time column > Select Group and Show Detail > Select Group > Select Minutes as your Grouping.

That should do it.