How do I plot the correlation coefficient within a graph?

Question

How do I plot the correlation coefficient within a graph?

Anonymous

I am trying to plot costs against output in Euros, and, evaluate whether this sample of data indicates that output affects costs, and which I hope to have accurately plotted within excel.

A visual confirmation (for simplicity) might be attained by trying to plot output and total costs as linear graphs in order to review the trendline, however, with the default settings excel has only managed to plot output correctly, with the total costs showing a straight line. This is incorrect when you review the Output values.

How can I adjust the graph to plot the trendlines? Is an online tool or function available to confirm my correlation analysis?

The graph is available at http://https://skydrive.live.com/view.aspx?cid=B712073B3513EB8E&resid=B712073B3513EB8E%211489, and, I have tried using Office 2010 that is available within Skydrive, and, also Office 2007, hence answers for any or both platforms would be appreciated.

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

2 additional answers

Answer 1

Arrange data with horizontal X data (output) in a column on the left and vertical Y data (costs) in an adjacent column on the right. Select all numerical data (two columns wide by number-of-observations rows). Insert an XY Scatter chart type. Visually confirm that a linear (or other) relationship is appropriate. Click one of the data points to select the data series. Right-click and choose Add Trendline from the shortcut menu, and select the appropriate trend/regression type.

- Mike Middleton

TreePlan Software, http://www.TreePlan.com

Decision Tree, Sensitivity, and Simulation Add-Ins for Excel

Answer 2

(1) You are not using a correct formula for calculating linear correlation. You can use Excel's built-in functions, CORREL or PEARSON, to calculate the correlation coefficient, R (approximately 0.94 for your data).

(2) You are using a Line chart type. You should use an XY Scatter chart type (as I suggested in my original reply to your posting).

(3) If you use the XY Scatter chart type, you can then use the Add Trendline feature, and you can choose to display R-squared and the trendline equation. Or, you can use the RSQ worksheet function, which yields 0.89 for your data.

Loose Interpretation: Approximately 89% of the variation in total costs can be explained by output using a linear model.

- Mike Middleton

TreePlan Software, http://www.TreePlan.com

Decision Tree, Sensitivity, and Simulation Add-Ins for Excel