Scatterplots and COD

No, we're not talking Scatterplots and Codfish... As was discussed about back in November of 2010 in this blog, Scatterplot (XY) Charts and the Coefficient of Determination (COD) function are powerful tools for some quick analysis. This combo truly should be in your "Bag of Excel Tricks"

A Scatterplot Chart is commonly used to show the relationship between two variables or sets of data. For example, a sales manager could plot the Number of Sales Calls Taken with the Number of Sales Made. Another example is comparing the Average Length of Time a customer service representative takes per call and the Overall Quality Score of their calls.

To determine how strong the correlation is between the sets of data, wily Excel users such as yourself can make a Scatterplot Chart and:

1. Right-click on one of the data points and
2. Choose Add Trendline
3. Right-click the Trendline and choose Format Trendline
4. Format the Trendline to your aesthetic preferences and
5. Put a Checkmark next to Display R-squared Value on Chart

The R-Squared value is your Coefficient of Determination (COD) that will tell you how strong your data on your two axes. In the graph example above the COD value is .5574 (or approximately 56%) representing a Strong Correlation (and therefore reliable).

Wow! That's all there is to it! Try using a Scatterplot and Coefficient of Determination sometime when seeking the strength of a correlation of data sets. It’s remarkably easy and you can reveal valuable information in the blink of an eye!