How to Measure R2 of Two Lines in Excel

Techwalla may earn compensation through affiliate links in this story. Learn more about our affiliate and product review process here.
Correlate as many factors as possible to see what drives your profit.
Image Credit: Comstock/Comstock/Getty Images

Correlation is the amount of similarity between two sets of data. The coefficient of correlation R ranges between -1 and 1, with 0 showing no correlation, 1 showing perfect correlation and -1 being perfect anti-correlation. R-squared -- ranging between 0 and 1 -- is often used to report the similarity between two sets of data. In business analysis, correlation can tell you the strength of the relationship between a factor and a results. For example, marketing spending and sales may be strongly correlated and have a high R-squared value. The correlation coefficient can be simply calculated in Microsoft Excel.

Advertisement

Step 1

Open an existing spreadsheet in Microsoft Excel containing the data you wish to correlate. You will be correlating the numbers in one data column to another.

Video of the Day

Step 2

Select an empty cell to contain the calculated correlation coefficient R. Create a label above this cell to distinguish it from data cells.

Step 3

Enter the formula "=CORREL(array 1,array 2)" in the formula box with the Correlation Coefficient cell selected. Change "array 1" and "array 2" to the corresponding ranges of the two sets of data you wish to correlate. Note that these must be of the same length. For example:

Advertisement

Advertisement

=CORREL(A2:A7,B2:B7)

Step 4

Select a new cell to contain the R-squared value calculated from the R value. Enter the formula "=POWER(cell,2)" into the formula box with the cell selected, changing "cell" to the cell containing the correlation coefficient. For example:

=POWER(C2,2)

Video of the Day

Advertisement

Advertisement

references