As Seen on TechCrunch: Finofo Raises $1.65M CAD!

Excel Guide

How to Calculate Z Score in Excel

Z score is a statistical measurement that tells you how many standard deviations a data point is from the mean.

Using the Z-Score Formula in Excel

The z-score formula is:

z = (x−mean) / standard deviation

where:

  • z is the z-score
  • x is the raw score
  • mean is the mean of the population
  • standard deviation is the standard deviation of the population

Finding the Mean

You can find the mean by using the AVERAGE function in Excel.

  1. Type =AVERAGE( into a cell.
  2. Select the cells containing the data you want to include in the calculation. For example, if your data is in cells A1 to A5, type =AVERAGE(A1:A5).
  3. Press Enter, and the mean will be calculated.

Finding the Standard Deviation

You can find the standard deviation by using the STDEV function in Excel.

  1. Type =STDEV( into a cell.
  2. Select the cells containing the data you want to include in the calculation. For example, if your data is in cells A1 to A5, type =STDEV(A1:A5).
  3. Press Enter, and the standard deviation will be calculated.

Calculating the Z-Score

Once you have the mean and standard deviation, you can use the z-score formula to calculate the z-score for each data point.

  1. Type = (x-mean)/standard deviation into a cell, replacing �x with the cell number for the data point you want to calculate. For example, if the data point you want to calculate is in cell A1, the mean is in cell B1, and the standard deviation is in cell C1, the formula would be = (A1-B1)/C1.
  2. Press Enter, and the z-score will be calculated.

Interpreting the Z-Score

Once you have calculated the z-score, you can use it to interpret how far away from the mean a data point is.

  • A z-score of 0 means that the data point is exactly the same as the mean.
  • A z-score of 1 means that the data point is one standard deviation away from the mean.
  • A z-score of -1 means that the data point is one standard deviation below the mean.
  • A z-score of 2 means that the data point is two standard deviations away from the mean.
  • A z-score of -2 means that the data point is two standard deviations below the mean.

You can also use the z-score to find the percentage of data points that are above or below a certain data point.

  • To find the percentage of data points above a certain data point, use =NORMSDIST(z).
  • To find the percentage of data points below a certain data point, use =1-NORMSDIST(z).