What is the procedure for performing a regression in Microsoft Excel? Highlight the input ranges and then enter the correct formula for regression Click on the Data Tab in Excel and then use the Data Analysis tool to fill in a dialog box pertaining to regression. Choose the output cell and enter : =regression(A1:B10) Regression cannot be computed in Excel; you must computer multiple other functions and that imply the results of a regression.
Answers
Answer:
Select "Data" from the toolbar. The "Data" menu displays.
Select "Data Analysis". The Data Analysis - Analysis Tools dialog box displays.
From the menu, select "Regression" and click "OK".
In the Regression dialog box, click the "Input Y Range" box and select the dependent variable data (Visa (V) stock returns).
Click the "Input X Range" box and select the independent variable data (S&P 500 returns).
Click "OK" to run the results.
[Note: If the table seems small, right-click the image and open in new tab for higher resolution.]
Interpret the Results
Using that data (the same from our R-squared article), we get the following table:
The R2 value, also known as the coefficient of determination, measures the proportion of variation in the dependent variable explained by the independent variable or how well the regression model fits the data. The R2 value ranges from 0 to 1, and a higher value indicates a better fit. The p-value, or probability value, also ranges from 0 to 1 and indicates if the test is significant. In contrast to the R2 value, a smaller p-value is favorable as it indicates a correlation between the dependent and independent variables.
Charting a Regression in Excel
We can chart a regression in Excel by highlighting the data and charting it as a scatter plot. To add a regression line, choose "Layout" from the "Chart Tools" menu. In the dialog box, select "Trendline" and then "Linear Trendline". To add the R2 value, select "More Trendline Options" from the "Trendline menu. Lastly, select "Display R-squared value on chart". The visual result sums up the strength of the relationship, albeit at the expense of not providing as much detail as the table above.