How to Calculate P-Value & Its Correlation in Excel 2007

How to Calculate P-Value Its Correlation in Excel 2007
How to Calculate P-Value Its Correlation in Excel 2007

Finding a P-value in Excel for correlations isn’t too hard, but there isn’t a single function for the job. Correlations are often an important step in figuring out the relationship or link between two sets of data. You can use Excel’s built-in functions to figure out a correlation coefficient, such as Pearson’s correlation coefficient. There are also functions for doing statistical tests. But you need to change the r-value you get from your correlation into a t statistic before you can figure out what the results mean.

How to Use Excel to Find a Correlation Coefficient

If you want to find a Pearson correlation or a general correlation coefficient in Excel 2007, there are built-in functions that let you do this. First, you need two sets of data that you want to compare to see if there are any correlations. Think of them as being in columns A and B, from cells 2 to 21 in each. To find the correlation coefficient in Excel, you can use either the Correl or Pearson function. To find the correlation coefficient, type “=Correl([array 1], [array 2])” or “=Pearson([array 1], [array 2])” in a blank cell, where “[array 1]” refers to the first column of data and “[array 2]” refers to the second. In the example, you would type “=Pearson(A2:A21, B2:B21)” or “=Correl(A2:A21, B2:B21).” You can also open the parentheses, highlight the relevant cells with your mouse or keyboard, type a comma, and then highlight the second set. This gives you a correlation coefficient between -1 and 1.

How to Figure Out a Correlation in Excel

For a correlation to make sense in Excel, the output of the correlation function must be changed into a t value. A formula can be used to do this. Find a blank cell and type: “=([correlation coefficient]*SQRT([number of pairs of data]-2)/SQRT(1-[correlation coefficient]2)” Again, the information you need to fill in for your own data is in the square brackets. For “[correlation coefficient],” enter the cell reference you used in the last section to figure out the correlation. Enter the total number of data points in a single array for “[number of pairs of data].” There are a total of 20 pairs of data points in the example that runs from cell 2 to cell 21 in columns A and B. In statistical terms, this is n. So, let’s say you follow the example and put your correlation in cell C2. To find the t statistic, you would type “=(C2 *SQRT(20-2)/SQRT(1-C2^2))” into a blank cell.

Now you can use this with the “Tdist” function to find the P-value. To do the relevant significance test in Excel, type “=TDIST([t statistic], [degrees of freedom], [number of tails])” in an empty cell. Again, you put your own information in the square brackets. The value you just found is the t statistic. For this example, let’s say you did this in cell C3. The sample size (n) minus two gives the degrees of freedom for a correlation, which in this case would be 18. Lastly, a one-tailed or two-tailed test tells you if you’re looking for results that go in one direction or two, or if you’re specifically looking for a positive or negative correlation. If you don’t know which way the correlation would go, use a two-tailed test and put “2” where it says “[number of tails].

To find the P-value in the example, you would type “=TDIST(C3, 18, 2).” In general, if P 0.05, a result is considered significant.

Different kinds of Excel

Finding the correlation coefficient and doing a significance test in newer versions of Excel are both done in the same way. All of the later versions of Excel have the same functions. In versions of Excel before 2003, however, the “Pearson” function often makes mistakes when rounding, so you should use the “Correl” function instead.