Quantitative Methods Using Excel

4187 Words17 Pages
Calculate Pearson 's Correlation Using Microsoft Excel Correlation Analysis using Excel The correlation coefficient allows researchers to determine if there is a possible linear relationship between two variables measured on the same subject (or entity). When these two variables are of a continuous nature (they are measurements such as weight, height, length, etc.) the measure of association most often used is Pearson’s correlation coefficient. This association may be expressed as a number (the correlation coefficient) that ranges from –1 to +1. The population correlation is usually expressed as the Greek letter rho (r) and the sample statistic (correlation coefficient) is r. The correlation measures how well a straight…show more content…
Step1: In the Example worksheet, enter some labels in column I to indicate that you are calculating a correlation. Step 2: In the J3 (or wherever you want it) cell, you will enter an Excel function that will calculate the desired correlation. Step 3: Enter the formula =CORREL(C2:C51,D2:D51) Note that it is of the form, =CORREL(array1,array2) Where the first array and second array contain the paired numbers to correlate. It is IMPORTANT that the numbers be paired correctly.) The answer will appear in the cell. In this case, the Pearson’s correlation is 0.764 (rounded.) Example 3: Visualize the relationship between the two variable by displaying a scatterplot. Step 1: Select Insert/Chart (or click chart icon) Step 2: From the Chart Wizard step 1, select XY(Scatter) and select the top chart sub-type. Click Next. Step 3: Indicate the data range as =EXAMPLE!$C$1:$D$51 (either type it in or use the data selector to highlight the values in the spreadsheet – include the column names). Indicate that the data are in columns as shown here. Click Next. Step 4: Indicate an appropriate chart title and axis labels. Click
Open Document