Correlation means how strongly two statistical variables are dependent on each other. Graphically, we can see the dependence in XY scatter graph. On Y axis we have the dependent variable (variable to be explained) on X the independent variable (explaining variable).

If the correlation is between 0,8 and 1 or -0,8 and -1, then the correlation is obvious.

We have simple data independent and dependent variables.

First, we calculate the averages.

Then we need to calculate the difference between the observation and average.

After that, we multiply the differences.

The next step is to calculate the squares for differences.

Then, we need to calculate the sums for differences in columns F, G and H.

Finally, we calculate the correlation by having multiplication of x and y differences, divided by square root of x and y difference multiplications.

The correlation in our case is roughly 0,959 which is a high value and there is a statistical dependence between independent and dependent variables.

To make it easier, you can just use either CORREL or PEARSON functions. Those functions return the same value as we calculated manually.

One quick way to calculate the correlation is to use data analysis. I have data analysis under data menu, last one in the right.

Select correlation.

Enter the input and output ranges.

The result is still the same.

Do you still want to have a scatter graph ?

The result is still the same.

Do you still want to have a scatter graph ?

Activate the data and select home – analyze data.

Scroll down to find the scatter graph.

Select the dots in the graph and press right mouse button. Select add trendline.

When you scroll downwards the format trendline, you can select display R-squared value on chart.

Now we have a trend line. We see that the dots are pretty close to the trend line.

R square is a square for the correlation.

R square indicates how many percents of changes in independent variable explains the changes in dependent variable. In our case that is 92 %.