Soumik Dutta
Z-Score is a parameter using which we can calculate the value of probability. Excel contains some formulas by which we can easily estimate the value of the Z-Score. In this article, we are going to show the steps of how to calculate probability from Z-Score in Excel elaborately. If you are also curious to know about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
What Is Z-Score?
Z-Score is a special type of value that indicates how far the value is from the mean. The general formula for Z-score is:
Here,
- Z represents the value of the Z-score
- X is the value of any case
- µ stands for the mean value
- σ represents the value of the Standard Deviation
What Is Probability?
Probability represents the possibility of occurring any events with respect to the total number of events. The mathematical expression of probability is:
Step-by-Step Procedure to Calculate Probability from Z-Score in Excel
To demonstrate the process, we consider a dataset of 10 students of a school. The name of the students in column B and their examination marks in column C. The procedure to calculate the value of probability from the Z-score is given below:
Step 1: Estimate Mean Value of Dataset
In this first step, we will calculate the Mean value of our total marks number. For that, we are going to use the AVERAGE function.
- At first, select cell F5.
- Now, write down the following formula into the cell.
=AVERAGE(C5:C14)
- Press Enter.
- You will get the value of the mean of our dataset.
Thus, we can say that we have completed the first step, to calculate probability from the Z-score in Excel.
Step 2: Evaluate Standard Deviation
Now, we are going to estimate the Standard Deviation of our dataset. To determine the value, we will use the STDEV.P function.
- First, select cell F6.
- After that, write down the following formula into the cell.
=STDEV.P(C5:C14)
- Press the Enter key.
- You will get the value of the standard deviation.
So, we can say that we have finished the second step to calculate probability from the Z-score in Excel.
Step 3: Determine Z-Score
Here, we are going to evaluate all the values of the Z-score. The general expression of the Z-score shown above, tells us the parameters required to determine the value.
- At the beginning of this step, insert a column between columns C and D.
- Then, rename the column as Z-Score.
- After that, select cell D5 and write down the following formula into the cell. Make sure that you put the Absolute Cell Reference for cells G5 and G6.
=(C5-$G$5)/$G$6
- Press Enter.
- Now, double-click on the Fill Handle icon to copy the formula up to cell D14.
- You will get all the values of Z-Scores for our datasheet.
In the end, we can say that we have accomplished the third step to calculate probability from the Z-score in Excel.
Read More: How to Calculate Critical Z Score in Excel (3 Suitable Examples)
Step 4: Calculate Probability for Each Data
This is the final step of our calculation. Here, we will estimate the value of probability from the value of Z-Score. To calculate the value of probability, we are going to use the NORM.DIST function. The process is given below step by step:
- At first, insert a column between columns D and E.
- After that, rename the column as Probability.
- Now, select cell E5 and write down the following formula into the cell. Make sure that you put the Absolute Cell Reference for cells H5 and H6.
=NORM.DIST(C5,$H$5,$H$6,TRUE)
- Then, press Enter.
- Next, double-click on the Fill Handle icon to copy the formula up to cell E14.
- Finally, you will get all the values of probability.
At last, we can say that we have finished the final step to calculate probability from the Z-score in Excel.
🔍 Interpretation of the Result
We are illustrating the result for cell E5. The value of probability is 0.664. The value means that the possibility of happening that event is 0.664 with respect to the total number of events.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to calculate probability from Z-score in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!
