Hello,

This will make my first post on this forum so please excuse any blunders I make this time. Also I'm an Excel novice so I'll try to make this as concise as possible.

  • I have an Excel 2007 spreadsheet containing several worksheets each with 64,000+ rows.
  • Each row (of each worksheet) represents data for a 15 minute interval for an entire year (data carries over to next worksheet).
  • The intervals begin on worksheet 1/row 2 (sheet 1/row 1 is column headers) at 1/10/11 00:00:00 and finish on the last worksheet at 12/31/11 23:30.
  • The sample data set below contains raw data for 1 hour for a category we're looking at (there are several categories).
  • The data for each of the 15 minute intervals for each hour for each category (i.e. critical) is to be summed and combined into 1 hour intervals (see sample analysis below) to produce averages of each category for each hour for the year.

Now that that is out of the way, I'd like to know the best way to sum each interval's data into a composit 1 hour value.

The concern is not with "how" to sum a few of the interval data; that's easy. The concern is with the best way to do it for each interval set in 64,000+ rows which, if performed manually, will consume far too many resources (time & personnel).

I hope my writeup was clear enough to grasp. Thanks!

sample data set
critical date time day offered answered
1 1/10/11 00:00:00 Monday 0 0
1 1/10/11 00:15:00 Monday 3 2
1 1/10/11 00:30:00 Monday 7 5
1 1/10/11 00:45:00 Monday 7 6

sample analysis
critical date time avg.offered avg.answered
1 1/10/11 00:00:00 4.25 3.25
1 1/10/11 01:00:00 n.nn n.nn
1 1/10/11 02:00:00 n.nn n.nn