Excel multi-line graph

[elkay]

I thought I was pretty good with Excel, but this has me stumped. I've never really bothered with graphs before, and now that I want one, I can't figure out how to get what I want.

My data is:
- Column A is date/time
- Column B is a type indicator
- Column C is a value

I simply (!) want to display the values relating to each type on a separate line on the chart.

A little background...I was recently diagnosed with diabetes, and have been capturing data re blood sugar levels before & after meals, and exercise effort as well. I would like to see the trends for the blood sugar levels, and be able to relate variances caused by exercise (and probably add food intake in some form at a later stage).

Any suggestions?

elkay

[servodude]

is column B a filter for types of column C?

if so I'd probably use a couple of other columns to break out the values you want in to two or more "time series"

so with a bit of pseudo code
- Column D would be

Code: Select all

=IF(Bx == "typeA", Cx, "")

- Column E would be

Code: Select all

=IF(Bx == "typeB", Cx, "")

You'd end up with sparse columns but they should print together correctly as scatter plots (there's an option somewhere to not print "empty" values)

There's also pivot tables if you're prepared to try that
https://exceljet.net/excel-pivot-tables

- sd

[masped]

I think you need to change the way that your data is organised to put it into a format that Excel can more easily plot on a graph, so instead of three columns of data you arrange it into a table organised by date/time and type:

Instead of having Column A as dates / times, make the different dates/times separate column headings
The different types will be labels for the different rows of the table
Place the values at the relevant place in the table

So it will look something like this (I'm using dashes just to get everything lined up):

---------Date/time1-----Date/time2-----Date/time3 ...
Type1------ValueA---------ValueB-----------ValueC
Type2------ValueD---------ValueE-----------ValueF
Type3------ValueG---------ValueH-----------ValueI


This is then easy to turn into a multi-line graph using the Excel chart wizard

[servodude]

Masped has done a far better job than I of explaining how your data might be arranged to help

I'll just add that it's normally quite simple to get Excel to do this for you (by using extra columns or sheets)
- which can be really useful if the data you have is automatically generated

good luck
- sd

[MikeyWorld]

oops submitted twice

[MikeyWorld]

When I was diagnosed, I set up a spreadsheet with the date across the top and each of the readings underneath.
Below that I gave details of each meal, any additional notes that may have affected the result, and a row for any changes in dosage so that it stood out.

I then plotted the data (top five rows) as a histogram overlaid with a 10 day moving average for each of the four readings.

Date
Breakfast [data]
Midday
Evening meal
Bed

Info:
Breakfast
Midday
Evening meal

Notes

Dosage


Within a month I was convinced that I wasn't diabetic and stopped taking the meds
Within four months I had convinced the NHS
Within fourteen months I had convinced the DVLA, so don't tell the DVLA unless you're certain you really are diabetic.

[elkay]

Thanks for your input folks,

Servodude/masped.

This was the the type of manipulation I was hoping to avoid - I expect so much from the tools available to us now that I expected there to be a solution with a minimal amount of intervention to the data that is captured from my app. I'm now resigned to a solution along the lines you have suggested.

MikeyWorld,

Unfortunately the diabetes diagnosis is cast iron; more than 50% of my pancreas is dead. So I am on gliclazide which improves the production of insulin, and enzymes to assist digestion of food.

My plan is along a similar idea to yours, however with more focus on exercise. I attended my first hospital appointment with a doctor specialising in diabetes on Thursday. She was impressed with the data I had already captured, but talking to her it became clear that my exercise pattern is significantly skewing the results. Also, an additional focus now is to identify the effect of my exercise pattern, because of the risk of hypoglycaemia. I probably also need to think about capturing some info on my food intake as well.

Again, thanks for your help folks. I may return with additional questions once I have my data restructured.

regards
elkay

[servodude]

Thanks for your input folks,

Servodude/masped.

This was the the type of manipulation I was hoping to avoid - I expect so much from the tools available to us now that I expected there to be a solution with a minimal amount of intervention to the data that is captured from my app. I'm now resigned to a solution along the lines you have suggested.

MikeyWorld,

Unfortunately the diabetes diagnosis is cast iron; more than 50% of my pancreas is dead. So I am on gliclazide which improves the production of insulin, and enzymes to assist digestion of food.

My plan is along a similar idea to yours, however with more focus on exercise. I attended my first hospital appointment with a doctor specialising in diabetes on Thursday. She was impressed with the data I had already captured, but talking to her it became clear that my exercise pattern is significantly skewing the results. Also, an additional focus now is to identify the effect of my exercise pattern, because of the risk of hypoglycaemia. I probably also need to think about capturing some info on my food intake as well.

Again, thanks for your help folks. I may return with additional questions once I have my data restructured.

regards
elkay

Try a pivot table
- they are THE tool for this kind of thing; but can be a bit unintuitive
- you might find it easier than you think

Good luck
-sd