LIS 504 - Excel statistical functions

Microsoft Excel provides a number of functions for common statistical operations, including the following:
AVERAGE arithmetic mean
CHITEST probability from the chi-square test, given observed and expected values
CONFIDENCE confidence interval for a population mean, given confidence level, standard deviation, and sample size
CORREL correlation coefficient, given two lists of values
COUNT number of items with numeric values
FREQUENCY frequency distribution from a set of values
MAX largest numeric value in a set
MEDIAN median
MIN smallest numeric value in a set
MODE mode
PERCENTILE kth percentile, given k and a set of values
QUARTILE kth quartile, given k and a set of values
RANK rank of a value in a set
STDEV estimate of population standard deviation from sample set
STDEVP standard deviation
TTEST probability from t-test, given two data sets, number of tails (1 or 2), and type (paired, or two-sample equal or unequal variance)
VAR estimate of population variance from sample set
VARP variance

To access any of these functions except FREQUENCY, do the following.

  1. Select an empty cell.
  2. Click on the "Edit Formula" ("=") button to the left of the data entry field.
  3. Type in the function name, followed by a right parenthesis ("(").
  4. Fill out the form that appears for the function.
    (To specify a set of values in an Excel formula, indicate the first and last cell in the range separated by a colon; e.g., a1:a10.)
  5. Click on the "OK" button on the form.

The procedure for FREQUENCY is a bit more complicated:

  1. Enter an increasing series of values (e.g, 10, 20, 30, etc.) in one column.
    These are referred to as bins. Generally, make the highest value in the series equal to or greater than the maximum value in the set of values for which you want a distribution.
  2. Select the same number of empty cells in another column.
  3. Click on the "Edit Formula" ("=") button to the left of the data entry field.
  4. Type in frequency(.
  5. Fill out the form that appears for the function, putting the range for the values for which you want a distribution in the "Data_array" box and the range for the bins cells in the "Bins_array" box.
  6. Click on the "OK" button on the form.
  7. Click on the formula in the formula bar.
  8. While holding down both the Shift and the Ctrl keys, press the Enter key.

Note on copying text from formulas

When you copy text from a formula in Excel, whether with Ctrl-C, with Ctrl-Ins, or with "Edit|Copy", immediately press the Esc key before doing anything else; otherwise, Excel will not copy to the clipboard and may in addition corrupt the formula from which you wanted to copy.
Home

Last updated November 1, 2000.
This page maintained by Prof. Tim Craven
E-mail (text/plain only): t.craven@uwo.ca
Faculty of Information and Media Studies
University of Western Ontario,
London, Ontario
Canada, N6A 5B7