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

The article will show you how to calculate Critical Z Score in Excel. It’s an important parameter in the field of statistics. It tells us whether a hypothesis test is under the rejection region or not. With that help, we can find out the significance of any value in the data. A critical value can also give us the probability within the distribution region.


Download Practice Workbook


What Is Critical Z Score?

The Critical Z Score is a value that refers to the area under the Standard Normal model. This is a vertical line that separates the distribution region into two sections. This value also emphasizes how the Standard Deviation varies from the Mean. We often use the Critical Z Score value when the sampling distribution is normal or approximately equal to normal. It also has a great significance while calculating the probability for an unknown Standard Deviation.


3 Examples to Calculate Critical Z Score in Excel

In the dataset, we will calculate for 2 different significance levels (α).

how to calculate critical z score in excel


1. Calculating Critical Z Score for Right-Tailed Test

A Right-Tailed Test is the test to determine if  your hypothesis experiment is less than a significance level. In other words, it is the distribution region to the left side of that level. The basic way to calculate Critical Z Score in Excel is to use the NORM.S.INV function. This a dedicated Excel function to calculate Critical Z Score. We will be using this function to calculate the Right-Tailed Critical Z Score. Let’s go through the discussion below.

Steps:

  • First, make a column to store the Critical Z Score values and type the following formula in cell C5.

=NORM.S.INV(1-B5)

The formula here returns the Right-Tailed Critical value for a level of significance of 0.1 or 10%.

  • After that, hit the ENTER button to see the Right-Tailed Critical Z Score.

how to calculate critical z score in excel

  • Thereafter, use the Fill Handle to AutoFill the lower cell.

Finally, you will get the value of the Right-Tailed Critical Z Score for both 0.05 and 0.1. Note that, these values are positive because they are right to the distribution region.

Thus you can calculate the Right-Tailed Critical Z Score in Excel.


2. Determining Critical Z Score for Left-Tailed Test

A Left-Tailed Test is a test to determine if your hypothesis experiment is greater than a significance level. In other words, it is the distribution region to the right side of that level. The basic way to calculate Critical Z Score in Excel is to use the NORM.S.INV function. This is a dedicated Excel function to calculate Critical Z Score. We will be using this function to calculate the Left-Tailed Critical Z Score. Let’s go through the discussion below.

Steps:

  • First, make a column to store the Critical Z Score values and type the following formula in cell D5.

=NORM.S.INV(B5)

how to calculate critical z score in excel

The formula here returns the Left-Tailed Critical value for a level of significance of 0.1 or 10%.

  • After that, hit the ENTER button to see the Left-Tailed Critical Z Score.

  • Thereafter, use the Fill Handle to AutoFill the lower cell.

how to calculate critical z score in excel

Finally, you will get the value of the Left-Tailed Critical Z Score for both 0.05 and 0.1. Note that, these values are negative because they are left to the distribution region.

Thus you can calculate the Left-Tailed Critical Z Score in Excel.


3. Calculating Critical Z Score for Two-Tailed Test

A Two-Tailed Test is a process where the critical area of a distribution has a two directional region and checks if a value is within the normal distribution region. This test is widely used in null-hypothesis experiments for statistical significance. If the value falls into any of the critical regions. The basic way to calculate Critical Z Score in Excel is to use the NORM.S.INV function. This a dedicated Excel function to calculate Critical Z Score. We will be using this function to calculate the Two-Tailed Critical Z Score. Let’s go through the discussion below.

Steps:

  • First, make a column to store the Critical Z Score values and type the following formula in cell D5.

=NORM.S.INV($B$5/2)

The formula here returns one of the Two-Tailed Critical values for a level of significance of 0.1 or 10%.

  • Hit the ENTER button and you will see the left-sided value of this Two-Tailed test.

how to calculate critical z score in excel

  • To get the other value of the Two-Tailed Test, type the formula below in C6 and press ENTER.

=NORM.S.INV((1-$B$5/2))

This will return the other value of the Two-Tailed Test.

  • Similarly, use the following formula to calculate left sided value of Two-Tailed Test for a significance level of 0.05 or 5% and hit the ENTER button.

=NORM.S.INV($B$7/2)

how to calculate critical z score in excel

  • Thereafter, use this formula to calculate right sided value of Two-Tailed Test for a significance level of 05 or 5% and hit the ENTER button.

Thus you can get the values of the Two-Tailed Critical Z Score for both 0.05 and 0.1. Note that, these values have both positive and negative signs because they are within the  distribution region and this region has positive and negative values in its right and left side respectively.

Thus you can calculate the Two-Tailed Critical Z Score.


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

how to calculate critical z score in excel


Conclusion

The fact to the matter is, this article provides you all the possible examples of how to calculate Critical Z Score in Excel in different conditions. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.com

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo