XLSTAT: Pearson correlation coefficient in Excel

 

Background

Today, many correlation coefficient calculators are available, but you can easily calculate the linear correlation coefficient yourself.
The Pearson correlation coefficient is calculated with the following formula:

How to interpret correlation?

The correlation coefficient represents the strength of the linear relationship between two variables.
The closer its value is to -1, the stronger the negative link between the variables: when one increases, the other decreases.
The closer its value is to 1, the stronger the positive link: both variables increase or decrease at the same time. A correlation coefficient of 1 represents a perfect positive linear relationship between the variables.
If the correlation coefficient is close to 0, it means that there is no link between the two variables.

How is the significance tested?

A test statistic is calculated with the formula below that follows a Student distribution with n-2 degrees of freedom.

If these values go out of bounds defined by the alpha = 0.05 value, then the null hypothesis is rejected and the Pearson correlation coefficient is significantly different from 0.

 

Dataset for Example

The data represents a sample of customers from an online shoe shop. Rows correspond to customers and columns to the money they spent as well as several other characteristics (e.g. shoe size, weight…).

 

Setting Up

• Open XLSTAT

• Click on the Correlation /Association tests menu and select the Correlation tests feature:

In the General tab:

• Select columns A-E in the Observations/Quantitative variables field.

• Choose the Pearson correlation coefficient from the drop-down list.

In the Outputs tab:

• Activate the display of p-values and coefficients of determination (R²).

• Enable the filtering and sorting options to organize the variables based on their R² values.

• Click on the OK button to launch the computations.

• The results are displayed on a new Excel sheet.

 

Interpreting the Results

The first results in XLSTAT are the descriptive statistics for all variables (mean, std.deviation, etc). The correlation matrix is then displayed followed by the 95% lower and upper confidence bounds for the correlation coefficients. One table will display the upper bounds and another the lower bounds. We can also display both bounds in a single table.

 

The correlations between the Invoice amount and the attributes Height and Weight are positive and strong (close to 1). On the other hand, we observe a negative correlation between the Time spent and the Invoice amount suggesting that the more time customers spend on the website the less money they spend. 
All the coefficients appear to be significant at a 0.05 significance level (values in bold). The p-values will be computed for each coefficient in order to test the null hypothesis that the correlation coefficients are equal to 0. In other words, the risk of rejecting the null hypothesis (coefficient =0) while this is true is less than 5%. This is confirmed by the table of the p-values below (p-values < 0.0001).
Note that the shoe size is not displayed in the correlation matrix. This variable was excluded because it has the lowest sum of R2 among all the variables.

The coefficients of determination correspond to the squared correlation coefficients. They measure the strength of the correlation, whether it is negative or positive. Here, using the filter variables option, we have chosen to display only the 4 variables for which the sum of R2 with other variables is the highest.

 

Moreover, we have sorted the variables using the BEA (Bond Energy Algorithm). This method applies a permutation on rows and columns of a square matrix in a way that columns having similar values on rows are close to each other. The FPC (First Principal Component) is also available. 
The next graph is a correlation map that uses a blue-red (cold-hot) scale to display the correlations. The blue color corresponds to a correlation close to -1 (e.g. Time spent on site vs Invoice amount) and the red color corresponds to a correlation close to 1 (e.g. Height vs Invoice amount).

The following graph is a matrix of plots. A histogram is displayed for each variable (diagonal) and a scatter plot for all combinations of variables. 
The color of the data points in the scatter plots reveals whether there is a positive (red) or negative correlation (blue). The patterns found in the scatter plots indicate the type but also the strength of the relationship between the two variables. For example, shoe size looks like it is poorly linked to all other attributes (last column or last row of the matrix) implying correlations close to zero

 

What’s next: exploring quantitative variables with Principal Component Analysis

The Principal Coordinate Analysis in Excel is a multivariate analysis method that will allow us to further explore the relationships between the variables and relate customers to variables and to one another.