+ Reply to Thread
Results 1 to 2 of 2

Charting - Hours of Attendance in a Service

  1. #1
    Registered User
    Join Date
    10-28-2013
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    3

    Charting - Hours of Attendance in a Service

    Hi everyone

    I have times booked to attend a service, for example:

    Booked1.jpg

    I want to create a graph (preferably a pie graph) that shows me the times booked, but Iíll have hundreds of lines.

    Therefore, I want to be able to group the same in/out times together.

    For example:

    Booked2.jpg

    So what I want to see in the graph (using the example), would be a pie graph with only 6 colours. I want to see that 9:00am-2:30pm has the largest wedge of the pie, then 8:00-2:00pm, etc.

    The information that I am trying to get from the graph, is which times are the most popular, next popular etc to least popular.

    Any ideas and assistance would be very helpful.

    Thank you

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,042

    Re: Charting - Hours of Attendance in a Service

    Without a sample workbook (translation: I'm too lazy to type in all the data above), I can only offer the concept.

    The way I would do this is:
    - Convert the data into an Excel Table since tables know how big they are so as you add or delete data formulas, pivot tables and charts based on the data keep up.
    - Tables also copy down formulas automatically which will come in handy if you need helper columns.

    I notice that you have a number of "overlaps" in the time. For example 9 Am to 1 PM, 9 AM to 2:30 PM, 9 AM to 5 PM. etc. Do you want these to be treated separately, or do you want to partition the times into "buckets" such as 9 Am to 10 AM? In the case of buckets, all three of these scenarios would fall into the 9 AM to 10 AM bucket.

    If you want separate times then you will need a helper column with a formula like: =Text(A2,"hh:mm a/p") & " to " & Text(A2,"hh:mm a/p") - then put this in a pivot table and chart on the helper column.

    If you want buckets then you'll need a matrix with start of the hour and end of the hour from 0:00 to 23:00 - fill in the helper column with a COUNTIFS for between the two times. Chart off the helper column.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ 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