How to use multiple regression in excel

Written by shane hall
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to use multiple regression in excel
Use Excel to run advanced data analysis. (John Foxx/Stockbyte/Getty Images)

Multiple regression analysis is an advanced statistical technique that uses more than one predictor, or independent variable, to examine the effects on a single outcome, or dependent variable. For example, a multiple regression model might examine average salaries (dependent variable) as a function of age, education, gender and experience (independent variables). Multiple regression calculates coefficients for each independent variable. The coefficient estimates the effect of a particular variable while holding constant the effects of other variables. Excel, the spreadsheet program in Microsoft’s popular Office software package, includes data analysis tools that enable it to conduct multiple regression analysis.

Skill level:
Moderate

Other People Are Reading

Instructions

  1. 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. 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. 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. 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. 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.

Tips and warnings

  • Because multiple regression requires more than one independent variable, the click-and-drag feature in Excel that specifies the values to be included in your regression analysis requires your independent variables to be in adjacent columns.
  • Remember that statistical significance does not prove causation.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.