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

2 notes on data visualization

Written on 9 October 2019, 09:37pm

Tagged with: , ,

  1. Know the limitations of pie charts: not so good for comparing values between themselves, but really good to compare relative to the 50% line
  2. Match your type of data with the right color scheme. There are 3 types of data: sequential, divergent and qualitative. The sequential color schemes help with ordered data. The divergent schemes use a neutral color the mid-range data and highly contrasting colors for the extremes. The qualitative schemes focus on creating visual differences between the sets of data.
Bar charts are better if you need to compare the values
But pie charts have their strengths when comparing to the 50% line
A sequential scheme. Colors range from light to dark, and are usually colorblind safe
A diverging scheme. Mid-range neutral color, highly contrasting extremes.
A qualitative, colorblind-safe scheme. It gets trickier if you need more than 4 colors. Each color need to scream “I’m different!