How do you calculate r-squared in Excel?
R-squared is a statistical relationship between two series of events. It is also a risk measure that is used in economics to represent the amount of an investment movement that can be attributed to movements in its benchmark index. In statistics. the probability of change in the dependent variable y is predicted against that of independent variable x.
Excel uses several data analysis tools and the R2 formula to find the correlation between two variables. The RSQ syntax requires a range of data points named x and y. Thus, you will be correlating numbers of one column against another.
Open the Microsoft Excel page with the data that you need to correlate. Name one column "x" and the other "y". Select an empty cell below the columns that will contain the
correlation coefficient R, once calculated. Create a label above it to distinguish it from other data cells.
Select the cell you created earlier and put an equal sign. Go to the Formula tab, select Function Library Group, and then More Function and Statistical. Select Correlation. A dialog box will pop up asking you to infill the range. In array one, fill in the number of variable (named x) as (A1: A8) with A1 being the first value and A8 as the last value. Repeat the procedure for array two as (B1:B8). Close the dialog box by clicking OK. The result will be displayed in the cell.
Empty cells and those with text are ignored. Both values of x and y should be equal in number and greater than one in number.Source: www.investopedia.com