Scatter Graph with Times

[attaknow]

I work for a fire department and I am trying to create graph which shows response times to a city. The data that I have is the date, response time in seconds and response time in minutes. I would like the date at the bottom and five minute increments on the left with a continuous line showing the graphed response times throughout the month.

I have gotten the dates at the bottom. At the Axis options for the time on the left I have put the following:

Bounds: Minimum - 0.000694444444444444 (1 minute) Maximum 0.013888889 (which is 1 min * 20minutes)

It shows 11 minutes on the left. Not sure why. I'm sure I have figured this wrong some how.

Additionally, when I select my data of the actual minutes, nothing shows up or I get a flat line at the bottom with some points like it is trying to document the response times.

Any help is appreciated.

 

[thisoldman]

Please show four or five points of your data as it's set up in the worksheet.

This works for me:
[TABLE="class: grid"]
<tbody>[TR]
[TD="class: xl64"][/TD]
[TD="class: xl67"][mm]:ss[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan[/TD]
[TD="class: xl66, align: right"]01:09[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]02-Jan[/TD]
[TD="class: xl66, align: right"]04:13[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]03-Jan[/TD]
[TD="class: xl66, align: right"]10:31[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]04-Jan[/TD]
[TD="class: xl66, align: right"]10:56[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]05-Jan[/TD]
[TD="class: xl66, align: right"]08:44[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]06-Jan[/TD]
[TD="class: xl66, align: right"]12:32[/TD]
[/TR]
</tbody>[/TABLE]

The response times use a custom number format, [mm]:ss
I used a line chart.

The vertical axis is formatted with,
Axis Options
Bounds

Minimum: 0
Maximum: 0.013888889
​

Units

Major: 0.003472222
​

Number

Category: Custom
Type: [mm]:ss
​

 

[attaknow]

So below is what the other system provided in my download.

[TABLE="width: 353"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Response Time (Seconds)[/TD]
[TD]Response Time[/TD]
[/TR]
[TR]
[TD]1/4/2018[/TD]
[TD]572[/TD]
[TD]0:09:32[/TD]
[/TR]
[TR]
[TD]1/5/2018[/TD]
[TD]448[/TD]
[TD]0:07:28[/TD]
[/TR]
[TR]
[TD]1/7/2018[/TD]
[TD]399[/TD]
[TD]0:06:39[/TD]
[/TR]
[TR]
[TD]1/7/2018[/TD]
[TD]722[/TD]
[TD]0:12:02[/TD]
[/TR]
[TR]
[TD]1/11/2018[/TD]
[TD]657[/TD]
[TD]0:10:57[/TD]
[/TR]
[TR]
[TD]1/12/2018[/TD]
[TD]510[/TD]
[TD]0:08:30[/TD]
[/TR]
[TR]
[TD]1/14/2018[/TD]
[TD]589[/TD]
[TD]0:09:49[/TD]
[/TR]
[TR]
[TD]1/17/2018[/TD]
[TD]638[/TD]
[TD]0:10:38[/TD]
[/TR]
[TR]
[TD]1/18/2018[/TD]
[TD]591[/TD]
[TD]0:09:51[/TD]
[/TR]
[TR]
[TD]1/18/2018[/TD]
[TD]646[/TD]
[TD]0:10:46[/TD]
[/TR]
[TR]
[TD]1/26/2018[/TD]
[TD]401[/TD]
[TD]0:06:41[/TD]
[/TR]
[TR]
[TD]2/7/2018[/TD]
[TD]733[/TD]
[TD]0:12:13[/TD]
[/TR]
[TR]
[TD]2/7/2018[/TD]
[TD]561[/TD]
[TD]0:09:21[/TD]
[/TR]
[TR]
[TD]2/8/2018[/TD]
[TD]801[/TD]
[TD]0:13:21[/TD]
[/TR]
[TR]
[TD]2/25/2018[/TD]
[TD]474[/TD]
[TD]0:07:54[/TD]
[/TR]
[TR]
[TD]3/5/2018[/TD]
[TD]427[/TD]
[TD]0:07:07[/TD]
[/TR]
[TR]
[TD]3/10/2018[/TD]
[TD]405[/TD]
[TD]0:06:45[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

Please show four or five points of your data as it's set up in the worksheet.

This works for me:
[TABLE="class: grid"]
<tbody>[TR]
[TD="class: xl64"][/TD]
[TD="class: xl67"][mm]:ss
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]01-Jan
[/TD]
[TD="class: xl66, align: right"]01:09
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]02-Jan
[/TD]
[TD="class: xl66, align: right"]04:13
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]03-Jan
[/TD]
[TD="class: xl66, align: right"]10:31
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]04-Jan
[/TD]
[TD="class: xl66, align: right"]10:56
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]05-Jan
[/TD]
[TD="class: xl66, align: right"]08:44
[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]06-Jan
[/TD]
[TD="class: xl66, align: right"]12:32
[/TD]
[/TR]
</tbody>[/TABLE]

