Friday, July 27, 2012

How to Use Multiple Regression in Excel


Excel for Multiple Regression
1. Enter the data you will use to conduct your regression analysis into an Excel spreadsheet. You can enter the data by hand or import a data file from another source, such as an ASCII file or another spreadsheet, into Excel.
2. Unlock the Data Analysis tool from the add-ins menu and install it. Open Excel, click “Tools” and select “add-ins” from the drop-down menu that appears. A smaller window opens that displays a set of options. Check the box next to “Analysis ToolPak” and click “OK.” The Data Analysis option appears in your Tools menu, ready for use. If you are using Excel 2007, you can access the Data Analysis add-in by clicking the Microsoft Office button in the top left corner of an open Excel workbook. Click the button and then click “Excel Options.” A new window opens, displaying a set of options on the left side. Choose “Add-ins,” select “Analysis ToolPak” and click “OK.”
3. Click the Tools menu in Excel and select Data Analysis (in Excel 2007, click the “Data” tab and click the Data Analysis button). A window opens that displays a menu of analysis tools. Scroll to “Regression” and click “OK.”
4. Enter the values for dependent variable (Y) and independent variables (X) by clicking on the applicable cells and columns in your Excel data sheet. After selecting the ranges of data to be entered for analysis, click 'OK.' Excel runs the procedure and display your results on a new worksheet.
5. Examine your summary output, starting with the regression statistics at the top of your output. Note the value of R-square, which tells you what percentage of the variability in the dependent variable (for example, average salaries) is explained by your regression model. Then note the values of the coefficients and corresponding t-statistics and significance levels. A t-statistic of 2 or greater indicates statistical significance, meaning that the relationship between that independent variable and the dependent variable is likely not due to random chance.

Blogger news