This blog is addition to my earlier blog about correlation. Here I present how to calculate several correlations at one go.

This is a sales report on four products.

A sales campaign was made for product P1 in P10/23.

How did the campaign affect other products? One way is to have a correlation matrix. That presents correlations between each product.

Select file – option – add-ins. Check that analysis toolpak is active.

Select data – data analysis.

Select correlation.

Select sales data as input range, include headers. Grouped by is columns as we have data per column.

Then we have the results. Correlations are presented per each combination.

The strongest correlation is between P1 and P4. Also, P2 and P4 have mutual correlation, as well P1 and P2. P3 lives its own life. Correlation between P1 and P3 is slightly negative.

Sales campaign on P1 affects positively P4 and P2. Correlation between P1 and P4 is bit higher than between P1 and P2. Campaign does not affect sales for P3 at all. Campaign is slightly decreasing the sales for P3.    

Correlation matrix makes analysis fast. You don’t have to calculate each correlation separately. Of course, you can do that with CORREL function.