Custom error bars not working

[nedwards]

Microsoft Excel 2002 SP3.
I have data that looks like this:
Median Min Max
Na 0.032617 0.000106 0.387142
Cl 0.0265 0.000017 0.282087
Ca 0.000524 0.0000047 0.0059883
K 0.005523 0.000007 0.038365
Mg 0.0000823 0.0000001 0.0018515
PO4 0.00031 0.00002 0.00105
HCO3 0.001655 0.000262 0.003048
I'm doing a line plot with the first column as the categorical x axis,
and the second column, median, on the y-axis, which is logarithmic. I
then set up error bars using the values in the min and max column
(columns 3 and 4 above) as custom values in the "error amount" part of
the format data series dialog. The max values would be for the plus
side of the error bar, and the min values are for the minus side.

I thought this would work perfectly, however, only the plus side of the
error bar is showing up, not the minus side. And in the format data
series dialog I DO have it set to show both.

So, I was wondering if anyone had run into this before, and if there is
a reason and a solution. I want the minus part of the error bars to
show.

Thank you,
Nicole

 

[Jon Peltier]

You need to tell Excel the proper values to use for the error bars. For
negative error, use (mean - min), and for positive error, use (max - mean).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[NTE]

Jon,
thank you ever so much for your reply. I tried it and it
works..........

I now have points with error bars representing the max and mins. Looks
just like what we wanted!

I have a second question, and I've spent most of the day on your web
page and this group trying to figure out if there is an answer (if you
prefer a sepearate posting, let me know).

The data represented above is from a natural source. We also want to
plot data for a man made source--so for each category (e.g. Na) there
is going to be another median, min, max. We want this to appear BESIDE
the natural data, not in line with it (and hence overlapping). I've
read in this group about offsetting a series, but there seems to be no
easy way to do it. I'm guessing with the categorical x axis that this
will be hard to accomplish?

Many thanks again for the solution.
Nicole

 

[Andy Pope]

Hi,

Now Jon has sorted you error bar problem I will try with the offset one

Add a secondary data series and change the chart type to xy scatter.
Try the following values for the X-values
1.2
2.2
3.2
4.2
5.2
6.2
7.2

You may find turning off the Log scale while you do this makes it clearer.

Cheers
Andy