How to Calculate the Z score in Excel
The Z-score is a statistical value that shows how many standard deviations a specific value happens to be from the mean of a given set of data. You can opt to use STDEV.S or STDEV.P and AVERAGE formulas to calculate the standard deviation and the mean values of the assigned group of data and use the values to calculate the Z-score of every value.
In simple terms, the Z-score is a way of comparing values from two different sets of data. It is simply defined as the number of standard deviations away from mean a data point lies. When calculating the Z-score, the first statistical value you require is the mean. Excel’s AVERAGE function calculates the value. To get it, you simply need to add up the values in the cell range and then divide that summed number of cells containing numerical values.
Standard deviation is another statistical value you will need, and Excel offers two ways to calculate it.
The previous Excel versions only had the STDEV function, which will calculate the standard deviation while treating data as a sample of the population.
STDEV.S function is identical to the previous STDEV function. This function calculates your standard deviation while treating the data as a sample population.
STEDV.P function usually calculates your standard deviation while treating the data as an entire population.
Whichever method you use, the difference is usually small, but the results of the STDEV.P function is always smaller than the results of the STDEV.S function of the same set of data. It is a more conventional approach to assume there is more variability in the data.
- Open the data in your excel. Excel is an app that has an icon of a green sheet with an X in front of the table. You need to open your excel file with the dataset you intend to find the Z score for.
- Enter the mean formula in a blank cell. If you have your data points recorded in your excel spreadsheets, you can calculate your mean using the formula =AVERAGE (“Cell ranger”) in the blank cell, replacing the cell range with the section that contains data points.
- Ensure you enter the standard deviation formula in your blank cell. For your data points recorded in your excel spreadsheets, you can calculate the standard deviation using the formula = STDEV (cell range”) and replace the cell range with the same content for all the data cells.
- Calculate the Z score. In the blank cell next to the section of the data point, you need to calculate the Z score, ensure you enter the formula = (data point – mean)/ standard deviation and replace the data point with the cell of the data point and replace mean and standard deviation with the respective cell locations.
- Apply the formula to every data point in the table. After calculating the Z score of your first data point, you need to apply the exact formula to the rest of the list. You need to click on the cell with the Z score you have created and click and drag the green box in the bottom-right corner of the section at the bottom of the column. This still applies to the rest of the list, giving the Z score to every data point in the list.
Recommended: How to Group Worksheets in Excel
