Combination Chart Help

[CzechCzar]

Hi all

My manager has asked me to make a pretty complex chart. Apologies for the crappy handwriting, but the drawing is necessary.

I work in HR, and we are dealing with mapping employee's salaries to market salary surveys. My manager wants a floating bar representing the 25th %ile, 50th %ile, 75th %ile, and average, of the survey position. I need the same for internal position to which the survey position matches - for these I want the maximum, mean, median, and minimum, again displayed as a floating bar. (Internal employees are called incumbents.)

Survey data is not given on an individual basis, but only aggregated in 25th %ile, 50th %ile, 75th %ile, and average. Internal incumbent data is individual, obviously, and I calculate these statistics myself.

Further down the rabbit hole: next to these floating bars, I need to display the individual employee (incumbent) data, in scatterplot form. And, I need to be able to separate the data, by gender, by age, etc.

I have no idea how to get this to work properly in Excel. Is this even possible?

[MrShorty]

For someone who has "no idea how to get this to work properly", I would probably first suggest this tutorial by Jon Peltier: http://peltiertech.com/WordPress/exc...ams-box-plots/ In this, he combines a floating column (and/or floating bar, depending on the desired orientation of the "boxes". Your picture suggests you are wanting a floating column rather than floating bar) and a scatter plot. If you understand how he puts this box and whisker diagram together, we will be a lot closer to getting your specific chart assembled.

[ben_hensel]

As a rule we tell people to post spreadsheets instead of pictures, but "I'm not sure how to build it, here is a cartoon of the chart I want" is probably the single biggest exception to that: they were just scribbles, but they were helpful.

Whether it's stacked columns or a box-and-whisker, I think the first chart is relatively straightforward. Probably it will need an intermediate "chart-building" table that processes the raw, but nothing special.

What I'm wondering about is this scatterplot. What's the x/y? Are those supposed to be "drag-and-drop", or do you want to filter, or what?

Well, I just played around and slapped something together, see attached.

[CzechCzar]

Thanks a lot guys. I will read over the article about whisker plots this weekend. As for the scatter plot details:

Realistically, I am going to separate out individual employees based upon other rows of data in my master data set. So, along with pay, I know other data that are useful to separate and analyze (like what state they live in for example). I am going to pivot off of those additional data.

For someone who has "no idea how to get this to work properly", I would probably first suggest this tutorial by Jon Peltier: http://peltiertech.com/WordPress/exc...ams-box-plots/ In this, he combines a floating column (and/or floating bar, depending on the desired orientation of the "boxes". Your picture suggests you are wanting a floating column rather than floating bar) and a scatter plot. If you understand how he puts this box and whisker diagram together, we will be a lot closer to getting your specific chart assembled.

As a rule we tell people to post spreadsheets instead of pictures, but "I'm not sure how to build it, here is a cartoon of the chart I want" is probably the single biggest exception to that: they were just scribbles, but they were helpful.

Whether it's stacked columns or a box-and-whisker, I think the first chart is relatively straightforward. Probably it will need an intermediate "chart-building" table that processes the raw, but nothing special.

What I'm wondering about is this scatterplot. What's the x/y? Are those supposed to be "drag-and-drop", or do you want to filter, or what?

Well, I just played around and slapped something together, see attached.