GIL .. BIOSCI/Bionet News .. Biosequences .. Software .. FTP

# Error bars in Excel?

Gilles Bronchti gilles.bronchti at ia.unil.ch
Wed Dec 15 17:06:01 EST 1993

```In article <CI2xr8.Itu at cen.ex.ac.uk> PBarnwel at cen.ex.ac.uk writes:
>From: PBarnwel at cen.ex.ac.uk
>Subject: Re: Error bars in Excel?
>Date: Wed, 15 Dec 1993 14:04:18 GMT

Drawing error bars in Excel is certainly not obvious, but it is quite easy to
do when you understand how the program works. I'll try to explain how I am
drawing real error bars (not hi-lo-close) in my graphics, using the capacities
of the soft:
First you have to introduce in different columns your data: x-values,
y-values. Then calculate the sd values, or SEM or whatever you want the error
bar to represent. Add to your data 2 columns, one with mean+sd (or SEM) that
will be call Uppermean hereafter, the second with mean-sd (or SEM) or
Lowermean hereafter. For the example I am dealing with one y-value columns,
but it works well with any number of y's you want.Be sure to enter the data so
the x-values column will be next to all y-values columns and then followed by
Uppermean and Lowermean. Something like:
x values  y1   y2   y3   y1upper,  y1 lower, y2 upper, y2 lower etc...
then select all the columns for creating the graph. Create the graph as say a
bar graph (if you prefer a curve, it works as well). You will get all your
data points in bars a result which certainly does not correspond to your
wishes, but be patient! Then select (I don't remember now in which menu) the
graph overlay, something like create overlay... Then go to the menu for graph
formating, then you will have 2 graphs to choose, the main graph and the
overlay graph. Choose the main graph, choose then how you want it to look like
(bar-graph, curve, scatter etc...), and in the same window you will have the
possibility to choose which series belongs to the overlay graph (in the bottom
of the configuring graph window), now, count that in our example above (with 3
y's) you have 3 series pertaining to the main graph and that you want to be in
bar graphs or curve, the rest, 6 series (y1uppermean, y1lowermean,
y2uppermean, y2 lowermean etc...) will be moved to the overlay graph so the
answer to the question in that windows, which series starts the overlay graph
will be, in this case, series 4). Then go to the formating of the overlay
graph. Choose it to be line. You have also there an option to click on to ask
the program to connect the lower and upper values.  Then you will get a bar
graph (to follow my first example) with lines passing through them, still not
error bars, but close to them. Then click on the say upperline. Choose it to
appear without lines (just points) and that the points will be of the form of
the large "-" proposed by excel (there is a small one and a large one, choose
the large!). Do the same with the lower line. Now you have a bar graph, or a
curve with error-bars fitted on it.
My explanations might be complicate, but once you learn how to do that, you
will see that it works easily, and faster than me writing all these. I am not
good in macro writing, but I am sure that all this procedure may be done by
macro, providing you give the proper number of the series you want to start
the overlay graph. If something is not clear, feel free to contact me by
e-mail, I will go to excel and look at the exact commands, name of menu etc...
Gilles Bronchti

```