Microsoft Excel includes a large number of statistical formulas, including one that can calculate a z-score (standard score) directly. You may also calculate z-scores with a formula.
Video of the Day
Enter the data values for which you wish to calculate standard z-scores in a single column. There is no need to sort them or arrange the values in any particular way. Provide headings for the other elements that will be needed in calculating the z-score.
Use the Average function to calculate the mean of the data values. In the cell set aside for the mean value, enter the function as =AVERAGE(range). Select -- highlight -- the values to fill in the range values.
You may also access this function in an additional two ways:
- by clicking the function button on the formula bar and selecting the Statistical category on the pop-up box and then Average from its list, or
- by choosing the Formulas tab on the Excel Ribbon and clicking the More Functions button, selecting Statistics, and then Average from the popup list.
In the cell set aside for the standard deviation value, enter the function as =STDEV(range). Again, select the cells to be included in the calculation.
There are actually four functions available for calculating the standard deviation, which you use depends on the data. The STDEV() function is used for sample sets with no logical (true/false) or text in the data range. The STDEVA() function is another sample set calculation, but includes all text and logical values in its range. The STDEVP() calculates the standard deviation for an entire population. Like the STDEV(), it omits logical or text values. The STDEVPA, like the STDEVA() includes logical and text values in calculating the standard deviation for a population.
In Excel 2007 and later, use the STANDARIZE() function to calculate the standard z-score for a value using the mean and standard deviation of the population/sample of the value.
Calculate a z-score using a formula. The formula for calculating a z-score is z = (x - μ) / σ, where x is the value, μ is the mean, and σ is the standard deviation.