Variation of a Gantt chart

T

travelug

New Member
Joined
Sep 29, 2010
Messages
1
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: 
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!

<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:Author>dispatch</o:Author>
<o:LastAuthor>dispatch</o:LastAuthor>
<o:Revision>1</o:Revision>
<o:TotalTime>2</o:TotalTime>
<o:Created>2010-09-29T12:48:00Z</o:Created>
<o:LastSaved>2010-09-29T12:51:00Z</o:LastSaved>
<o:Pages>1</o:Pages>
<o:Words>236</o:Words>
<o:Characters>1349</o:Characters>
<o:Company>SVEMS</o:Company>
<o:Lines>11</o:Lines>
<o:Paragraphs>3</o:Paragraphs>
<o:CharactersWithSpaces>1582</o:CharactersWithSpaces>
<o:Version>11.9999</o:Version>
</o:DocumentProperties>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:WordDocument>
<w:SpellingState>Clean</w:SpellingState>
<w:GrammarState>Clean</w:GrammarState>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
</w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" LatentStyleCount="156">
</w:LatentStyles>
</xml><![endif]-->
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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.
 
Upvote 0
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.

AmbulanceDispatch1.png


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.

AmbulanceDispatch23.png


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.

AmbulanceDispatch45.png


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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

2
  • Question Question
Need to display comment data in new text box
Replies
4
Views
655
2mutch
2
2
  • Question Question
Two ByVal Target As Range choices on same sheet
Replies
2
Views
525
2mutch
2
B
  • Question Question
Formatting Palette inaccessible
Replies
0
Views
627
bob40
B
2
  • Question Question
Find all rows that fall within date range
Replies
8
Views
1K
2mutch
2
A
  • Question Question
Macro for Sorting
Replies
1
Views
422
alexoigres
A

Forum statistics

Threads
1,223,879
Messages
6,175,148
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top