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 which can help you with the calculations faster. Excel is one of them. In this tutorial, we are going to see how to calculate Z score from a dataset and then calculate the P value from the Z score in Excel.


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.


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

how to calculate p value from z score in excel

Where,

Z = Z score

x = observed value

= 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.


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 entry, 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 Z scroe 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.

how to calculate p value from z score in excel

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

how to calculate p value from z score in excel

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

how to calculate p value from z score in excel

Here,

P is the P value of the particular observation.

S indicates the test statistics,

x is the value of observation,

Pr(condition|H0) is the probability of the event condition assuming H0 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.


Step-by-Step Procedure to Calculate P Value from Z Score in Excel

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.

Read More: How to Calculate Critical Z Score in Excel (3 Suitable Examples)


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)

how to calculate p value from z score in excel

  • 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.

how to calculate p value from z score in excel

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

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

how to calculate p value from z score in excel

  • 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 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.

how to calculate p value from z score in excel

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

=NORMSDIST(D5)

how to calculate p value from z score in excel

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

how to calculate p value from z score in excel

  • 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.

how to calculate p value from z score in excel

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


Conclusion

That concludes our discussion on how to calculate the Z value and then 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.

For more guides like this, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo