[ Murdoch University logo and link to homepage ]
SEARCH| MURDOCH| INDEX| PEOPLE
School of Chemical and Mathematical Sciences

 

 

Excel Procedures

Mean
Standard Deviation
Median
Maximum, Minimum and Range
Quartiles and Interquartile Range
Quantiles and Percentiles
Boxplot
Histogram


Excel Procedures to Calculate the Sample Mean

Excel 2007

  • Formulas > Function Library: Insert Function > Statistical > AVERAGE or Formulas > Function Library: More Functions > Statistical > AVERAGE - will insert a formula to calculate the mean of an array into a cell. The dialogue boxes are as for Excel 95 to 2003.
  • Data > Analysis: Data Analysis > Descriptive Statistics - will provide the sample mean and much more. The dialog boxes are as for Excel 95 to 2003.

Excel 95 to 2003


Excel Procedures to Calculate the Sample Standard Deviation

Excel 2007

  • Formulas > Function Library: Insert Function > Statistical > STDEV or Formulas > Function Library: More Functions > Statistical > STDEV - will insert a formula to calculate the sample standard deviation of an array into a cell. The dialogue boxes are as for Excel 95 to 2003.
  • Data > Analysis: Data Analysis > Descriptive Statistics - will provide the sample standard deviation and much more. The dialog boxes are as for Excel 95 to 2003.

Excel 95 to 2003


Excel Procedures to Calculate the Sample Median

Excel 2007

  • Formulas > Function Library: Insert Function > Statistical > MEDIAN or Formulas > Function Library: More Functions > Statistical > MEDIAN - will insert a formula to calculate the sample standard deviation of an array into a cell. The dialogue boxes are as for Excel 95 to 2003.
  • Data > Analysis: Data Analysis > Descriptive Statistics - will provide the median and much more. The dialog boxes are as for Excel 95 to 2003.

Excel 95 to 2003



Excel Procedures to Calculate the Sample Minimum, Maximum and Range

Excel 2007

  • Formulas > Function Library: Insert Function > Statistical > MIN or Formulas > Function Library: More Functions > Statistical > MIN - will insert a formula to calculate the minimum of an array into a cell. The dialogue boxes are as for Excel 95 to 2003.
  • Formulas > Function Library: Insert Function > Statistical > MAX or Formulas > Function Library: More Functions > Statistical > MAX - will insert a formula to calculate the maximum of an array into a cell. The dialogue boxes are as for Excel 95 to 2003.
  • Range is calculated by subtraction: = MAX(insert array) - MIN(insert array). Details are as for Excel 95 to 2003.
  • Data > Analysis: Data Analysis > Descriptive Statistics - will provide the minimum, maximum, range and much more. The dialog boxes are as for Excel 95 to 2003.

Excel 95 to 2003



Excel Procedures to Calculate the Sample Quartiles and Interquartile Range

Excel 2007

  • Formulas > Function Library: Insert Function > Statistical > QUARTILE or Formulas > Function Library: More Functions > Statistical > QUARTILE - will insert a formula to calculate either the lower quartile, upper quartile, median, minimum or maximum of an array into a cell. The dialogue boxes are as for Excel 95 to 2003.
    The Interquartile Range is calculated by subtraction: = QUARTILE(insert array,3) - QUARTILE(insert array,1). Details are as for Excel 95 to 2003.
  • Formulas > Function Library: Insert Function > Statistical > PERCENTILE or Formulas > Function Library: More Functions > Statistical > PERCENTILE - will insert a formula to calculate any percentile (including the 25% and 75% point) of an array into a cell. Dialogue boxes are as for Excel 95 to 2003
  • Formulas > Function Library: Insert Function > Statistical > SMALL or Formulas > Function Library: More Functions > Statistical > SMALL - Construct a formula to calculate the quartiles by the method of your choice. SMALL(insert array , insert integer k) is the kth smallest value in the array. Details are as for Excel 95 to 2007

Excel 95 to 2003



Excel Procedures to Calculate Sample Quantiles and Percentiles

Excel 2007

  • Formulas > Function Library: Insert Function > Statistical > PERCENTILE or Formulas > Function Library: More Functions > Statistical > PERCENTILE - will insert a formula to calculate any percentile (including the 25% and 75% point) of an array into a cell. Dialogue boxes are as for Excel 95 to 2003
  • Formulas > Function Library: Insert Function > Statistical > SMALL or Formulas > Function Library: More Functions > Statistical > SMALL - Construct a formula to calculate the quartiles by the method of your choice. SMALL(insert array , insert integer k) is the kth smallest value in the array. Details are as for Excel 95 to 2007

