**In a heated argument, the regression shouted at correlation, “You are dumbass, you don’t even know the cause and effect of this argument” **

**Correlation** is used to measure strength of the relationship between two variables. It can be positive, negative or zero.

**Positive Correlation : ** Both variables tend to move in the same direction: If one variable increases, the other tends to increase. If one decreases, the other tends to decrease.

**Negative Correlation :** Both variables tend to move in the opposite directions: If one variable increases, the other tends to decrease, and vice-versa.

*Note: The correlation coefficient may take on any value between +1 and -1.*
**Examples**
1: How are sales of ABC Company and GDP related?

2: How are annual returns on Treasury Bills and Bonds related?

*Suppose you would like to know whether there is a relationship between grades and number of hours you spend studying.*

You can

**download the workbook **used in this example and practice what you learn.

**Using Excel:**

1. If you haven’t already installed the **Analysis ToolPak **, Click the **Microsoft Office** button, then click on the **Excel Options** , and then select **Add-Ins** , Click **Go**, check the **Analysis ToolPak** box, and click **Ok**. **How to Install Analysis ToolPak**
2. Select **Data** tab, then click on the **Data Analysis** option, then selects **Correlation **from the list and Click** Ok**. **[Data tab >> Data Analysis >> Correlation]**

3.

Select the

**data range** (both independent and dependent variable) in the

**Input Range** box.

4. Check

**Labels in first row** and enter range in the

**Output Range** box and Click on

**Ok**.

**Interpretation: **

As you see the correlation between the grades and number of hours you spend studying is a very positive correlation (84%). This means as more number of hours students study their grades improve.

You can accomplish the same task using

**CORREL** function.

**Syntax: **
**=CORREL (range1, range2)**
For the above problem the syntax would be

*=CORREL(OFFSET($A$2:$A$500,,ROWS($1:1)-1),OFFSET($A$2:$A$500,,COLUMNS($A:A)-1))*
*For a detailed explanation of this formula , visit this link ***Formula Explained:Correlation Matrix**
**Do You Know? **
**How to deal with outliers when doing correlation?**
Solution: Look at the residuals from a regression by plotting the points to a scatter diagram. If they are not normally distributed around 0 the realibility of the Pearson correlation could be unreliable.

### It’s Your Turn!

*If you want me to keep writing this site, please post your feedback in the comment box below. While I love having friends who agree, I only learn from those who don't. ***To hire me for services or advertising, you may contact me at deepanshu.bhalla@outlook.com**

### Related Posts:

## 0 Response to "Correlation Matrix Using Excel"

## Post a Comment