High/Low chart with multiple series per day

[PennStan]

I'm trying to create a chart for my golf league that will show the high, low, and median scores for each or four flights for each golf day. There are four flights in the league and 10 golfers in each flight. So, for each date, I would like four vertical lines of different color showing high/low/median scores.

I tried using the stock chart (high/low/close), but that appears to only allow one "stock" (flight) for the day. Also looked at stacked chart, but can't make that work.

Appreciate any ideas. Attached is sample data and charts I have tried. Thanks.

[Bob Phillips]

How about this?

[PennStan]

Bob - thanks, but not really what I'm looking for. Would be quite long by the end of a 20-week season. I attached a revised sheet showing more what I would like. I suppose I could create the initial chart with first line for each date, then use a macro to add the remaining three lines for each date. Might be above my abilities, though.

[Bob Phillips]

I can't see how you are going to add extra series to a stock chart, but I see Jon Peltier has posted a method at http://peltiertech.com/WordPress/sto...in-excel-2007/ that I will leave you to investigate, but I don't see how that is going to be any shorter after 20 weeks than my simple column chart. You could reduce the gap width to slim it down.

[Andy Pope]

You can build the chart with a line chart and error bars using custom values. The fake date spacing/labelling can be done using columns on the secondary axis.

If you stagger your data to give position and colour to each of the 4 flights. The +/- error bar values.

[PennStan]

Sorry for the delayed response, been away.

Yes, Andy. That is what I'm looking for. I need to dissect what you did and understand it.

Related topic - I have also been trying to give each flight a date and time where the time would provide the separation on the chart between the bars for each date.

Thanks.

[Andy Pope]

You would need to use a xy-scatter instead of Line chart to handle position of flight based on time within day.

[PennStan]

OK, think I got it. Wasn't familiar with error bars, but they accomplish exactly what I wanted. To provide separation between bars, ended up adding one day to each date on data points 2, 3, and 4 for each base date. I think data will eventually get too jammed up putting the four bars on one date and separating using time. With proper formatting of x-axis, it will be invisible to viewer. Thanks, everyone, for the assistance.