How to Calculate a Z Score Using Microsoft Excel.
Contents
A Z-Score is a statistical value that tells you how many standard deviations a particular value is from the mean of the entire data set. You can use the AVERAGE and STDEVS or STDEVP formulas to calculate the mean and standard deviation of your data, and then use those results to determine the Z-score for each value.
What is a Z-Score and what do the AVERAGE, STDEV.S and STDEV.P functions do?
A Z-Score is a simple way to compare values from two different data sets. It is defined as the number of standard deviations from the mean that a data point lies. The general formula looks like this:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Here’s an example to help clarify. Suppose you want to compare the test scores of two algebra students taught by different teachers. You know that the first student got a 95% on the final exam in one class and the student in the other class got an 87%.
At first glance, the 95% mark is more impressive, but what if the teacher of the second class gave a more difficult exam? You can calculate the Z score for each student’s score based on the mean scores for each class and the standard deviation of the scores for each class. Comparing the Z-scores of the two students might reveal that the student with the 87% score did better compared to the rest of his class than the student with the 98% score compared to the rest of his class.
The first statistical value you need is the “mean” and Excel’s “AVERAGE” function calculates that value. It simply adds up all the values in a range of cells and divides that sum by the number of cells that contain numeric values (ignores blank cells).
The other statistical value we need is the ‘standard deviation’ and Excel has two different functions to calculate the standard deviation in slightly different ways.
Older versions of Excel only had the “STDEV” function, which calculates the standard deviation while treating the data as a “sample” from a population. Excel 2010 split it into two functions that calculate the standard deviation:
STDEV.S: This function is identical to the previous function “STDEV”. Calculates the standard deviation while treating the data as a “sample” from a population. A sample of a population might be something like particular mosquitoes collected for a research project or cars that were set aside and used for crash-safety testing.
PSTDEV: This function calculates the standard deviation while treating the data as the entire population. An entire population would be something like all the mosquitoes on Earth or every car in a production run of a specific model.
What you choose is based on your data set. The difference will usually be small, but the result of the “STDEV.P” function will always be less than the result of the “STDEV.S” function for the same data set. It is a more conservative approach to assume that there is more variability in the data.
let’s see an example
For our example, we have two columns (“Values” and “Z Score”) and three “auxiliary” cells to store the results of the “AVERAGE”, “STDEV.S” and “STDEV.P” functions. The “Values” column contains ten random numbers centered around 500, and the “Z-Score” column is where we will calculate the Z-Score using the results stored in the “auxiliary” cells.
First, we will calculate the mean of the values using the “AVERAGE” function. Select the cell where you will store the result of the “AVERAGE” function.
Type the following formula and press enter -or- use the “Formulas” menu.
=AVERAGE(E2:E13)
To access the function via the “Formulas” menu, select the “More Functions” dropdown menu, select the “Statistics” option, and then click “AVERAGE”.
In the Function Arguments window, select all cells in the “Values” column as input for the “Number1” field. You don’t need to worry about the “Number2” field.
Now press “OK”.
Next, we need to calculate the standard deviation of the values using the “STDEV.S” or “STDEV.P” function. In this example, we will show you how to calculate both values, starting with “STDEV.S”. Select the cell where the result will be stored.
To calculate the standard deviation using the “STDEV.S” function, enter this formula and press Enter (or access it through the “Formulas” menu).
=STDEV.S(E3:E12)
To access the function via the “Formulas” menu, select the “More Functions” dropdown menu, select the “Statistics” option, scroll down a bit, and then click the “STDEV.S” command.
In the Function Arguments window, select all cells in the “Values” column as input for the “Number1” field. You also don’t have to worry about the “Number2” field here.
Now press “OK”.
Next, we will calculate the standard deviation using the function “STDEV.P”. Select the cell where the result will be stored.
To calculate the standard deviation using the “STDEV.P” function, enter this formula and press Enter (or access it through the “Formulas” menu).
= PDEV(E3:E12)
To access the function via the “Formulas” menu, select the “More Functions” dropdown menu, select the “Statistics” option, scroll down a bit, and then click on the “PSTDEV” formula.
In the Function Arguments window, select all cells in the “Values” column as input for the “Number1” field. Again, you won’t have to worry about the “Number2” field.
Now press “OK”.
Now that we have calculated the mean and standard deviation of our data, we have everything we need to calculate the Z-Score. We can use a simple formula that references the cells that contain the results of the “AVERAGE” and “DEV.S” or “DEV.P” functions.
Select the first cell of the “Z-Score” column. We’ll use the result of the “STDEV.S” function for this example, but you could also use the result of “STDEV.P”.
Type the following formula and press Enter:
=(E3-$G$3)/$H$3
Alternatively, you can use the following steps to enter the formula instead of typing:
Click cell F3 and type = (
Select cell E3. (You can press the left arrow key once or use the mouse)
Write the minus sign –
Select cell G3 and then press F4 to add the characters “$” to make an “absolute” reference to the cell (it will loop through “G3” > “$G$3″>”G$3” > “$G3″> » G3 ”If you continue to press F4)
Guy )/
Select cell H3 (or I3 if you are using “STDEV.P”) and press F4 to add the two “$” characters.
press enter
The Z-Score has been calculated for the first value. It is 0.15945 standard deviations below the mean. To check the results, you can multiply the standard deviation by this result (6.271629 * -0.15945) and check that the result equals the difference between the value and the mean (499-500). Both results are the same, so the value makes sense.
Let’s calculate the Z scores of the rest of the values. Highlight the entire ‘Z-Score’ column starting with the cell that contains the formula.
Press Ctrl + D, which copies the formula in the top cell down through all the other selected cells.
Now the formula has been ‘filled’ in all cells, and each will always refer to the correct cells “AVERAGE” and “DEVS.S” or “DEV.P” because of the “$” characters. If you get errors, go back and make sure the “$” characters are included in the formula you entered.
Calculate Z-Score without using ‘helper’ cells
Auxiliary cells store a result, such as those that store the results of the “AVERAGE”, “STDEV.S”, and “STDEV.P” functions. They can be useful but not always necessary. Instead, you can skip them entirely when calculating a Z-score using the following generalized formulas.
Here’s one that uses the “STDEV.S” function:
=(Value-AVERAGE(Values))/STDEV.S(Values)
And one that uses the “STEV.P” function:
=(Value-AVERAGE(Values))/STDEV.P(Values)
When entering cell ranges for the “Values” in functions, make sure to add absolute references (“$” using F4) so that when you ‘complete’ you’re not calculating the average or standard deviation of a different range of cells in each formula .
If you have a large data set, it may be more efficient to use helper cells because it does not calculate the result of the “AVERAGE” and “DEV.S” or “DEV.P” functions each time, saving processor resources and speeding up the time needed to calculate the results.
Also, “$G$3” needs fewer bytes to store and less RAM to load than “AVERAGE($E$3:$E$12)”. This is important because the standard 32-bit version of Excel is limited to 2 GB of RAM (the 64-bit version has no limitation on the amount of RAM that can be used).
