+ Reply to Thread
Results 1 to 5 of 5

Sum orders by 15 minute periods

  1. #1
    Registered User
    Join Date
    01-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Sum orders by 15 minute periods

    I have 1 year of Oracle table data ready to go into Excel. In Execl I'd like to count orders by 15 minute periods (buckets, on the quarter hour). Some 15 minute periods have many orders while some have 0. Is there an Excel function (or a series of a few steps) that will look at column A & B and populate subsequent columns in this way (i.e., columns E & F below)?

    Original Data Columns
    A (raw data timestamp) B (orders)
    ************************* ************
    1/1/2010 1:05 AM, 1
    1/1/2010 1:11 AM, 1
    1/1/2010 1:20 AM, 1
    1/1/2010 1:32 AM, 0
    1/1/2010 1:48 AM, 1
    for a whole year
    ...

    Interim Columns (I supose would be necessary)
    C (beginning minute
    timestamp of buckets) D (orders)
    ************************* ************
    1/1/2010 1:00 AM, 1
    1/1/2010 1:00 AM, 1
    1/1/2010 1:15 AM, 1
    1/1/2010 1:30 AM, 0
    1/1/2010 1:45 AM, 1

    E (the ultimate goal column) F (orders aggregated into proper th bucket)
    ********************************* *************************************************
    1/1/2010 1:00 AM 2
    1/1/2010 1:15 AM 1
    1/1/2010 1:30 AM 0
    1/1/2010 1:45 AM 1

    The purpose is to graph E & F for certain high volume order days of the year.
    Thanks in advance!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,375

    Re: Sum orders by 15 minute periods

    Hi gco111 and welcome to the forum,

    Pivot Tables allow you to group by the minute and then you can specify a group of 15 minutes long. It allows you to count and regroup by day etc.

    Here is a picture of how to do it in older versions. It is easier in 2010 Excel. You can also do a pivot chart from the data.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    01-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Sum orders by 15 minute periods

    Thank you for the pivot table solution. My objective has changed a bit so I'm now wondering if there is a programatic solution in my situation. The data looks like this - well actually 4268 rows (3 days) in total, but here's an exact 15 minute subset:
    01/31/2011:00:00 1
    01/31/2011:00:01 0
    01/31/2011:00:02 2
    01/31/2011:00:03 0
    01/31/2011:00:04 1
    01/31/2011:00:05 3
    01/31/2011:00:06 1
    01/31/2011:00:07 2
    01/31/2011:00:08 2
    01/31/2011:00:09 0
    01/31/2011:00:10 1
    01/31/2011:00:11 0
    01/31/2011:00:12 0
    01/31/2011:00:13 1
    01/31/2011:00:14 2
    ...
    I'd really like to be able to sum Column B by 15 minutes intervals based on the Column A character/string timestamps. (perhaps I would need to convert Column A to a real date/time format first?)

    I'm picturing code that would place grouped counts in rows below the source/input rows like this:
    01/31/2011:00 16
    01/31/2011:15 n (for the next set of 15 minutes/rows)
    ... (and so forth)

    It would be really great if I could also adjust the code to generate this by 5 and 10 minute intervals also, such as (5 min):
    01/31/2011:00:00 4
    01/31/2011:00:05 8
    01/31/2011:00:10 4
    ...
    and (10 min.):
    01/31/2011:00:00 12
    01/31/2011:00:10: n
    01/31/2011:00:20: n
    ...
    I would be happy to post this in the Programming area of this excellent forum if that is appropriate. Thank you.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Sum orders by 15 minute periods

    A Pivot Table (with adjustable grouping) would seem the obvious choice here
    (adjusted date time strings per locale and headers of course)

    Attached is one approach which utilises

    1. Dynamic Named Range as source for PT

    2. Formula to adapt the datetime strings (US locale assumed)

    3. Entry Cell for Interval Gap of Interest

    4. VBA Event Code to update the PT as and when the Interval (3) is modified
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-30-2011
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Sum orders by 15 minute periods

    This solution is very helpful. The resources under recommended reading will be useful as well. Thank you!

+ 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