Excel 95 to 2003



Excel Macro to Draw Boxplots

This macro draws a simple boxplot for a single column of data or a parallel or side-by-side boxplot for multiple columns of data. A box will be included in the plot for every column of data you select. Including column headings in the selection is optional. If headings exist in the line above your data they will be included on the plot whether or not they are selected. The selected area must be continuous (i.e. no control-click selections allowed) and hence rectangular. Empty cells in the selection will simply be ignored.

Excel 2007

  • Copy the file BoxPlotMacro.xls to your disk. When you first open the file you may be receive a warning that macros have been disabled. Enable the macro before proceeding.
  • Enter your data into one or more columns in a worksheet of BoxPlotMacro.xls and run the macro. If your data is already in another Excel file, you can run the boxplot macro from that file provided BoxPlotMacro.xls is open with macros enabled.

Excel 95 to 2003

  • Copy the file BoxPlotMacro.xls to your disk. When you first open the file you may be asked whether you want to enable the macros. Do so.
  • Enter your data into a worksheet of BoxPlotMacro.xls and run the macro. If your data is already in another Excel file, you can run the macro from that file provided BoxPlotMacro.xls is open with macros enabled.



Excel Procedures to Draw Histograms

Excel 2007

  • Data > Analysis: Data Analysis > Histogram (dialogue boxes are as for Excel 95 to 2003)
Excel 95 to 2003 - will produce something resembling a histogram.

Note: It is not essential to specify your own bin endpoints, but it is highly advisable. If you leave the "Bin Range:" input box blank, Excel will choose the bin endpoints itself. Enough said. As Excel does not really understand the concept of areas representing frequencies, when setting up your bin endpoints it is essential that you make all the bin widths the same. It is also critical that there are no sample values to the left of the first bin endpoint or to the right of the last. (You can sort the values in your column into ascending order via Data > Sort or calculate the minimum and maximum values of your data.) Thus there should be no sample values in the first bin or in the last bin (which Excel creates beyond your last endpoint and labels "MORE") 1. These empty bins can be deleted from the histogram after the event, by deleting the appropriate cells from the table of frequencies that Excel creates.

The graph can be resized by clicking it and dragging a corner. You can delete various annotations from the graph by clicking the offending object and deleting. To remove the gap between the rectangles right click one of the histogram's boxes, click "Format Data Series..." and select "Series Options" (just "Options" pre Excel 2007) and set the "Gap width" to 0.

On the horizontal axis, Excel labels the midpoint of each box with the endpoint of the interval. There appears to be nothing you can do to avert this. After the event you can change the labels in the table of frequencies that Excel creates to the midpoints, rather the endpoints. Excel will not recalculate the frequencies when you change these labels. To complete the illusion you can move the tick mark to the midpoint by right clicking the horizontal axis, selecting "Format Axis..." and then setting "Position Axis:" to "On tick marks" (Pre Excel 2007 users need to deselect "Value (Y) axis crosses between categories" on the "Scale" tab of "Format Axis"). If you desperately want to label the endpoints of the bins with the actual endpoint value then change the format of the cells containing the axis labels in Excel's table of frequencies to text. You can then insert leading blanks before each endpoint. This will shift the labels to the right. The latter approach can be combined with a change in the diagonal orientation of the axis labels to 1o (which translates to -1 Degrees pre Excel 2007). This is achieved by right clicking the axis, clicking "Format Axis..." and then "Alignment". This will also shift the labels to the right. Alternatively, you can completely remove the labelling and redo it in some graphics editing package. The problems arise because Excel thinks it is drawing a column chart with a categorical scale on the x-axis. Categorical scales only know about labels and have no concept of order, width or relative position.

1 The necessity for this step arises because the first and last bins that Excel creates effectively have infinite width and thus do not have the same width as all the other bins in the plot. Excel will not compensate for the unequal widths when drawing the histogram, and so phantom data lumps could appear at the left and right hand ends of your plot unless you chose the first and last endpoint wisely.
© 2009 Murdoch University | Copyright & Disclaimer | CRICOS Code: 00125J | Page Information