Forecasting and Business Analysis
Writer Pro
copy_of_correlations.xlsIntroduction
| Forecasting and Business Analysis | |
| Copyright UPmarket Software Services. This file must not be used without permission. |
Correlation Analysis
Correlation models tell you how strong the linear relationship is between two variables. The statistic used is the coefficient of correlation denoted r (Rho). This is used mainly for understanding and can take values from -1 to +1 to measure the degree of association.
Y
X
Correlation Analysis
The chart on the left shows a simple relationship between X and Y. Correlation analysis can help show the strength of the relationship and also if the relationship is positive or negative.
Using Excel for Correlation Analysis
Excel can be used to calculate an individual correlation or a correlation matrix. The matrix is a table of correlations between a number of variables. This worksheet shows how to calculate a single correlation coefficient using the CORREL function in Excel and also how to create a correlation matrix using the Analysis ToolPak.
Correlation Example
| Value $ | Land Area | Rooms | Building Area | |
| $155,500 | 694 | 5 | 124 | |
| $160,000 | 465 | 5 | 134 | |
| $163,500 | 794 | 6 | 119 | |
| $172,000 | 696 | 5 | 120 | |
| $175,000 | 715 | 5 | 133 | |
| $212,000 | 634 | 8 | 234 | |
| $218,000 | 918 | 6 | 164 | |
| $225,000 | 695 | 8 | 204 | |
| $250,000 | 922 | 7 | 181 | |
| $265,000 | 801 | 7 | 158 | |
| $275,000 | 348 | 6 | 175 | |
| $310,000 | 695 | 7 | 220 | |
| Value $ | Land Area | Rooms | Building Area | |
| Value $ | 1 | |||
| Land Area | 0.00045 | 1 | ||
| Rooms | 0.60722 | 0.19927 | 1 | |
| Building Area | 0.70607 | -0.04193 | 0.86599 | 1 |
| 0.8659925633 |
The Excel Correlation Function in Analysis Tools
You can automatically calculate a correlation matrix in Excel using the Correlation function in the Analysis ToolPak. Open the ToolPak using Tools - Data Analysis. When you open the ToolPak you will see a long list of statistical methods that you can access. Go down the list and click Correlation. A screen will appear a little like that below. (It may vary depending upon the version of Excel you are using). The Input range is the data array of all input variables. If this includes a label in the first row of your data, you should check the Labels in First Row box. In this case the data is organised by columns. You need to select an output range where the output appears. When it is all entered - click Okay and the Correlation Matrix will be calculated.
The Excel Correl Function
The Correl function is CORREL(array1,array2). This returns a single correlation between array1 and array2. In cell 21B above is the function for calculating the correlation between Rooms and Building Area. Note that labels are not used - this is a mathematical function only. See HELP for details.
