Excel Procedures to Calculate the Sample Mean

(Excel 95 or later))

• Insert: Function: Statistical: AVERAGE - will insert a formula to calculate the mean of an array into a cell
• Tools: Data Analysis: Descriptive Statistics - will provide the sample mean and much more.

Excel Procedures to Calculate the Sample Standard Deviation

(Excel 95 or later))

• Insert: Function: Statistical: STDEV - will insert a formula to calculate the mean of an array into a cell
• Tools: Data Analysis: Descriptive Statistics - will provide the sample standard deviation amongst other things.

Excel Procedures to Calculate the Sample Median

(Excel 95 or later))

• Insert: Function: Statistical: MEDIAN - will insert a formula to calculate the median of an array into a cell
• Tools: Data Analysis: Descriptive Statistics - will provide a litany of the usual suspects, including the median.

Excel Procedures to Calculate the Sample Minimum, Maximum and Range

(Excel 95 or later)

• Insert: Function: Statistical: MIN - will insert a formula to calculate the minimum of an array into a cell
  Insert: Function: Statistical: MAX - will insert a formula to calculate the maximum of an array into a cell
  Range is calculated by subtraction: = MAX(insert array) - MIN(insert array)
• Tools: Data Analysis: Descriptive Statistics - will provide a litany of the usual suspects, including the minimum, maximum and range.

Excel Procedures to Calculate the Sample Quartiles and Interquartile Range

(Excel 95 or later)

• Insert: Function: Statistical: QUARTILE - will insert a formula to calculate either the lower quartile, upper quartile, median, minimum or maximum of an array into a cell
  Interquartile Range is calculated by subtraction: = QUARTILE(insert array,3) - QUARTILE(insert array,1)
• Insert: Function: Statistical: PERCENTILE - will insert a formula to calculate any percentile (including the 25% and 75% point) of an array into a cell
• Insert: Function: Statistical: SMALL - Construct a formula to calculate the quartiles by the method of your choice. SMALL(insert array , insert integer k) is the k^th smallest value in the array.

Excel Procedures to Calculate Sample Quantiles and Percentiles

(Excel 95 or later)

• Insert: Function: Statistical: PERCENTILE - will insert a formula to calculate any percentile of an array into a cell
• Insert: Function: Statistical: SMALL - Construct a formula to calculate quantiles (percentiles) by the method of your choice. SMALL(insert array , insert integer k) is the k^th smallest value in the array.

Excel Macro to Draw Boxplots

(For Excel 95 or later)

Copy the file BoxPlotMacro.xls to your disk. It will open a blank worksheet where you can enter data and run the macro. If your data is in another worksheet, you must also have BoxPlotMacro.xls open to run the macro.

Excel Procedures to Draw Histograms

(Excel 95 or later)

• Tools: Data Analysis: Histogram - will produce something resembling a histogram.

Note: It is not essential to specify your own bin ranges, but it is highly advisable. If you leave the "Bin Range:" input box blank, Excel will choose the bin ranges itself. Enough said. When setting up your bin ranges, make all the bin widths the same. Make sure 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). 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"). These 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 double click one of the rectangles, select "Options" in the dialogue box which appears and set the "Gap width" to 0. Excel labels the midpoint of each rectangle with the endpoint of the interval. There appears to be nothing you can do about this. You can change the diagonal orientation of the labelling by double clicking the axis, but it still remains very confusing. The best idea would be to completely remove the labelling and redo it in some graphics editing package. The problems arise because Excel thinks it is drawing a bar chart.