Charting date and time against multiple object values (Series)

[dlock]

I have three instruments that have their values recorded every 5 minutes
This produces the following sample data pulled from a SQL database:

Time Name Value
17/04/2018 00:01:02 Instrument_1 1.49
17/04/2018 00:01:25 Instrument_2 0.35
17/04/2018 00:01:53 Instrument_3 11.39
17/04/2018 00:06:02 Instrument_1 1.52
17/04/2018 00:06:25 Instrument_2 0.33
17/04/2018 00:07:01 Instrument_3 12.37
17/04/2018 00:11:02 Instrument_1 1.56
17/04/2018 00:11:25 Instrument_2 0.38
17/04/2018 00:11:53 Instrument_3 12.37
17/04/2018 00:16:02 Instrument_1 1.55
17/04/2018 00:16:25 Instrument_2 0.33
17/04/2018 00:16:54 Instrument_3 11.39
17/04/2018 00:21:02 Instrument_1 1.55
17/04/2018 00:21:25 Instrument_2 0.38
17/04/2018 00:21:54 Instrument_3 12.37
17/04/2018 00:26:02 Instrument_1 1.6
17/04/2018 00:26:25 Instrument_2 0.26
17/04/2018 00:26:54 Instrument_3 12.04
17/04/2018 00:31:02 Instrument_1 1.58
17/04/2018 00:31:25 Instrument_2 0.3
17/04/2018 00:31:54 Instrument_3 12.09


I want to chart this information , as a line graph, with date/time on the X axis , showing a separate line for each Instrument Name (Series), with the value as the Y axis.
When I try to create a simple line chart it defaults to creating a single line, with the x axis being date/time and instrument Name, and the y axis being the value

I know this must be easy but I cant figure it out.
I know I could do this if the data was in tabular form but since the date/time values are all different then this is not possible
I would also like to be able to chart for any number of instruments up to 5 going forward.


I tried it as a pivot table/chart but ran into issues there as I have to average the values instead of summing them , and then got blank spaces in the graph where there was a was a time difference in the minutes i.e.

Time Name Value
17/04/2018 00:01:02 Instrument_1 1.49
17/04/2018 00:01:25 Instrument_2 0.35
17/04/2018 00:01:53 Instrument_3 11.39
17/04/2018 00:06:02 Instrument_1 1.52
17/04/2018 00:06:25 Instrument_2 0.33
17/04/2018 00:07:01 Instrument_3 12.37

Anyone help me please?

Thanks in advance.
Doug

 

[thisoldman]

I will assume you have separated your data into columns as shown in columns A:D, below. Dates in the first column are Excel dates: numbers, not text. In columns F:I, set up the data for the chart.

Book1
	A	B	C	D	E	F	G	H	I
1		Time	Name	Value		Time	Instrument_1	Instrument_2	Instrument_3
2	17/04/18	00:01:02	Instrument_1	1.49		17-Apr 00:01	1.49	FALSE	FALSE
3	17/04/18	00:01:25	Instrument_2	0.35		17-Apr 00:01	FALSE	0.35	FALSE
4	17/04/18	00:01:53	Instrument_3	11.39		17-Apr 00:01	FALSE	FALSE	11.39
5	17/04/18	00:06:02	Instrument_1	1.52		17-Apr 00:06	1.52	FALSE	FALSE
6	17/04/18	00:06:25	Instrument_2	0.33		17-Apr 00:06	FALSE	0.33	FALSE
7	17/04/18	00:07:01	Instrument_3	12.37		17-Apr 00:07	FALSE	FALSE	12.37
Sheet1

Cell Formulas
Range	Formula
F2	=A2+B2
G2	=IF($C2=G$1, $D2)
H2	=IF($C2=H$1, $D2)
I2	=IF($C2=I$1, $D2)

You want to remove all the values with FALSE. Select a cell in the data to be charted and press Ctrl+*. The data cells in this section will be selected. Bring up the GoTo dialog by pressing Ctrl+g or F5. Click on the "Special..." button. Select "Formulas" and uncheck all the checkboxes except for "Logicals". Press "OK".

The cells containing FALSE will be selected. Press "Delete". The FALSE values will disappear.

A better format for the date+time chart labels would be nice for the chart. I like a short date on one line and the time on the second line. Select the time values and bring up the "Format Cells" dialog. Select "Custom" on the "Number" tab. In the "Type:" box, enter "dd-mmm", press Ctrl+j, then enter "hh:mm". (the quotes are not to be typed. The Ctrl+j puts a line break character in the custom format. It may look strange in the worksheet, but works in the chart.

Select the chart data in columns F:I and Insert a scatter chart, not a line chart. Choose "Scatter with Straight Lines and Markers". The chart may appear without connecting lines or as a sawtooth pattern.

To correct this, choose "Select Data" from the design tab of the ribbon, or the right-click contextmenu of the chart. Click the button labeled "Hidden and Empty Cells". Select "Connect data points with line" and OK your way out.

Finished:

 

[dlock]

wow! thanks for your detailed help!
My datetime field is actually just a single field but apart from that i'll give this go
Many thanks!

 

[dlock]

Thank you for your help, that's me got it all up and running.
Did a lot of it in VBA but I used your solution for turning column data into tabular and it worked a treat! thanks again

 

[Jon Peltier]

It would be much easier to use a pivot table to split out the instruments. Select the data and click the Pivot Table button on the Insert tab. In the Pivot Table Fields task pane, drag Time to the Rows area, Name to the Columns area, and Value to the Values area. Then make your chart from the pivot table, right click and choose Select Data, click the Hidden and Empty Cells button, and select the Connect Data Points with Line option for Empty Cells.

 

[Jon Peltier]

Oh yeah, you can only make a line chart from a pivot table, and you really want an XY Scatter chart.

So select a blank cell that's not touching the pivot table, and insert an XY Scatter chart (markers and straight lines). Right click on the blank chart, choose Select Data, and whatever you do, stay away from the Chart Data Range box at the top.

Instead click Add above the left hand list. Select the pivot table cell with the first instrument name for Name, select the time column for X Values, and select the first instrument's values for Y Values. Click OK, then repeat for instruments 2 and 3. With the Select Data dialog still open, click on Hidden and Empty Cells, and choose Connect Points with Lines for Empty Cells.

I set the X axis minimum to 43207.0, which is 17 April 2018 in General number format, and the X axis major unit to 0.00694444444444444, which is 10 minutes in General format (i.e., 10/60/24).