When you perform regression analysis, the coefficient of determination, noted R^2, tells how well the regression line predicts the actual data points. Excel 2013 provides a way to compute the R^2 value as well as display it on an XY chart.
Collect 12 months worth of marketing expense and sales data for your company and enter it into an Excel worksheet. You want to understand if your marketing efforts, represented by marketing expense, impact sales. In a formal regression analysis, you refer to the marketing expense as the independent variable, noted X, and the sales data as the dependent variable, noted Y.
Use Excel's RSQ function to compute R^2. The RSQ function has two arguments, the first represents the Y values and the second represents the X values. Assuming you store the sales values in range C9:C20 and the marketing expense values in range B9:B20, write the following formula in C5: =RSQ(C9:C20, B9:B20)
Press the Enter key on your keyboard and confirm the formula returns the R^2 value.
To chart the data, begin by selecting the marketing expense and sales data. Select the Insert > Scatter (X,Y) chart icon and pick the Scatter style.
Excel inserts the chart into the worksheet. To display the R^2 value on the chart, select the Chart Tools > Design > Add Chart Element > Trendline > More Trendline Options icon.
In the resulting Format Trendline panel, check the Display R-squared value on chart check box. Excel displays the R^2 value, and you can confirm it is the same value computed by the RSQ function.