Results 1 to 10 of 10

Thread: Pivot Table - Volume by Day and Half Hour

  1. #1
    Junior Member
    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0

    Pivot Table - Volume by Day and Half Hour

    Hi there! I've asked this similar question different ways through different forums for years now and most often don't even see a reply Hope someone here can help!

    Quick summary, I am trying to quantify the amount of volume for each day of the week broken down by half hour.

    I have a long list of data which is broken down by hh:mm with a field to the right indicating which day of the week its from. I'm pretty unfamiliar with pivot tables though. How can I take data similar to below and chart out volume on particular days by half hour? 00:00-00:30 would be midnight to 12:30a.

    Time of Day Day of Week
    00:00 Monday
    00:02 Monday
    00:02 Monday
    00:03 Monday
    00:03 Monday
    00:03 Monday
    00:00 Tuesday
    00:01 Tuesday
    00:01 Tuesday
    00:02 Tuesday
    00:04 Tuesday
    00:04 Tuesday
    00:05 Tuesday


    Thanks for any ideas you can suggest!

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Ian,

    Welcome to ExcelFox !!

    Could you please attach a sample workbook ?
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Quote Originally Posted by Admin View Post
    Hi Ian,

    Welcome to ExcelFox !!

    Could you please attach a sample workbook ?
    Thanks! Sure, putting one together now!

  4. #4
    Junior Member
    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Here ya go. Took a few times of trimming to get it down to the minimum attachment size. =)
    Attached Files Attached Files

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    You haven't indicated what volume is. What do you mean when you say 'volume'?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  6. #6
    Junior Member
    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Ah my mistake. Volume you could say is a call, which is indicated by a time. So essentially the times would illustrate call volume during specific times, broken down by day.

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Wasn't sure what you were looking for exactly. Hope this is close.
    Attached Files Attached Files
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Junior Member
    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    That's just what I'm looking for! From that I could make a simple chart where the 30min intervals are on the x-axis and the count for each one is a point on the line. Then I'd have a separate line for each day of the week.

    Since I'm so new to pivot tables I apologize if this is a basic question. So if I'm reading this correct, the pivot table is actually not reading the E column at all, but using the new time of day labels you made from column H which are then counted, is that right? If so, that seems like a pretty simple way to count the intervals. Great thinking. Trying to build off what you made I went and paste the rest of the data that wouldn't fit in my sample but I noticed the chart doesn't change so I suspect I have to expand the range somewhere. I noticed the formula for the H cells is =VLOOKUP(TIMEVALUE(E4&":00"),$Q$3:$R$50,2) where the only changing text in that is the E# field. Is it possible to drag that down so it duplicates to new rows of data I add for the rest of the days of the week? I tried but figure I might be doing something wrong since none of the numbers changed.
    Last edited by Ian Herndon; 07-07-2012 at 09:45 PM.

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    When you say duplicates to new rows of data, do you mean the look up range of cells? Or the data in your original table? Maybe you want to explain with a sample. By the way, if you want to post a larger file, try using something like box.net or other file sharing sites, and post the link here.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    Junior Member
    Join Date
    Jul 2012
    Posts
    6
    Rep Power
    0
    Actually I got it figured out! I made a second pivot table right below it and just kind of copied the placement of each of the field names into the various sections, row labels, values, etc. That is sooo much faster than what I was doing before it's crazy. Also, I noticed what was required to do that was pretty basic, yet really powerful at the same time. You've been a great help, ty so much sir!

Similar Threads

  1. Preparing Trial Balance Using Pivot Table
    By Zaigham in forum Excel Help
    Replies: 4
    Last Post: 05-14-2013, 11:57 AM
  2. Create a Pivot table
    By NITIN SHETTY in forum Excel Help
    Replies: 3
    Last Post: 01-26-2013, 11:01 AM
  3. Lookup and Count Using Pivot Table
    By RobExcel in forum Excel Help
    Replies: 2
    Last Post: 12-21-2012, 11:08 AM
  4. Pivot Table Count No of Items per Category
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 07-04-2012, 10:49 PM
  5. Filter more than one pivot table at one time
    By larryt1940 in forum Excel Help
    Replies: 8
    Last Post: 05-04-2012, 06:45 PM

Posting Permissions

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