Procedure to Calculate Partial Correlation Using Excel
We can automate our task of calculating Partial Correlation in excel. For example, “Arushi” is the “Maths” teacher of the 10th class. She had the data of students, consisting of internal assessment marks(Z), the number of hours a student play(X), and the final marks in Term2(Y). She wants to find the number of hours a student play is correlated to the final marks in Term2 by controlling the internal assessment marks. It means that she wants to find a Partial Correlation between X and Y, controlling Z.
Following are the steps
Step 1: Make four(4) new columns in range E2:E5, name Correlation(X, Y), Correlation(Y, Z), Correlation(X, Z) and PartialCorrelation(X,Y).Z. Use =correl(array1, array2) function to calculation the correlation between two variables.
Step 2: In cell F2, use =correl(B2:B6, C2:C6), function to calculate the correlation between Hours of Study(X) and Final marks(Y). Press Enter.
Step 3: The value of correlation between Hours of Study(X) and Final Marks(Y) is -0.77116.
Step 4: In cell F3, use =correl(B2:B6, A2:A6) function to calculate the correlation between Hours of Study(X) and Internal Assessment Marks(Z). Press Enter.
Step 5: The value of correlation between Hours of Study(X) and Internal Assessment Marks(Z) is 0.518751.
Step 6: In cell F4, use =correl(c2:C6, A2:A6), function to calculate the correlation between Final marks(Y) and Internal Assessment Marks(Z). Press Enter.
Step 7: The value of correlation between Final marks(Y) and Internal Assessment Marks(Z) is -0.01257.
Step 8: In cell F5, write the formula of partial correlation as explained above, i.e., =(F2-F3*F4)/(SQRT(1-F3*F3)*SQRT(1 – F4*F4)). Press Enter.
Step 9: The value of partial correlation between Hours of Study(X) and Final Marks(Y), controlling Internal Assessment Marks(Z) is -0.89446.
How to Calculate Partial Correlation in Excel?
Partial correlation helps find the correlation between the two variables by removing the effect of the third variable. There can be situations when the relations between variables can be many. This could reduce the accuracy of correlation or could also give wrong results. Partial correlation removes the effects of other variables. Excel helps us find a partial correlation automatically by the formula. In this article, we will learn how to find partial correlations in excel.