The response times use a custom number format, [mm]:ss
I used a line chart.

The vertical axis is formatted with,
Axis Options
Bounds

Minimum: 0
Maximum: 0.013888889
​

Units

Major: 0.003472222
​

Number

Category: Custom
Type: [mm]:ss
​

 

[attaknow]

I still can't get my data to show up.

 

[thisoldman]

Remove the header label "Date", make it an empty cell. Then select the first and third columns, including the empty cell, and insert your chart. That may help solve the difficulties. Also make sure the dates are actual dates, not text strings.

A scatter or line chart may not be the best choice, depending on the story you wish to illustrate. What's the connection between the date and thee response time? You also can have multiple calls per day which means points running on top of each other.

Here's a few variations:

Without knowing the goal of your chart, my choice of chart would be a bar chart with the data sorted by response times. Here are two examples:

You can download a copy of my workbook from Dropbox: https://www.dropbox.com/s/3gwiky28tbbswco/response_times.xlsx?dl=0
Explore the charts and perhaps you can discover where the problem lies.

 

[attaknow]

Couple of questions...I apologize but I am trying to learn from this.
1) Why when you select data does yours have check boxes?
2) How did you determine the bounds and units?

 

[attaknow]

Thanks for all your help on this!

Cheryl

 

[thisoldman]

Couple of questions...I apologize but I am trying to learn from this.
1) Why when you select data does yours have check boxes?
2) How did you determine the bounds and units?

I'll take the second question first—it's easier to answer. For dates and times I usually adjust the major unit first and see where Excel puts the minimum and maximum automatically. Only then do I adjust these last two settings.

For the response time axes, I knew the response times were all under 15 minutes. A reasonable major unit seemed to be 5.0 minutes. I typed 00:05:00 into a cell and Excel recognized the entry as a time. I changed the cell format to "General" and copied the number displayed into the text box for the axis' Major Units.

I may have set some of these response time axes limits to manual as I was playing around. They don't need to be set to manual. Excel sets the minimum to zero for these charts and selects its own maximum.

For the date axes on the scatter and line charts, one week is a good division so 7 was entered as the major unit. The minimums and maximums were then adjusted to where there would be an even division displayed.

On the column and bar charts, I set the response time major units. For the bar charts, after the response time axis looked good, I decided I didn't need the axis on the chart. With that axis selected, I pressed the Delete key. Excel let the grid lines remain displayed. I thought the grid lines would help people read the charts, make comparisons, so I did not remove them.


First question: check boxes? When you select data? I'm not quite sure what you mean: perhaps the Charting Tools icons?

For Excel 2013 and later, if I select a chart or any item within a chart, three icons appear to the right of the chart. The plus icon, when clicked, brings up a menu that allows me to quickly toggle on or off chart elements. If I hover on an item, a rightward pointing arrow (gray triangle) appears. If I click on that arrow, a flyout menu appears where I have a little bit more control over which items should appear on the chart.

The Paintbrush icon is where I can go to quickly choose one of the builtin styles or one of the preset colors. I don't like Excel's presets.

The filter icon, lets you fine tune the data to display in the chart. I forgot that this menu is available. I usually separate raw data from charted data: separate data blocks on the same worksheet or separate worksheet. The way I work, I have usually filtered the data before I insert a chart.


There are many good websites to learn about excel charts. When I first started trying to improve my charts, I visited Purna Duggirala's website, https://chandoo.org/, a lot. For help with specific problems, Jon Peltier's blog, https://peltiertech.com/, is great. Charley Kyd's blog, http://www.exceluser.com/index.htm, has also proved helpful. And finally, I find I visit Jorge Camões site often, https://excelcharts.com/.

 

[attaknow]

Thank you for all the information and your time! I'm getting there doing it on my own. Mind you I'm not there yet but I can't do it unless I do it myself. Thanks again!

 

[thisoldman]

I'm glad I helped. I feel the same way about doing things myself. If you do get stuck, this forum is a great resource.