Statistical coefficients and Excel

Written on 11 January 2022, 10:32am

Tagged with: , , ,

Quick follow up to this post.

Here is how to use Excel in order to answer the question below:

The correlation coefficient is calculated in Excel using the correl() function: =CORREL(B4:B9;C4:C9)

The determination coefficient is calculated in Excel using the rsq() function: =RSQ(B4:B9;C4:C9)

Of course, the coefficient of determination (R^2) can also be calculated as (correlation coefficient) ^ 2

Note: instead of the correl() function, you can also use the formula as here. You will arrive at the same result.

More complicated, but same result (0.529809)

The R-squared of the data set can be also shown by Excel if the data points are plotted in a chart and a linear trendline is added:

Note the same R^2 value of 0.2807