Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Variation of a Gantt chart

  1. #1
    New Member
    Join Date
    Sep 2010
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Variation of a Gantt chart

    Hi. I'm brand new here and definitely an Excel novice. Here's what I'm looking for:

    Our local 911 center makes some statistical data available to each service they dispatch for in excel format. Here is an example of a snippet of data:

    HTML Code:
    LOCATION    UNIT NAME       DISPATCHED           AVAILABLE        
    Apple St    Ambulance 1     9/29/2010 00:10      9/29/2010 00:58
    Pear St     Ambulance 7     9/29/2010 02:18      9/29/2010 03:02
    Plum Alley  Ambulance 4     9/29/2010 02:40      9/29/2010 03:18
    Peach Lane  Ambulance 1     9/29/2010 07:04      9/29/2010 07:58
    Grape Way   Ambulance 2     9/29/2010 07:10      9/29/2010 08:03
    Orange St   Ambulance 1     9/29/2010 13:18      9/29/2010 14:15
    The data is sorted chronologically by time dispatched.

    What we want to do is chart WHEN each unit is committed to an incident. A horizontal bar chart, where the entire length of a row on the chart would represent a 24 hour period (one day). For each UNIT NAME, a colored block/section representing when they were on a call and blank/white when they were available.

    Using the example above, 'Ambulance 1' would have 3 blocks of time on the same bar, showing they were committed from 00:10 to 00:58, 07:04 to 07:58 and again from 13:18 to 14:15. Each UNIT NAME would have its own bar. In the above example, the chart would have a total of 4 bars, sorted by UNIT NAME.

    Hopefully, I've sufficiently explained what I'm looking for. Please ask for any clarifications or omissions. travelug@yahoo.com

    Thanks in advance for any/all help!


  2. #2
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,553
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Variation of a Gantt chart

    This is a Gantt chart. See Gantt Charts in Microsoft Excel, Advanced Gantt Charts in Microsoft Excel, and Gantt Chart Links for tutorials and links to other resources.

    Set up your data with one row per unit. Put times for each unit chronologically left to right. Ambulance 1 has six times: dispatched, available, dispatched, available, dispatched, available; the other units have only two times: dispatched, available. This fills the first eight columns.

    The next six columns are Available, On Call, Available, On Call, Available, On Call. The first Available column is merely a link to the first Dispatched time for each unit. The first On Call column is the first Available time minus the first Dispatched time, so it's the duration of the call. The next available column is the second Dispatched time minus the first Available time, so it's the gap between calls. Complete this logic for the rest of the calculated columns.

    Select the last six columns and create a stacked bar chart. Hide the three Available series by formatting them with no border and no fill. Format all three On Call series with the same fill. Go to the source data dialog and add either location (the streets) of unit (the ambulances) as the Category (X axis) labels. Format the left hand axis so the categories are plotted in reverse order, and if desired have the value axis cross at the maximum category.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  3. #3
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,553
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Variation of a Gantt chart

    There's an even easier approach, which I've written about in my blog, in Gantt Chart for Repeated Tasks
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

  4. #4
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    4,553
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Variation of a Gantt chart

    Here's another way.

    The data is shown below. The ambulance numbers in column B are really just the numbers in the cells (1, 7, 4, 1, 2, 1), formatted using a custom number format of

    "Ambulance "0

    The durations in column E are Available minus Dispatched, formatted as h:mm times.



    Select column B of the data and insert an XY Scatter chart (first chart below). Note the Y axis shows Ambulance Y, not just Y.

    Select the series in the chart. The series formula is

    =SERIES(Sheet1!$B$1,,Sheet1!$B$2:$B$7,1)

    Series name in cell B1, no X values, Y values in B2:B7. Click between the two commas, then select the dispatch times in the data, so the series formula becomes

    =SERIES(Sheet1!$B$1,Sheet1!$C$2:$C$7,Sheet1!$B$2:$B$7,1)

    Series name in cell B1, X values in C2:C7, Y values in B2:B7. Awesome. Now format the Y axis so the minimum is 40450 (which means 9/29/2010), the maximum is 40451 (9/30/2010), and the major unit is one hour, which is 1/24, or 0.416666666666666. This yields the second chart below.



    Format the series so it uses no markers, but add error bars (upper chart below).

    Delete the vertical error bars, and format the horizontal error bars to show positive only with no end caps, using the Custom Values option, and click the Specify Value button to use the Duration values in E2:E7. Format the error bars as thick colored lines (I used 6 points). This is shown in the bottom chart below.



    You can hide Ambulance 0 and Ambulance 8 by using a custom number format of

    [>7]"";[>0]"Ambulance "0;;

    for the Y axis tick labels. If there are no Ambulances 3, 5, and 6, you need to do some more complicated manipulation of the data and axis formatting.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try Peltier Tech Charts for Excel

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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