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

Get FREE Advanced Excel Exercises with Solutions!

The article will show you how to calculate the 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.


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 of an unknown Standard Deviation.


How to Calculate Critical Z Score in Excel: 3 Suitable Examples

In the dataset, we will calculate for 2 different Level of Significance, α.

how to calculate critical z score in excel


1. Calculating Critical Z Score for Right-Tailed Test

A Right-Tailed Test is a 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 the Critical Z Score in Excel is to use the NORM.S.INV function. This is a dedicated Excel function to calculate the 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 the Critical Z Score in Excel is to use the NORM.S.INV function. This is a dedicated Excel function to calculate the 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 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 the Critical Z Score in Excel is to use the NORM.S.INV function. This is a dedicated Excel function to calculate the 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 the left-sided value of the 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 the right-sided value of the 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 on its right and left sides respectively.

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

Read More: How to Calculate Z Score in Excel


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


Download Practice Workbook


Conclusion

The fact of the matter is, that this article provides you with 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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Meraz Al Nahian
Meraz Al Nahian

Md. Meraz Al Nahian has worked with the ExcelDemy project for over 1.5 years. He wrote 140+ articles for ExcelDemy. He also solved a lot of user problems and worked on dashboards. He is interested in data analysis, advanced Excel, statistics, and dashboards. He also likes to explore various Excel and VBA applications. He completed his graduation in Electrical & Electronic Engineering from Bangladesh University of Engineering & Technology (BUET). He enjoys exploring Excel-related features to gain efficiency... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo