How to Calculate P Value from Z Score in Excel (with Quick Steps)

Z score and P value are two of the most important concepts in statistical probability calculations. These terms are very helpful in determining distributions of data and outliers in a dataset. Of course, you can determine them manually from a dataset. But for a larger dataset, there are many tools at your command that can help you with the calculations faster. Excel is one of them. In this tutorial, we are going to see how to calculate the Z score from a dataset and then calculate the P value from the Z score in Excel.

What Is Z Score?

The Z score is the number of standard deviations from the population mean for a particular data point. To put it simply, It indicates how far a particular value is from the mean of the set with respect to the standard deviation. (Standard deviation is the RMS value of all the differences between points and mean.) The mathematical formula for the Z score is

Where,

Z = Z score

x = observed value

x̅ = mean value

σ = standard deviation

Although we are going to determine the standard deviation through function in Excel, the formula for the term is:

Where N is the total number of observations.

Read More: How to Calculate Z Score in Excel

What Is P Value?

P value is the number indicating the probability of a particular value in a set of values, assuming the null hypothesis is correct. A P value of 0.01 of a number indicates if there is a total of 100 observations as entries, the probability of finding the value in question is 1. Similarly, a particular value with the P value of 0.8 in the same observation indicates the probability of finding the value 80%.

The mathematical formula to calculate the P value from the Z score depends on the type of test the Z score is from. For a one-tailed test from the left, the P value is determined by the following formula.

If the one-tailed test is from the right, the formula is:

While the P value for a two-tailed test is as follows.

Here,

P is the P value of the particular observation.

S indicates the test statistics,

x is the value of observation,

Pr(condition|H₀) is the probability of the event condition assuming H₀is true.

min{x,y} denotes the smaller number between x and y.

Thankfully, Excel helps us to ignore all of those equations with the help of a single function while calculating the P value from a Z score.

How to Calculate P Value from Z Score in Excel: with Quick Steps

First of all, let’s consider a set of observations. This is a dataset with those observations.

It is a list of credit scores of seven different people. We are first going to calculate the Z score of the sample by determining the mean and standard variation of all the numbers. And then we are going to find the P value of each of the observations. Follow these step-by-step guides for the whole process.

Step 1: Calculate Mean of Dataset

First of all, we need to find the mean of the dataset. This will be helpful in both determining the standard deviation and the Z score. We can easily determine the mean of the observations with the help of the AVERAGE function. This function takes in a series of arguments or a range of values and returns their mean.

To determine the mean of our dataset, follow these steps.

First of all select cell C13.
Then insert the following formula into the cell.

=AVERAGE(C5:C11)

After that, press Enter on your keyboard. Thus you will have the mean of all the data.

Step 2: Estimate Standard Deviation

To calculate the standard deviation of the sample we are going to use the STDEV.P function. This function returns the standard deviation from a series of numbers or a range of values it takes as arguments.

To determine the standard deviation, select cell C14.
Then write down the following formula in the cell.

=STDEV.P(C5:C11)

After that, press Enter on your keyboard. Consequently, you will have the standard deviation of the dataset.

Step 3: Evaluate Z Score

To calculate the Z score of the values, we first need the difference of the value from the mean and then divide it by the standard deviation according to the formula. Follow these steps for a detailed guide.

First, insert a column for the Z score.

Then select cell D5.
Now write down the following formula in the cell.

=(C5-$C$13)/$C$14

After that, press Enter on your keyboard. Thus you will have the Z score for the first value in the dataset.

Next, select the cell again and click and drag the fill handle icon to fill out the rest of the cells in the column with the formula.

This way, you will have Z scores for all the entries of the dataset.

Step 4: Calculate P Value from Z Score

Finally, to calculate the P value from the Z score we have just determined, we are going to use the NORMSDIST function. Follow these steps to find the P value of each of the entries.

First, insert a column for the P values.

Then select cell E5.
Now write down the following formula in the cell.

=NORMSDIST(D5)

After that, press Enter on your keyboard. You will have the P value for the first entry.

Next, select the cell again. Finally, click and drag the fill handle icon to the end of the column to fill the rest of the cells with the formula.

As a result, you will have the P values for all the entries.

Download Practice Workbook

Download the workbook used for this example from the link below. It contains the dataset and the result in different sheets. Try yourself while you go through the step-by-step process.

P Value from Z score.xlsx

Conclusion

That concludes our discussion on how to calculate the Z value and then the P value from the Z score in Excel. Hope you will be able to determine both the Z scores and P values for your series of data. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.

<< Go Back to Z Score in Excel | Statistical Significance in Excel | Excel for Statistics | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!