Calculating Averages Using VBA

**Sugar Ape** · 09-28-2009, 07:04 AM

I have a worksheet containing a list of measurements taken at 1 second intervals over the course of approximately 8 hours. Row A gives the time in hh:mm:ss, Row B contains the measurements.
I would like to calculate the average value per minute, (i.e., the average of rows 1 – 60, 61 – 120, 121-180 etc… ) and output the result in a new table.

As there are over 27000 rows in the worksheet, attempting to do this manually would take an age. I was hoping to use a macro to perform this task but having no VBA skills I don’t know where to start. Could anyone please point me in the right direction?

I’ve made a rather poor attempt to put some pseudo code together for what I am trying to achieve…

Please Login or Register  to view this content.

Many thanks
Stuart

**DonkeyOte** · 09-28-2009, 07:21 AM

Given use of XL2007 I would suggest use a Pivot Table - this is purpose built for this type of analysis.

Using your original table A:B as source (ensure you have titles for each column, eg Time / Value) you can set the PT up such that

ROW Label: Time
DATA Field: Value -- set to AVERAGE (rather than SUM)

Right click on any Time value in the PT and choose GROUP and select HOURS & MINUTES (nothing else).... with the PT active you can alter the layout/design of the PT to be however you wish using the options provided via the Ribbon (Design)