Combining Charts

[SunnyDay7]

I am trying to create a chart that shows the 4 quartiles of a pay band, and where individual salaries fall within that payband. So the payband chart is easy enough using a stacked bar chart. Now what I have been unable to do, is plot the individual salaries on the chart. When I tried, using a scatter chart, the salaries were plotted on a secondary axis, and therefore did not appear in the pay bad quartiles. This seems like it should be simple enough to do, but hours (actually days) later and I think it must be impossible.

 

[Jon Peltier]

If you delete the secondary Y axis, all data will default to using the primary axis.

 

[SunnyDay7]

I was so hopeful to try that today, unfortunately, it didn't work. The axis goes away, but the data points don't shift to the primary axis. Thanks for the idea, though.

 

[Jon Peltier]

Guess I need to see the chart.

 

[SunnyDay7]

Thanks, Jon. Here's a link https://www.dropbox.com/s/57hp56h1r7004ra/Salary Band Chart.xls

 

[Jon Peltier]

Yeah, I envisioned a vertically stacked chart (column chart). Horizontal bar charts follow different rules. You need to plot the actual salary points as the X values of the XY series, and use 0.5 as all the Y values, then format the secondary X axis to mimic the primary X axis, and the secondary Y axis so it runs 0 to 1. Then hide or delete the secondary Y axis.

 

[Jon Peltier]

Actually, starting from scratch, I made my stacked horizontal bar chart, copied the XY data, pasted it into the chart (paste-special) with categories in first row, converted to XY, and it worked fine on the bar chart's horizontal axis.

I think sometimes if you mess around too much with a chart that has funny axis and chart type combinations, things get hinky and you can't fix the chart.

I saved the workbook back to Dropbox.

 

[SunnyDay7]

FANTASTIC! Exactly what I was going for. Thanks so much for your help ... I think you're right about messing around with it too much and then nothing seems to work.