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.
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 to Find T Critical Value 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.
1.2 Combine ABS and T.INV Functions to FindT Critical Value 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.
1.3 Apply T.INV.2T Function to Find T Critical Value 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.
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.
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.
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.
- Similarly, write down the following formula in C11.
- After that, press ENTER to calculate the result.
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.
The post How to Find Critical Value in Excel (2 Useful Methods) appeared first on ExcelDemy.
