How to setup stacked bar chart to graph medication doses?

Question

My Mom is sick (cancer) and my Dad has been writing down the times that he has administered pain medications to her. He asked me to plug it into a spreadsheet and chart it so he can visualize trends and changes. Ideally he’d like to see a stacked bar chart for each day that would show the count of doses and the relative time they were given.

It’s difficult to explain, but let me try this way: He wants to see how many times he gave her medication X and how long between doses it was to gauge her “breakthrough pain” (when the pain is very bad and she needs a lot of medication). I thought a stacked bar chart would be good for this as it can show the relative time between doses and also show the dose count with its overall height.

But… I have never used them and I’m fumbling pretty bad with it. Here is a sketch of what I’m basically after:

So my dad could log the meds sequentially. Then the data would need to be grouped by drug and partitioned by date.

Maybe this isn’t even possible? I don’t know, I’m terrible with charting in excel and any advanced excel stuff, really. If the "clustered" requirement (drugs grouped by day) is a problem I can use separate charts for each drug.

Here is a link to the the data from my sketch in a Google Sheet.

I don’t usually like to be spoon-fed solutions, but in this case I’m really not sure how to proceed and she has a doctor’s appointment tonight that I want to have this for. If anyone can help me it really will be greatly appreciated by me and my whole family, actually.

Answer 1

I would suggest a different tack altogether (similar to what Raystafarian suggested) and use an XY Chart.

You can easily create a series for each med, with the X Axis being date/time (since time is continuous, using individual columns doesn't provide a full picture between the columns). The Y Value can be the same (arbitrary) number for each series, while the markers provide the indication of med administration.

Also, since you're trying to track the relationship between pain and the meds, I would add a series for pain and use that for the Y Axis at each med administration (and perhaps a regular interval in-between).

So here's what it could look like (based upon some fictional pain data):

Here's another example with date/time axis and periods of sleep (which will likely affect pain and when meds are administered) if you track it.

Answer 2

This is as close as I could get to your description and drawing.

I used a Pivot Chart in Excel 2007 to achieve this with your original data.

In Row Labels, insert Date then Rx Type. Under Column Labels insert Minutes. Under Values, insert Numbers as a Sum.

To get the number of minutes in each section of the bar, go under the Layout ribbon, and choose Data Labels->Center.

I removed the left-hand labels (right-click on it and Delete), so yours might looks slightly different.

Here's a screenshot of the criteria:

Answer 3

You're looking for a way to plot the doses that will help to visualize trends and changes. I couldn't do anything useful with a stacked bar chart, but I can suggest a different way to plot it:

I put both medicines on one chart but I think it would be better to put each in it's own chart.

This is just a scatter chart with time of day as the X axis, dose number as the Y axis, and each day's data as a seperate series. Changes in patterns stand out pretty well. Trends, not so much.

You can improve that by going to a 3D chart. I've got a very old version of Excel with limited 3D options, so this was the closest I could come to the idea:

This is a 3D line chart with dose number as category and date as the series (I left the second medicine off this chart). This lets you see both patterns and trends (comparing line to line).

Answer 4

All right, this was the best I could come up with using a pivot chart.

First, I custom sorted the data by date first then Rx Type. Then, I added a column to your data in column F to count the doses and number them -

Now, I went in and created a pivot chart with these settings -

Note the values are the "minimum" of Dose No.

The table at the bottom is in pivot chart tools - layout - data table.

Basically you're getting the total count of each Rx at each dose per day at the time it was given.