I am having trouble getting Excel to create the type of graph I need but I am sure there is a really simple solution that neither I nor my colleagues can think of.
I have a set of data (roughly 100 observations)
For each item I have information about:
cost per health gain (a numeric value)
severity of disease (low, medium, high. Some are described as low to medium thus spanning two categories but in a pinch those could be forced to fit into L, M, H)
decision (Yes, Maybe, No)
I want to show graphically how cost and severity influence our decisions.
I have been trying to make a diagram with cost on the vertical axis and severity on the horizontal axis. But I want the decision to be visible as well, for example through different colours on the individual bars or points.
Can anyone help me accomplish that? Or perhaps come up with an even better way to show it?
I’m using Excel 2003 but I can switch to a different computer and use 2007 if that makes it easier.
This example uses random number generation to create data, so the data will change.
The strategy is to create a scatter graph, and use three different series, one each for Yes, No, and Maybe. Each series gets its own color. The trick is organizing the data so you can get three series out of this. The master data is in the first three columns, then there is another set of columns that filters out the data for each of the three series in the graph.
To plot a scatter graph, I had to assign numeric values for Low, Lo-to-Med, Med, and Hi. I added text boxes on the chart to show it, that is not automatic.
I had to also show data at 0 which can be ignored. I’m thinking about this some more to see if there is a more elegant solution.
Let me know if this is what you need or if you have further questions.
Have you tried a pivot table? I’d put cost in the vertical axis, severity in the horizontal axis, and average of decision in the body of the table, and graph from there. You’d need to assign numeric values to severity and decision, of course.
Pivot tables are amazing. Once you learn how to use them (and it’s pretty straightforward, really) you will find that they are useful in all kinds of different situations. Here’s a tutorial from MS, although IIRC it doesn’t really show you just how powerful this tool really is:
Thanks CookingWithGas that accomplishes what I was trying to achieve.
I did try to mess around with pivot tables and diagrams but didn’t succeed. Pivot diagrams for example wouldn’t allow me to choose scatter plot as the diagram type.