Convert Error Bars to Series and Colour

[Spiggy Topes]

Tricky one, I think.

I have a chart showing several series, using multiple overlaid areas and a couple of lines. One of the lines currently has error bars, but I want the user to be able to extract the values for those error bars using a VBA macro, and the DOM for Excel does not include access to error bar values. All other series just fine, error bars, no.

So I'm thinking the only way to make this data available without specifically including a data sheet - it's a package of charts and it's big enough already - would be to turn the two error bars into two series and shade the intervening space, at 50% transparency so you can still see what's behind.

Trouble is, I want to colour JUST the area between the series, and lay it on top of everything else. Tried just colouring the lower series at 100% transparency, didn't expect it would work, and it didn't.

Any geniuses out there got a great solution to this? Or another way to handle?

Just to elaborate, the plots are:

Over the all time maximum (background colour)
Between all time and ten year max
Above 75th percentile (upper error bar)
Mean
Below 25th percentile (lower error bar)
Between all time and ten year minumim
Below ten year minimum

So four coloured zones, plus the mean and two error bars on top.

 

[Spiggy Topes]

Tricky one, I think.



I have a chart showing several series, using multiple overlaid areas and a couple of lines. One of the lines currently has error bars, but I want the user to be able to extract the values for those error bars using a VBA macro, and the DOM for Excel does not include access to error bar values. Allother series just fine, error bars, no.



So I'm thinking the only way to make this data available without specifically including a data sheet - it's a package of charts and it's big enough already - would be to turn the two error bars into two series and shade theintervening space, at 50% transparency so you can still see what's behind.



Trouble is, I want to colour JUST the area between the series, and lay iton top of everything else. Tried just colouring the lower series at 100% transparency, didn't expect it would work, and it didn't.



Any geniuses out there got a great solution to this? Or another way to handle?



Just to elaborate, the plots are:



Over the all time maximum (background colour)

Between all time and ten year max

Above 75th percentile (upper error bar)

Mean

Below 25th percentile (lower error bar)

Between all time and ten year minumim

Below ten year minimum



So four coloured zones, plus the mean and two error bars on top.

Guess that's a "no", huh? Well, I can't figure a way to get there. So I'll just add the two values as invisible series and that will have to suffice.

Sad to see a group die like this...