error bars in Excel charts

George Grills grills at aecom.yu.edu
Wed Jun 17 11:29:47 EST 1998


Hi Jan,

Here are some directions on inserting error bars in Excel.  I compiled
these directions from a discussion string on bio-soft at net.bio.net.  

- George


INSERTING ERROR BARS IN EXCEL:

Once you select a data series, you can INSERT error bars and then give
customized error values. The data boxes for this will accept a data range.
For example, if you have your data in 3 columns - X values, Y values and Y
errors - you can use the third column to define the error bar extent for
the data series.

You must indicate the SHEET in which the values are located. For example,
if your SEM values are  in SHEET1, then enter in the custom box:

=sheet1!$c$1:c$4


Or, what I usually do is, after clicking the data series, and then
clicking on the Error Bars tab, I click in the custom box (leaving the
cursor there).  Then, using the mouse, I click in the worksheet containing
the error bar values, and I drag the mouse over those values to highlight
them.  As I drag the mouse over the cells containing the error values, the
range 'magically' appears in the custom box. When I have the range I want,
I return the mouse to the custom box and click OK. This gives me the error
bars on the graph.

As for StdDev and StdErr:

1)  Make a table with the X-values, averages and the standard deviations or
whatever you want to use for error bars. StdDev is easy as it is a built-in
function in Excel.  Std Err and 95% confidence intervals are a little more
complex as you must build the function yourself using the Variance, Count
(n) and the t-value corresponding to that n (n-1).

   Example table
		A	B              C
	1	Date      Average   StdDev   
	2	Jan-95   5.6	  1.2
  	3	Mar-95   6.3          1.5
	4	May-95  7.2          1.4
	5	Jul-95    6.5           1.6

2)  Make an XY, bar or line chart using A1:A5 as X and B1:B5 as Y.

3)  Double click the chart to make it active

4)  Either double click the line itself and go to Y error bars
      or choose Insert  -  Error bars  from the menu.

6)  Choose the Custom tick box and in both the fields available for that
      option, use the mouse to insert, C1:C5



At 12:15 PM 6/17/98 +0200, you wrote:
>Using MS EXCEL 7.0 I'm analyzing statistical data. Those data shall be
>displayed in a columnar diagram. Since weeks I'm looking for a
>possibility to add bars for the standart deviation and/or the standart
>error ( 'error bars' )to my columns. The problem is not calculating the
>standart deviation or the standart error, the problem is adding this
>information to my graphics.
>
>           _________________________________
>          | mailto:janni at zedat.fu-berlin.de |
>          | Jan Oliver Olivier  -----  1998 |
>           ---------------------------------
>

___________________________________________________________

 George Grills
 Director
 DNA Sequencing and Oligonucleotide Facilities
 Albert Einstein College of Medicine
 713 Ullmann Building           
 1300 Morris Park Avenue       
 Bronx, New York 10461-1602    
 
 Tel: (718) 430-2657
 Fax: (718) 430-8778
 E-mail: grills at aecom.yu.edu
 DNA Sequencing: http://leper1.ca.aecom.yu.edu/dnacore
 Oligonucleotide: http://sequence.aecom.yu.edu/oligo
___________________________________________________________






More information about the Bio-soft mailing list