Combo of min/max and floating bar

[MCHolloman]

Hi there - I am trying to plot investment performance vs. the quartile ranges
of competitor performance. Not sure how to get a chart that looks as
follows... appreciate any help!

xxxxxxx <-- this is top quartile max (max of entire data set)
x x
x x
x x
xxxxxxx <-- this is top quartile min for period (need multi periods, 1
bar/pd)
x x
x x
x F x <-- this is example of where the FUND would plot (via marker
over bar)
x x
x x
xxxxxxx <--- this is 2nd quartile min (also 3rd quartile max)
x x
x 3 x
x x
xxxxxxx <-- this is 3rd quartile min (4th quartile max)
x x
x x
x B x <-- this is example of where benchmark would plot (marker over
bar)
x x
x x
x x
x x
xxxxxxx <-- this is min of data set (sometimes above/below 0, thus
floating)

I'm not sure how to orgaize the data, nor what chart type to select to make
this work. Sample data currently looks as follows:

Maximum: 18.5
Top Quart: 14.0
Median: 8.0
Bottom Quart: 5.0
Minimum: -1.0

Fund: 16.0
Benchmark: 13.5

There should be 4 stacked variable height bars sizes to match range of each
quartile, with two plot markers for the fund/benchmark.

I have tried doing a stacked bar for all 6 series, then converting the
fund/benchmark to line markers. That works, except that I am getting 5 bars
for the quartiles, due to 5 defining values. How do I do floating ranges?

Thanks in advance for your help... I used to think of myself as an Excel guru!

 

[Jon Peltier]

The problem occurs when some of the bars have to span the Y=0 axis. You need
to incorporate additional columns which show the portion above and below the
axis, as described in this tutorial:

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

I have a box and whisker utility that makes charts similar to what you tried
to sketch in ascii art:

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

- Jon