Table of Contents
Point-Biserial Correlation is a statistical measure used to determine the relationship between a continuous variable and a binary variable. In Excel, this can be calculated by first organizing the data into two columns, one for the continuous variable and one for the binary variable. Then, using the CORREL function, the correlation coefficient can be calculated. This coefficient, ranging from -1 to 1, represents the strength and direction of the relationship between the two variables. A positive value indicates a positive correlation, while a negative value indicates a negative correlation. Point-Biserial Correlation in Excel is a quick and simple way to analyze the association between a continuous and binary variable.
Calculate Point-Biserial Correlation in Excel
Point-biserial correlation is used to measure the relationship between a binary variable, x, and a continuous variable, y.
Similar to the Pearson correlation coefficient, the point-biserial correlation coefficient takes on a value between -1 and 1 where:
- -1 indicates a perfectly negative correlation between two variables
- 0 indicates no correlation between two variables
- 1 indicates a perfectly positive correlation between two variables
This tutorial explains how to calculate the point-biserial correlation between two variables in Excel.
Example: Point-Biserial Correlation in Excel
Suppose we have the following binary variable, x, and a continuous variable, y:
To calculate the point-biserial correlation between x and y, we can simply use the =CORREL() function as follows:
The point-biserial correlation between x and y is 0.218163.
Since this number is positive, this indicates that when the variable x takes on the value “1” that the variable y tends to take on higher values compared to when the variable x takes on the value “0.”
We can easily verify this by calculating the average value of y when x is 0 and when x is 1:
When x = 0, the average value of y is 14.2. When x = 1, the average value of y is 16.2. This confirms the fact that the point-biserial correlation between the two variables should be positive.
We can also use the following formulas to calculate the p-value for this correlation coefficient:
The p-value turns out to be 0.5193. Thus, although the correlation coefficient between the two variables is slightly positive it turns out to not be a statistically significant correlation.
Additional Resources
How to Calculate Spearman Rank Correlation in Excel
How to Calculate Partial Correlation in Excel
How to Find the P-value for a Correlation Coefficient in Excel