Floating Bar with Quartile Ranks

[GH]

I'm looking to create a floating bar chart separated into four quartiles to
plot some statistical data. For example, I'd be plotting the 5th percentile,
25th percentile, median, 75th percentile, and 95th percentile to separate the
bar into 4 sections. I would then overlay another data point that fits into
one of those sections. Any suggestions on how to do that?

 

[Del Cotter]

I'm looking to create a floating bar chart separated into four quartiles to
plot some statistical data. For example, I'd be plotting the 5th percentile,
25th percentile, median, 75th percentile, and 95th percentile to separate the
bar into 4 sections. I would then overlay another data point that fits into
one of those sections. Any suggestions on how to do that?

Lay your data out like this:

5th 25th Median 75th 95th Other
A [5th] [25-5] [med-25] [75-med] [95-75] [other]
B [5th] [25-5] [med-25] [75-med] [95-75] [other]
C [5th] [25-5] [med-25] [75-med] [95-75] [other]

Create a stacked bar chart, format the first series ("5th") to have no
border and no area, so it's invisible. Then change the chart type of
"Other" to a Line chart and format it to have no line, only a symbol.

The key is to make each stacked rectangle be the *difference* between
two percentiles, so that stacking them gets you the total.

Use PERCENTILE() to find the statistics of your base data.

 

[Jon Peltier]

That's not too different from this:

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

- Jon