How to Find Critical Value in Excel (2 Useful Methods)

Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. In this article, I will elucidate how to find critical value in Excel by showing 2 useful methods.


Download Practice Workbook

Download this practice while going through this article.


2 Useful Methods to Find Critical Value in Excel

This is the dataset that I am going to work with. I have assumed the Degree of Freedom (n) = 14 and Level of Significance (α) = 0.1. I will calculate the T-values and Z-values using these parameters.

How to Find Critical Values in Excel


1. Find T Critical Value in Excel

The T critical value is basically the indicator of determining the statistical significance in a T-test. Depending on the type of test, the calculation process varies.


1.1 Use T.INV Function for Left-Tailed Test

Here we will learn how to calculate T critical value for a Left-Tailed Test. We need to use the T.INV function in this case.

Steps:

  • Go to C8. Write down the following formula.
=T.INV(C4,C5)

  • Now press ENTER. Excel will return the result.

How to Find Critical Values in Excel


1.2 Combine ABS and T.INV Functions for Right-Tailed Test

Now I will calculate the T critical value for a right-tailed test. This time I will use the ABS function along with the T.INV function.

Steps:

  • Go to C9. Write down the following formula.
=ABS(T.INV(C4,C5))

Explanation:

Here the T.INV(C4,C5) returns the T-value for the left-tailed test and the ABS function adjusts the result for a right-tailed one.

  • Now press ENTER. Excel will return the result.

How to Find Critical Values in Excel


1.3 Apply T.INV.2T Function for Two-Tailed Test

Now let’s focus on a two-tailed test. To calculate the T critical value for a two-tailed test, we ought to use the T.INV.2T function.

Steps:

  • Go to C10. Write down the following formula
=T.INV.2T(C4,C5)

  • Then press ENTER. Excel will show the result.

How to Find Critical Values in Excel


2. Use of NORM.S.INV Function to Find Z Critical Value in Excel

Now I will put some light on Z critical value. It is a statistical term widely used to determine the statistical significance of a hypothesis. In this case, the population parameters are of concern. We need to calculate the Z critical value for 3 different types of cases.

  • Left-tailed test
  • Right-tailed test
  • Two-tailed test

I will discuss all the cases one by one.


2.1 For Left-Tailed Test

In this section, I will focus on the left-tailed test.

Steps:

  • Go to C8 and write down the following formula
=NORM.S.INV(C4)

  • Then press ENTER. Excel will return the output.

How to Find Critical Values in Excel


2.2 For Right-Tailed Test

In this section, I will explain how to calculate Z critical value for a right-tailed test.

Steps:

  • Go to C9 and write down the following formula
=NORM.S.INV(1-C4)

  • Then press ENTER. You will get the result.

How to Find Critical Values in Excel


2.3 For Two-Tailed Test

Excel can also compute the Z critical value for two-tailed tests. There are two values corresponding to a two-tailed test.

Steps:

  • Go to C10. Write down the following formula
=NORM.S.INV(C4/2)

  • Now press ENTER to get the output.

How to Find Critical Values in Excel

  • Similarly, write down the following formula in C11.
=NORM.S.INV(1-C4/2)

  • After that, press ENTER to calculate the result.

How to Find Critical Values in Excel


Things to Remember

  • The ABS function adjusts the T value for a right-tailed test.
  • The T and Z critical values are different from the T and Z values. We calculate the T and Z values from the sample statistic and population parameter. Then we compare those values with the critical values to determine the statistical significance of a hypothesis.
  • T values are used when the standard deviation of the population is unknown and the sample size is relatively small.

Conclusion

In this article, I have demonstrated 2 handy methods to find critical value in Excel. I hope it helps everyone. And lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.

Akib

Akib

Hi, this is MD Akib Bin Rashid. I completed my BSc in Engineering back in 2019. After that, I did an MBA. Then, I joined as an Excel and VBA Content Developer at SOFTEKO Digital. Being passionate about data analytics and finance, I will try to assist you with Excel

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo