Content

• Steps

In this article, we will show you how to calculate Z-score in Excel.In statistics, this estimate characterizes the measure of the relative spread of values, that is, it shows the number of standard deviations relative to the mean. To calculate the Z-score, you need to know the mean (μ) and standard deviation (σ) for the dataset. Formula for calculating the Z-score: (x - μ) / σwhere "x" is a data point from the dataset.

Steps

1. 1 Open the data sheet in Excel. To start Excel, click the X-shaped icon with a green background. Now open the table with the dataset for which you want to calculate the Z-score; if necessary, enter the data into a blank Excel spreadsheet.
2. 2 Enter a formula to calculate the average. Do it in an empty cell. The average is calculated using the formula = AVERAGE (cell range), where instead of "range of cells" you must enter a range of cells with the required data.
   • For example, if the data is in cells A2 through A11, and you want to calculate the average in cell D2, in cell D2, enter = AVERAGE (A2: A11).
3. 3 Enter a formula to calculate the standard deviation. Do it in an empty cell. The standard deviation is calculated by the formula = STDEV (cell range)where instead of "range of cells" enter the range of cells with the desired data.
   • For example, if your data is in cells A2 through A11, and you want to calculate the standard deviation in cell D4, in cell D4, enter = STDEV (A2: A11).
   • In some versions of Excel, instead of = STDEV need to enter = STDEV or = STDEVPA.
4. 4 Calculate the Z-score for the data point. In an empty cell, which is next to the cell of the desired data point, enter the formula = (data point - $ mean) / $ standard deviation, where instead of “data point” substitute the address of the cell with the data point, and instead of “mean value” and “standard deviation” substitute the absolute addresses of the corresponding cells (the dollar sign before the letter and cell number means that the address will not change if the formula is inserted into other cells).
   • For example, to calculate the Z-score of the data that is in cell A2, select cell B2 and enter the formula = (A2- $ D $ 2) / $ D $ 4... Dollar symbols in front of the letter and cell number mean that the address will not change if the formula is inserted into other cells
5. 5 Apply the formula to other data in the table. When you calculate the Z-score for the first data point, apply the same formula to the other data by copying the formula into the appropriate cells. Click on the cell with the calculated Z-score, and then drag down the green square that appears in the lower-right corner of the cell. This will copy the formula to other cells that display the Z-scores for the corresponding data.
   • In our example, select cell B2 and drag the green square that is in the lower right corner of the cell to cell B11. The Z-score appears in cells B2 through B11 next to the corresponding data in column A.