How to Calculate Z-Score with 95 Confidence Interval in Excel

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we need to determine the value of the Z score at different levels of confidence, according to our desire. In this article, we will demonstrate to you the step-by-step procedure to calculate the Z score with a 95 confidence interval in Excel. If you are also curious 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:

General expression of Z-score

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 Confidence Interval?

In statistics, a confidence interval describes the likelihood that a dataset parameter will fall between a set of values for a predetermined percentage of the time. Analysts frequently employ confidence intervals that include 95% or 99% of anticipated observations.


How to Calculate Z Score with Conventional Method

Here, we will show the manual calculation process of the Z score. The steps of this manual process are given below:

📌 Steps:

  • First, choose a dataset. Here, we use a simple dataset with 5 data. Those 5 values are 82, 77, 85, 78, and 80.
  • Second, we will estimate the simple Mean of this dataset.

Calculating mean value of the dataset manually

  • Third, we have to evaluate the Standard Deviation of our data.
  • You can see that the value of the Standard Deviation is 2.87. So, the dataset is normally distributed.

  • Choose your desired confidence level interval. For our data, we set it at 95%.
  • After that, in the Z-Score chart, we have to find out the value of 0.975 (e.g. 0.95+(0.05/2)=0.975).
  • Now, you may notice that the vertical axis value for 0.975 is 1.9 and the horizontal axis value is 0.06.

Evaluating Z-score value manually

  • Thus, our Z-score value for a 95% confidence interval will be 1.9+0.06 = 1.96.

So, we can say that we are able to estimate the Z score with a 95 confidence interval manually.


Step-by-Step Procedure to Calculate Z-Score with 95 Confidence Interval in Excel

In this section, we are going to show you the step-by-step procedure to evaluate the Z-score value with a 95 confidence interval in Excel.


Step 1: Calculate Mean 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.

Calculate Mean of Dataset to Calculate Z-Score with 95 Confidence Interval

  • You will get the value of the mean of our dataset.

Thus, we can say that we have completed the first step, to calculate the Z score with a 95 confidence interval in Excel.

Read More: Excel Confidence Interval for Difference in Means (2 Examples)


Step 2: Estimate 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.

Estimate Standard Deviation to Calculate Z-Score with 95 Confidence Interval

  • You will get the value of the Standard Deviation.
  • Now, you may notice that the value of the Standard Deviation is 2.914. So, we can take the decision that our dataset is normally distributed.

So, we can say that we have finished the second step, calculating the Z score with a 95 confidence interval in Excel.

Read More: How to Calculate 90 Percent Confidence Interval in Excel


Step 3: Define Confidence Interval Level

In this step, we have to define our confidence level interval.

  • At first, title cells E7 and E8 as Confidence level and Alpha, respectively.

  • After that, in cell F8, write down the following formula to get the Alpha value.

=1-F7

  • Then, press Enter.

Defining Confidence Interval Level

  • Our task is completed.

Therefore, we can say that we have accomplished the third step, calculating the Z score with a 95 confidence interval in Excel.


Step 4: Estimate Z Score for Desired Confidence Interval

In this final step, we will estimate the Z score value for our desired confidence interval level. To determine the value of the Z score, we will use the NORM.S.INV and ABS functions.

  • Firstly, select cell F10.
  • Now, write down the following formula into the cell.

=ABS(NORM.S.INV((F8)/2))

  • Press Enter.

Z-Score for Desired Confidence Interval

  • You will get the Z score value with a 95 confidence interval level which is equal to the manual procedure.

Finally, we can say that we have finished the final step, calculating the Z score with a 95 confidence interval in Excel.

🔎 Breakdown of the Formula

We are breaking down the formula for cell F10.

👉 NORM.S.INV((F8)/2): The NORM.S.INV function provides us the Z-score value of 0.025. As this interval level is at the right side of the mean position, the value will show a negative sign. Here, the function returns -1.960.

👉 ABS(NORM.S.INV((F8)/2)): The ABS function will show the absolute value of the result of the NORM.S.INV function. For this cell, the function returns 1.960.

Read More: How to Calculate P-Value from Confidence Interval in Excel


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 z-score 95 confidence interval 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!


Related Articles

Soumik Dutta
Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo