Home » Google Sheets » How to Find Correlation Coefficient in Google Sheets
FormulasGoogle Sheets

How to Find Correlation Coefficient in Google Sheets

Adrey Adrey April 4, 2023 58 0

A correlation coefficient is a metric that expresses a correlation, or a statistical link between two variables, in numerical terms. In brief, correlation coefficient is important to understand the significance and direction of the linear correlations between pairs of variables. In this article, we have demonstrated 5 easy ways to find correlation coefficient in Google Sheets.

A Sample of Practice Spreadsheet

You can download the spreadsheet used for describing methods for this article from here.

What Is Correlation Coefficient ?

The correlation coefficient indicates how closely two variables are related. There are three different types of data correlation: Positive correlation, Negative correlation, and No correlation.

According to the Positive correlation coefficient, when one variable rises, another variable rises as well. A positive correlation coefficient has a value larger than 0.

Positive correlation coefficient in Google Sheets

According to the Negative correlation coefficient, as one variable rises, another variable falls. The negative correlation coefficient has a value that is less than 0.

Negative correlation coefficient in Google Sheets

The correlation coefficient, whose value is near to or equal to 0, indicates that there is no association between two variables.

No correlation coefficient in Google Sheets

5 Easy Methods to Find Correlation Coefficient in Google Sheets

We will be using the following dataset to demonstrate methods in this article. The dataset represents some persons’ height and age.

Dataset on correlation coefficient in Google Sheets

1. Using CORREL Function

The built-in CORREL function in Google Sheets returns the value of the correlation coefficient of two ranges of cells. The first one is the cell ranges of Y data which is the dependent variable. The second range of cells is the data of independent variable X.

1.1 Inserting Data Manually

We can simply use this CORREL function by typing down the data of both variables within the formula. It’s not a feasible method for a long dataset but maybe helpful for split values.

Steps:

  • In the following dataset, select Cell C14 and type the following formula then press Enter-
=CORREL({138,170,176,175,178,174,170,172},{12,15,17,20,24,29,32,45})

Here, the data in the first {} are the values of dependent variable Y and the data in the second {} are the values of independent variable X.

Inserting Data Manually using CORREL function

1.2 Using Cell References

We can simply use cell references within the CORREL formula to get the correlation coefficient.

Steps:

  • In Cell C14, apply the following formula and then Enter–
=CORREL(B5:B12,C5:C12)

Here, Cell range B5:B12 indicates the dependent variables’ values while Cell range C5:C12 represents the independent variables’ values.

Using Cell References while CORREL to find correlation coefficient in Google Sheets

Read More: Find Value in a Range in Google Sheets (3 Easy Ways)

2. Combining RSQ and SQRT Functions

Correlation coefficient is the square root of coefficient of determination. Here, we will first calculate the value of the coefficient of determination using the RSQ function. Then we will get the result of the square root of that as correlation coefficient using the SQRT function.

Steps:

  • First, select the Cell C14 in the following dataset and enter the following formula-
=RSQ(B5:B12,C5:C12)

This will calculate the value of the coefficient of determination.

Combining RSQ and SQRT Functions to measure Correlation Coefficient

  • Now, activate Cell C15, put the following formula below there and press Enter–
=SQRT(C14)

In the formula, Cell C14 refers to the cell that carries the value of the coefficient of determination.

Using SQRT Functions to measure Correlation Coefficient

Read More: Easy Guide to Replace Formula with Value in Google Sheets

3. Applying PEARSON Function

The formula of PEARSON is as follows-

Great news is, Google Sheets has a built-in PEARSON function for this formula. The Pearson correlation coefficient is returned by the PEARSON function after receiving two arrays as parameters in which the first one is for the dependent variable and the 2nd one is the values of the independent variable. We can easily find correlation coefficient in Google Sheets using this formula.

Steps:

  • First, select Cell C14 and then apply the following formula and press Enter–

=PEARSON(B5:B12,C5:C12)

Then you will get the same output as before.

Applying PEARSON Function

Read More: Find All Cells With Value in Google Sheets (An Easy Guide)

Similar Readings

4. Using PEARSON Formula Manually

If you got an assignment or an upcoming test based on correlation coefficient, I would prefer you to follow this method to understand your topic properly. Before this, I have already shown you the use of the PEARSON function. But now, we will learn how to solve the Pearson correlation coefficient manually in Google Sheets. The AVERAGE, SUM, and SQRT functions will come up in this manner.

The basic equation in this case is as follows:

Pearson Formula

We will make a dataset for this as shown below-

Using PEARSON manual method

Steps:

  • First of all, we have to find X̅ (average value of independent variable X) and Y̅ (average value of dependent variable Y). Select Merged Cell B15:B22, apply the following formula-
=AVERAGE(C5:C12)

Here, Cell range C5:C12 refers to the values of independent variable X.

Applying AVERAGE formula while operating pearson method manually

  • Secondly, do the same for the Y variable as well. Select Merged cell C15:C22 and then apply the following formula-
=AVERAGE(B5:B12)

Cell range B5:B12 refers to the values of the Y variable. The formula here will calculate the average of Y values.

Applying AVERAGE formula while operating pearson method manually

  • Now, we have to calculate the values of (Xi-X̅) and (Yi-Y̅). For that, we will apply the following formula in Cell D15.
=C5-$B$15

This will calculate the difference between the value of X in Cell C5 and the average value of X that is X̅.

Calculating difference while operating pearson method manually

And then we will apply the below formula into Cell E15.

=B5-$C$15

This will measure the difference between the value of Y in Cell B5 and the average value of Y that is Y̅.

Calculating difference while operating pearson method manually

  • After that, select Cell D15 and then simply drag down using the Fill handle icon as shown in the circled portion below.

Dragging down while Calculating difference while operating pearson method manually

This will calculate the difference between X̅ and the rest of the other X values as well.

output after applying fill handle

  • Now, do the exact same by selecting Cell E15 and the result will be as follows.

output after dragging down using fill handle

  • At this point, we have to calculate the product of (Xi-X̅) and (Yi-Y̅) for each value of (Xi-X̅) and (Yi-Y̅). Select Cell F15 and apply the following formula.
=D15*E15

Here, Cell D15 refers to the value of (Xi-X̅) and Cell E15 refers to the value of (Yi-Y̅).

Calculating difference while operating pearson method manually

  • Drag down using the Fill Handle icon in Cell F15 like previously and the result will be as follows.

Dragging down while Calculating difference while operating pearson method manually

  • Select Cell F23 and apply the below formula. Have a look at the Pearson formula again. We need the value of ⅀(Xi-X̅)*(Yi-Y̅).
=SUM(F15:F22)

The SUM function we calculate the summation of all the values of the product (Xi-X̅)*(Yi-Y̅).

Using SUM function while operating pearson method manually in google sheets

  • Next, we need to calculate the square values of all the (Xi-X̅) and (Yi-Y̅) Apply the formula into the box below in Cell G15 and press Enter–
=D15^2

Obviously it will calculate the square of (Xi-X̅) value which in Cell D15. You can see the output in Cell G15.

Calculating square

  • Drag down using the Fill handle icon as shown in the circled portion to get the square value of other (Xi-X̅) values as well.

Dragging down again for square value

After that the square value of other (Xi-X̅) values.

All square values after dragging down

  • Thereafter, choose Cell G23 and apply the following formula below.
=SUM(G15:G22)

Going through this, the summation of all (Xi-X̅)2 values across the Cell range G15:G22.

Using SUM function while operating pearson method manually in google sheets

  • Next, select Cell H15, apply the following formula below and press Enter–
=E15^2

This will determine the square of (Yi-Y̅) value which is in Cell E15. The output will be as follows in Cell H15.

Calculating square

  • Now drag down using the Fill handle icon in Cell H15 like previously to get the square of other (Yi-Y̅) values as well.

Dragging Down for all square values

  • After that, activate Cell H23, give input the following formula and press Enter–
=SUM(H15:H22)

Here, the SUM function will calculate the summation of all the (Yi-Y̅)2 values across Cell range H15:H22.

Using SUM function

  • Finally, select Cell H4 and apply the following formula below then press Enter–
=(F23)/SQRT(G23*H23)

This formula is basically the representation of the Pearson formula: Pearson FormulaHere, Cell F23 refers to the value ⅀(Xi-X̅)(Yi-Y̅) (the numerator of the Pearson formula).

SQRT(G23*H23) refers to the value of √(⅀(Xi-X̅)^2*(Yi-Y̅)^2) (the denominator of the Pearson formula).

Therefore, the value of the correlation coefficient will be as follows in Cell H4.

finally using the PEARSON formula in Google Sheets

Read More: How to Find Merged Cells in Google Sheets (3 Ways)

5. Using Chart Feature

We can create a chart and get the value of the coefficient of determination from that. Then, calculating the square root value from that, we will simply get the value of the correlation coefficient.

Steps:

  • Select the Cell range B4:C12, click on the Insert chart ribbon from the toolbar at top.

Using Chart Feature

  • A scattered chart will appear on your screen.

Plotting scattered chart

  • Now, from the sidebar titled “Chart editor”, go to the Customize menu then select “Series” drop-down.

Customizing from chart editor

  • Afterward, in that drop-down menu, mark the Trendline box and then mark the Show R2

marking coefficient of determination in chart

  • Following this, you will see that the value of coefficient of determination that is R2 will appear on your chart.

calculating coefficient of determination in chart

  • Therefore, type down this value “0.156” into Cell C14 in the following dataset.

putting the value of coefficient of determination for calculating correlation coefficient

  • Finally, apply the following formula into Cell C15 and press Enter–
=SQRT(C14)

In the formula, Cell C15 refers to the cell that carries the value of coefficient of determination.

The output showing in Cell C15 is the value of correlation coefficient.

using SQRT function

Read More: How to Find Slope of Trendline in Google Sheets (4 Simple Ways)

Conclusion

The article contains 5 efficient methods to find correlation coefficient in Google Sheets. Hope this will help with your task. Visit officewheel.com to explore more relevant articles.

Related Articles

Tags:

Adrey

Adrey

Hello! This is Raiyan Zaman Adrey, a fresh graduate in BSc in Civil Engineering from Bangladesh University of Engineering and Technology. From my high school I have been using Excel in which I was always interested and had fun and this led me to do more research on Excel and Google Sheets as both are kinda similar platform. I have always been enthusiastic, self-motivated, reliable and hard working person and for a long time, I am trying to improve myself more and more so that I can face any challenging situation and adapt myself to any environment.

We will be happy to hear your thoughts

Leave a reply

About OfficeWheel.com

OfficeWheel.com is a place where you can learn all about Google Sheets, Excel, Data Analysis, and other Office-related programs. We provide tips, how-to guides, and also provide solutions to your business problems.

OfficeWheel
Logo