How to Find Critical Value of r in Excel (With Easy Steps)

In this article, we will learn to find the critical value of r in Excel. Generally, the critical value of r is determined from a chart. But we can also determine the critical value of r in Excel using a formula. Today, we will demonstrate step-by-step procedures to calculate the critical value of r. You can also use the practice book below as an ‘r critical value’ calculator. So, without any delay, let’s start the discussion.


Download Practice Book

Download the practice book from here and exercise to test your skills.


What Is Critical Value of r?

The critical value of r is the minimum value of r. It is calculated for the given sample size and alpha level. For a small sample size, the value of critical r should be high or near the value of the correlation coefficient.

There are two types of r critical value. They are 1-tailed r and 2-tailed r. The formula for calculating the critical value is slightly different in these two cases. In 2-tailed r, we divide the value of the probability by 2.


Step-by-Step Procedures to Find Critical Value of r in Excel

In the following sections, we will discuss the steps to find the critical value of r and explain the used formula.

STEP 1: Make Dataset Structure Ready

  • In the first step, we need to create a dataset structure in our excel worksheet.
  • In the dataset, you need to have cells for Sample Size (N), Alpha (ɑ), and Degrees of Freedom.
  • Also, we need to allocate cells to enter the formulas of 1-tailed and 2-tailed r.

Step-by-Step Procedures to Find Critical Value of r in Excel


STEP 2: Insert Formula of Degrees of Freedom

  • Secondly, we will insert the formula of degrees of freedom.
  • To find the degrees of freedom, we need to subtract 2 from the sample size.
  • In our dataset, Cell B5 will contain the sample size.
  • So, in Cell D5, we have typed the formula below:
=B5-2

Step-by-Step Procedures to Find Critical Value of r in Excel

  • After that, press Enter and you will see -2 in Cell D5.
  • We see -2 in Cell D5 because we have not entered the sample size yet.

Step-by-Step Procedures to Find Critical Value of r in Excel


STEP 3: Apply Formula to Find Critical r (1-tailed)

  • Thirdly, we will apply the formula to find the critical value of r (1-tailed).
  • In order to do that, select Cell C8 and type the formula below:
=(T.INV(1-C5,D5))/SQRT((T.INV(1-C5,D5))^2+D5)

Step-by-Step Procedures to Find Critical Value of r in Excel

Here, we have used the T.INV function and the SQRT function to find the critical value of 1-tailed r. The T.INV function calculates the left-tailed inverse value of the distribution. It has two required arguments. The first argument denotes the probability and the second argument indicates the degrees of freedom. And the SQRT function finds the square root of a given number.

  • After typing the formula, hit Enter.
  • You will get the #NUM! error. It happens because Cell B5 & C5 are still empty.

Step-by-Step Procedures to Find Critical Value of r in Excel


STEP 4: Implement Formula to Determine Critical r (2-tailed)

  • In the following step, we will implement the formula to determine the critical value of r (2-tailed).
  • To do so, select Cell C6 and type the formula below:
=(T.INV(1-C5/2,D5))/SQRT((T.INV(1-C5/2,D5))^2+D5)

Step-by-Step Procedures to Find Critical Value of r in Excel

Here, the difference from the previous equation is that we divided the probability or the first argument of the T.INV function by 2.

  • After that, press Enter. Don’t worry if you see the #NUM! error.

Step-by-Step Procedures to Find Critical Value of r in Excel


STEP 5: Specify a Value of Alpha (ɑ)

  • At this moment, type the value of Alpha (ɑ) in Cell C5.
  • In this case, we have used 05 as the value of Alpha (ɑ).


STEP 6: Enter Sample Size (N) to Get Result

  • To begin with, in the sixth step, we need to enter the value of the Sample Size (N) in Cell B5.
  • We have used 18 as the sample size.

  • Finally, hit Enter to see the results like the picture below.


STEP 7: Calculate Critical Value with Different Sample Size (N) & Alpha (ɑ)

  • In order to test the calculator, change the value of Alpha (ɑ), and the critical values will automatically be updated.

  • Again, if you change the Sample Size (N), the r critical values will also be updated.


Conclusion

In this article, we have demonstrated step-by-step procedures to Find the Critical Value of r in Excel. I hope this article will help you to perform your tasks easily. Moreover, you can use the same code to save the file. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Again, you can also use the workbook as a critical value calculator. Visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo