Home >> Science & Nature >> Science

Detecting Outliers Using Z-Scores In Excel

Outliers are data points that seem grossly inconsistent with the rest of your data. Sometimes they can offer valuable insight. The data that provided the first evidence of the ozone hole, for example, was initially disregarded as outlier data. Consequently, it can sometimes be challenging to decide whether you should keep outlier data or discard it and set it down to experimental error. In situations like these, scientists often make use of a statistical test that determines whether a data point qualifies as an outlier based on the Z-score.

Instructions

- 1
  Enter your data into Excel. The format you use will depend on the kind of data you have. The most common example is single-variable data from a given population -- the length of time individuals in a group of runners took to cross the finish line, for example. In a case like this, you would enter the times in a single column with a label in the cell at the top.
- 2
  Select the cell just below the bottom of the data in the column and type =MEDIAN(
  Now click on the lowest cell containing data; a selection box with the "marching ants" symbol will appear around this cell. Hold down on the mouse and drag the top of this selection box upward until it contains all of the data in the column, then hit enter.
- 3
  Go down one cell further and type =AVEDEV(
  Again, select all the data in the column just as you did before and hit "Enter."
- 4
  Find the data point you suspect may be an outlier. Note the row number and column letter for this cell. If a cell is in column G row 6, for example, it would be designated as G6.
- 5
  Type the = sign followed by a pair of parentheses () in another cell on the chart. Inside the parentheses, type the value of the data point you think is an outlier followed by a - sign and the value of the median Excel calculated for you.
- 6
  Type a / sign after the parentheses and then type the AVEDEV value Excel calculated for you. Hit "Enter." Excel will calculate a Z-score.
- 7
  Read the Z-score. If it is greater than three, you can treat the data point as an outlier.

Science