Question

I need an example of how to calculate correlation in excel

Answer 1

Lets take an example data,

Plot with all the points in Excel

1. Put the data in excel as shown.

2. Highlight the data, go to the Insert tab , click on scatter plot and insert the scatter plot

3. The output chart will displayed as follows. We need to improve it futher by change the scale of the y axis.

4. Highlight y axis scale and right click on it and select Format axis from it.

5. In the menu that will follow only update the minimum value from 0 to 120, rest will get automatic adjusted.

6. Next we need to add the axis label and trend line.
for this click on the plus sign on the right top corner of the graph. Select axis label and trendline.
Now there will be labels put below the axis which you need to change by simply select and type the new name.

7. To display the equation of the line and the R2, we needed click on the plus sign, trendline and the further arrown and then more option.

8. In the menu, select Display Equation and Display Rsquare.

9. The formula to find the correlation is also shown

Equation : y = -0.115x + 168.17
R2 = 0.854
correlation = -0.9241

** Please comment.

***please ask if you have any doubts.Happy to help you.Thank you.Please Like.