This tutorial explains how to calculate Z Score and probability using SPSS and Excel.
You may also want to read
Calculate probability of a range using Z Score
Calculate Z Score and probability using SPSS and Excel
In statistical inference, we are interested to know whether a small sample comes from a population. To inference using sample mean, when the population standard deviation and population mean are known, we can use Z test to interference the population mean from sample mean.
In reality, we do not have data of the whole population. Without the population standard deviation, we use T Test (also known as Student’s t Test) to interference the population mean from sample mean.
Use Excel to calculate Z Score
There are two ways for Excel to calculate Z Score. Assume that we have a population data range named data_rng.
Method 1: Use AVERAGE and STDEV.P Functions
If we calculate Z Score manually, we use the below formula
Z score = (X-μ)/σ
= (target value - population mean) / population standard deviation
Follow the Z score formula with the help of Average Function to calculate mean and use STEDEV.P to calculate the population standard deviation. If you want to calculate sample standard deviation, use STEDEV.S
For example, type the below formula to find Z score for X = 10
=(10-AVERAGE(data_rng))/STDEV.P(data_rng)
Method 2: Use Standardize Function
Syntax of Standardize Function
STANDARDIZE(x, mean, standard_dev)
This Function is a bit cleaner than Method 1, but we still need the help of AVERAGE and STDEV.P Functions.
For X = 10
=STANDARDIZE(10,AVERAGE(data_rng),STDEV.P(data_rng))
Use Excel to calculate Z Score and probability
How Excel calculates probability is different from the Standard Normal Table. Standard Normal Table finds the probability from 0 to Z, while Excel calculates from infinity to Z.
Therefore, if you are trying to get the same result as Standard Normal Table does, subtract 0.5 by the Excel result and then apply absolute value.
For example, for Z score = 2.41, probability = 0.492 according to the Standard Normal Table. In Excel, the probability is 0.07869, which is approximately equal to 0.5-0.492.
| Data | Z Score | P value (Excel) | 0.5-P value | =ABS(0.5- P value) |
| 1 | -1.4142 | 0.0786 | 0.4214 | 0.4214 |
| 2 | -0.7071 | 0.2398 | 0.2602 | 0.2602 |
| 3 | 0.0000 | 0.5000 | 0.0000 | 0.0000 |
| 4 | 0.7071 | 0.7602 | -0.2602 | 0.2602 |
| 5 | 1.4142 | 0.9214 | -0.4214 | 0.4214 |
Method 1: Use NORM.S.DIST to convert Z score to probability
Syntax of Norm.S.DIST Function
NORM.S.DIST(z,cumulative)
| Z | Required. The value for which you want the distribution. |
| Cumulative | Required. Cumulative is a logical value that determines the form of the function. If cumulative is TRUE, NORMS.DIST returns the cumulative distribution function; if FALSE, it returns the probability mass function. |
This method directly converts Z Score to probability. Assume that we have already found Z Score, which is 1
=NORM.S.DIST(1,TRUE)
Assume that we haven’t found the Z score for X=10, then
=NORM.S.DIST(STANDARDIZE(10,AVERAGE(data_rng),STDEV.P(data_rng)),TRUE)
Method 2: Use NORM.DIST to calculate probability directly
Syntax of Norm.DIST Function
NORM.DIST(x,mean,standard_dev,cumulative)
| X | Required. The value for which you want the distribution. |
| Mean | Required. The arithmetic mean of the distribution. |
| Standard_dev | Required. The standard deviation of the distribution. |
| Cumulative | Required. A logical value that determines the form of the function. If cumulative is TRUE, NORM.DIST returns the cumulative distribution function; if FALSE, it returns the probability mass function. |
This method assumes that we still have not found the Z score yet. Assume that X=10
=NORM.S.DIST(STANDARDIZE(10,AVERAGE(data_rng),STDEV.P(data_rng)),TRUE)
Use SPSS to calculate Z Score and probability
Input the value 1 to 5 in SPSS Data View.
Navigate to Analyze > Descriptive Statistics > Descriptives
Move the variable to the right and check the box “Save standardized values as variables”
Close the Statistics Viewer (because we don’t need this) and go back to Data View.
Now we see a new column is created, which is the Z value for each data.
Lets compare SPSS result with Excel result, we can see that SPSS is using sample standard deviation to calculate Z Score (SPSS always uses sample instead of population).
After calculating the Z Score in SPSS, you can use Excel or refer to Standard Normal Table to find the probability.
Excel vs SPSS – calculate Z Score and probability
- SPSS fails to calculate Z Score for variable value not in the data set, while Excel can calculate any value
- SPSS can only calculate Z Score using sample standard deviation but not population standard deviation, while Excel can do both
- SPSS fails to convert Z Score to probability, while Excel can do
- SPSS fails to directly calculate Z Score from data set, while Excel can do