Bubble Charts with two non-numerical variables

[andy62]

I cannot figure out how to get a bubble chart to do what I want. I have two
variables, Impact vs Cost. Each of the two variables is coded as "Low",
"Medium", or "High". The third variable, which would be the size of the
bublles in the bubble chart, is the number of items for each of the nine
cells in the resulting 3 X 3 grid. Here's the source data:

High 1 0 1
Impact Medium 1 2 1
Low 4 1 1
Low Medium High
Cost
I just can't figure out what to put into the data sources panel as the X,
Y, and Sizes variables in order to get a chart with bubbles and the right
labels. I only get numbers on the axis (1, 2, and 3) instead of High,
Medium, Low. I tried something like ={"High", "Medium", "Low"} but that
didn't work. Anyone know how to pull this off? Or that it is impossible and
I should stop trying? Thanks in advance.

 

[Jon Peltier]

Use 1, 2, 3 for low, medium, high in columns 1 and 2 for Impact and Cost. In
column 3 put the bubble sizes. This is your plot. You can train the users
that 1-2-3 mean low-medium-high, or you can add custom axis labels using
dummy series:

http://peltiertech.com/Excel/Charts/ArbitraryAxis.html

You can't add an XY series, but an added bubble series will do fine.

- Jon

 

[andy62]

Thanks, Jon. I had no idea it was so complicated. At least I wasn't just
being dense.

What I did instead was to hide the grid values (1,2,3) and instead make the
axis labels each be two rows, as follows:

Low Med High
Risk

And then lined up the label until it matched up with the major gridlines.
Maybe a bit unstable, but I think it will do.