+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : Combine data from 15 minute intervals to one value

  1. #1
    Registered User
    Join Date
    01-10-2012
    Location
    DC
    MS-Off Ver
    Excel 2007
    Posts
    5

    Combine data from 15 minute intervals to one value

    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

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Combine data from 15 minute intervals to one value

    It will be better understood if you attach a sample workbook.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    01-10-2012
    Location
    DC
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Combine data from 15 minute intervals to one value

    Quote Originally Posted by arlu1201 View Post
    It will be better understood if you attach a sample workbook.
    Sure thing. I simplied the worksheet to 194 rows containing data for the 15 minute intervals for two days.

    sampleworkbook.xlsx

  4. #4
    Registered User
    Join Date
    01-10-2012
    Location
    DC
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Combine data from 15 minute intervals to one value

    I went ahead and sorted the data by day (Mon, Tues...) and placed each in a new worksheet. It was a simpler solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1