Error bars won't display with named formula/range data

  • Thread starter Thread starter Rob Hick
  • Start date Start date
R

Rob Hick

Hi all,

I have a bit of a problem with Y error bars on a line chart. I'm
tying to add custom error bars using a named formula but no error bars
are displayed. If i use an array formula to display the values on a
worksheet and select this range as the custom error bar then all is
displayed fine. Also, if i use the named formula as a new series then
all plots ok.

The named formula is:
=IssueCluster!DGH_Comparison-IssueCluster!DGH_Current
It calculates the difference between the values in 2 cell ranges.

Is this happening because the named formula is not evaluating itself?
If so, is there any way to force it to?

Thanks for your help
Rob
 
Rob -

The entire concept of named ranges to hold formulas sounds great, in for
worksheet use, it works pretty well, too. But many times the nice
formula I've written gives the array I'm expecting if I dump it into the
worksheet, but doesn't work in a chart. You can use a dynamic named
range for error bars, too, I guess as long as it refers to a worksheet
range. But I'm not surprised it is failing in your case. The most
reliable thing to do is to put the calculations into named ranges in a
worksheet, and use the named ranges for series and error bar values.

- Jon
 
Jon

Thanks for the reply - it's nice to know i'm not going mad! The
reason i'm using named formulas is to make the chart dynamic. All the
series have worked nicely so far, it's just when i come to try and add
error bars. I think a solution might be to put the named formula into
an array formula on a worksheet as an excessively large range. I
could then use a named range to select only those without NA. The
only trouble is I'm creating the workbook for others and am trying to
hide most of it out of harms way.

thanks again
Rob
 
Back
Top