Hi Mikey,
Two points here:
- For an x-y scatter chart, both parts of the data pair (time, including date; glucose reading) must be in non-header cells.
- When you enter a time, the date on which it is entered is automatically included.
With these points in mind, try formatting the cells in column B (time) as Date and Time, with both parts showing. Here's an illustration with two shots of the same table. In the first, all columns have been left at 'Automatic'. In the second, columns A and C have been set to Date & Time, with the date set to display as shown, and the time set to use a 24 hour time format. Note that the dates in columns A and C are a day later than the same entries in your table. This is because I entered only the time part and did that today (Dec 12).
Automatic:
A & C formatted as described:
Before producing the chart(s), I changed the formatting in column A to match the 'all-numeral' version in column D.
Here's the resulting table, and the chart(s) produced. Details below.
The chart is actually two charts, one superimposed on the other.
The first chart was made by selecting Cells A2—B9, then choosing x-y scatter from the charts button menu. The only edits to the default chart were to change "no connections" to "curve" and to change the value label orientation on the x axis to "vertical left".
For the second chart, I selected C2—D5 and again chose x-y scatter from the charts button menu.
For this chart, I left the connections pop-up menu at No connections, selected one of the data points and changed the shape and colour of the data points to green diamonds, set the maximum Y axis value to 20 (to match the first chart), hid the x and y axis labels by setting them to white and 0% opacity, set the y value grid lines to none, and hid the legend (as it would be in the same location as the 'glucose' legend when the charts were superimposed).
Then I used the mouse and the alignment guides to drag the second chart and align the two charts' x axes. With the second chart selected, I went to the Arrange menu and moved it one step toward the back, then used the left arrow (and the shift key) to move it leftward and behind the first.
Note that the first insulin dose is shown much earlier on the chart than in the data, as I neglected to account for the difference in start times of the two sets of data.
The result is as shown above.
It is also possible (and a lot less trouble) to put both series of data onto the same chart, but there is one difficulty: If lines or curves are used to connect the data points, a gap in the data with result in a gap in the line or the curve.
In this case, that won't be much of a problem if the insulin doses are taken (or recorded as being taken) at the same time as one of the glucoses measurements. In the first day's data, this is true for one of the three doses, but not true for the other two. I've treated the first and last dose records differently to demonstrate the effect of each treatment on the chart. The first has been recorded as taking place at the same time as the closest glucose level check; the last gets a separate entry, which causes a gap in the data list for glucose, and a corresponding gap in the curve between the last two data points for glucose.
Finally, here's an image of the first chart(s) with an adjustment to the width of the insulin chart to correct the time placement error mentioned above.
Regards,
Barry
