How to chart wait times over days

Occasional Contributor

I want to make a chart out of this type of data to show the us and downs of wait times for our center, I cannot figure it out, I've been advised to use plot charts, but the don't plot, it's just a straight line, I cannot get date on the correct axis and the wait time won't go over 2 hours.excel.JPG. I am frustrated!
Lise Quinn

14 Replies

Like this - the minutes waiting up to two hours on the vertical, the dates on the horizontal!

 

chart.jpg

@Lise Quinn 

I don't see a connection between the data in your sheet and the drawing of the chart...

On the vertical axis or date column in excel, you will see dates of 7/1, 7/8, 7/15 in the next column you will see wait times for "in processing" service which lists 25:20 minutes and seconds, for 7/1. 1:07:21 one hour, seven minutes and 21 seconds for 7/8 and then on 7/15 the wait time for the same service, in-processing was 28:26 minutes and seconds.
I then have the wait times for the next service, "Out-processing" and so on.
The last two columns, total served and total turned away won't be included in this chart.
I suspect you inserted a "2-d line" chart and you need to use the "scatter plot" option. highlight the data in columns A and B and select scatter plot and it should work.

@Lise Quinn 

I created a Line and a Stacked Line based off the drawing provided.

 

Please see attached:

@Lise Quinn 

In the attached workbook, I have created 5 possible charts based on your sample data. Perhaps there is something you can use.

I@Patrick2788 

I could just use yours but I don't understand, When I select and choose line it uses the line numbers

example_chart.JPG

@Lise Quinn  as I noted above, did you select SCATTER plot option??

mtarler_0-1665177180655.png

in older versions of excel I think it was called x-y plots

 

@Lise Quinn 

Excel is stupid. It uses the Date column as a data series. You have to click on the Chart, then click Select Data. Specify the date values as the X-values and remove the Date series from the chart.

@Hans Vogelaar I added the rest of the info to that charts xls you sent, and it won't let me expand the data to include the new data, it says 
Capture_data_range.JPG

All I am doing is changing the range in the right hand pane from A4 to A5
I've attached the xlsx file

@Lise Quinn 

There's a few ways to update a chart's data source. You can select the chart and then click-and-drag the blue box around the data to expand the range:

Patrick2788_0-1665185250922.png

 

I tried both dragging the blue box which wouldn't, but it would slide up and down with 4 selections and I tried in the properties but I get the error to complex.

@Lise Quinn 

Don't worry about the message "The data range is too complex". Close that dialog.

To expand the range, click o a single series in the chart.

You will see something like this (it depends on the selected series, of course):

S1844.png

You see the "formula" for the series in the formula bar.

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

It has four arguments:

  • The name of the series.
  • The x-values, currently A2:A4.
  • The y-values, currently B2:B4.
  • The order of the series (is it the 1st, 2nd. etc.)

You can expand the series by editing this formula. You want to extend the range to row 13, for example. So change it to

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

You will see the effect in the data source:

S1845.png

and in the chart:

S1846.png

Repeat for each of the other series.

It is also possible to create a dynamic chart, for example by changing the data source to a table.

@Hans Vogelaar I ultimately converted to minutes to a number rather than a time type of item. Once I did that I was able to make a line chart, thank you for all your input and helping me think this through. I got it done in time for my presentation and my CIO was very happy