EXCEL REGRESS Paula Ecklund Handouts Excel Regress AddIn

  • Slides: 46
Download presentation
EXCEL & REGRESS Paula Ecklund Handouts Excel Regress Add-In: Skill Building & Quick Guide

EXCEL & REGRESS Paula Ecklund Handouts Excel Regress Add-In: Skill Building & Quick Guide Materials on Website http: //faculty. fuqua. duke. edu/~pecklund/WEMBA/Regress. htm Rev. 3 -26 -08

Agenda What is regression? What is the Regress Excel add-in software? A brief look

Agenda What is regression? What is the Regress Excel add-in software? A brief look at Excel’s built-in regression. See how the Regress software operates. Prepare data Use the interface Run the regression Manage the outputs Return to Contents

What is a regression analysis? Regression A way to compare variables and make predictions.

What is a regression analysis? Regression A way to compare variables and make predictions. A regression models the relationship between variables. One set of variables is used to explain the other. These are called the independent variables. The single variable being explained. This is called the dependent variable. Return to Contents

For example Can the square footage of a house, the number of bedrooms, and

For example Can the square footage of a house, the number of bedrooms, and the average neighborhood income be used to predict the price for which a house is sold? Return to Contents

For example Can the square footage of a house, the number of bedrooms, and

For example Can the square footage of a house, the number of bedrooms, and the average neighborhood income be used to predict the price for which a house is sold? The independent variables (possible predictors) Return to Contents

For example Can the square footage of a house, the number of bedrooms, and

For example Can the square footage of a house, the number of bedrooms, and the average neighborhood income be used to predict the price for which a house is sold? The dependent variable (the predicted) Return to Contents

The Regress software facilitates exploratory regression analysis Preliminary results are quickly available. Statistics, summary

The Regress software facilitates exploratory regression analysis Preliminary results are quickly available. Statistics, summary table, correlation matrix. Easily add variables to (or drop variables from) the regression. Save and build on interesting results or discard unpromising ones. Select from a variety of results reports. Return to Contents

This session We will cover: ü ü ü Preparing data for Regress Understanding the

This session We will cover: ü ü ü Preparing data for Regress Understanding the Regress interface Selecting and managing Regress outputs We will not cover: r r r Installation issues All available Regress options The interpretation of Regress outputs Return to Contents

This session Need installation help? Contact Fuqua’s WEMBA computer support group. Want to know

This session Need installation help? Contact Fuqua’s WEMBA computer support group. Want to know about every available Regress option? Download the author’s complete guide. Have a question about the regression results? Ask Dr. Jill Stowe in your statistics class. Doctor Jill Return to Contents

Why use Regress instead of Excel’s built-in regression*? 1. Regress is integrated into Excel

Why use Regress instead of Excel’s built-in regression*? 1. Regress is integrated into Excel (as an add-in); easyto-use; no need to learn a new application (like Statgraphics). Note: Regress is intended as a teaching tool. It’s limited to 16 variables and 250 cases. 2. Regress uses any named data ranges (where Excel requires contiguous data). 3. Regress produces a wider variety of reports than Excel. 4. Regress is particularly useful for exploratory regression. * Part of Excel’s Analysis Tool. Pak: Tools, Data Analysis, Regression Return to Contents

A quick look at Excel’s built-in regression Menu command: Tools, Data Analysis Return to

A quick look at Excel’s built-in regression Menu command: Tools, Data Analysis Return to Contents

A sample of Excel’s regression output. Return to Contents

A sample of Excel’s regression output. Return to Contents

This look at Regress The mechanics of using the Regress software Sample data sets:

This look at Regress The mechanics of using the Regress software Sample data sets: Smyth. Pie. Data. xls (session demonstration) Calculus. xls (for your own practice) Return to Contents

Sample data & complete guide http: //faculty. fuqua. duke. edu/~pecklund/WEMBA/Regress. htm The sample data

Sample data & complete guide http: //faculty. fuqua. duke. edu/~pecklund/WEMBA/Regress. htm The sample data sets and the author’s complete Regress users guide are available for download from this web address: Return to Contents

Supporting & practice files Data files Smythe. Pie. Data. xls Calculus. Complete. xls Quick

Supporting & practice files Data files Smythe. Pie. Data. xls Calculus. Complete. xls Quick Guide Excel Regress Add-In: Skill-Building & Quick Guide (you have on paper) Complete User’s Guide (by the Regress author) Regress. Authors. Guide. doc Return to Contents

Regress Demo Return to Contents

Regress Demo Return to Contents

Step 1: Plan Smyth. Pie. Data. xls is an Excel file of pie sales

Step 1: Plan Smyth. Pie. Data. xls is an Excel file of pie sales data. The variables in the data: 1. 2. 3. 4. 5. 6. 7. Quantity (quantity sold) Price Ads (advertisements) Comp_Price (competitor price) Income Population Time 48 rows & 7 columns. Return to Contents

Step 1: Plan The question: Does advertising increase sales? Quantity = Quantity Sold =

Step 1: Plan The question: Does advertising increase sales? Quantity = Quantity Sold = Sales Return to Contents

Step 1: Plan The regression analysis Variables to use: ► the dependent variable =

Step 1: Plan The regression analysis Variables to use: ► the dependent variable = quantity (of pie sales) ► the independent variables = ads, price, comp_price, income, population, time Return to Contents

Step 2: Prepare the data set Make sure that: all variables have the same

Step 2: Prepare the data set Make sure that: all variables have the same number of elements (the data is a rectangular range) Return to Contents

Step 2: Prepare the data set Make sure that: all variables have the same

Step 2: Prepare the data set Make sure that: all variables have the same number of elements (the data is a rectangular range) all variables are numeric The variable data ranges are named Typical requirements. A requirement specific to the Regress software. Do NOT include range header names as part of the named ranges! (What happens if you do? ) Return to Contents

This highlighted range includes the column label “Quantity”. If you include this label in

This highlighted range includes the column label “Quantity”. If you include this label in a range name you supply to a Regress operation, Regress will choke because the “Quantity” label is text not a number. Return to Contents

Another possible range naming trap This highlighted range is the entire column. Regress will

Another possible range naming trap This highlighted range is the entire column. Regress will choke because it can’t handle that many cells (and many of the cells in the range are probably empty). Return to Contents

Two ways to name ranges in Excel 1. Menu Commands Highlight the entire range

Two ways to name ranges in Excel 1. Menu Commands Highlight the entire range of data, including the headers in the top row*. From the menus choose Insert, Name, Create. Excel names each column of data using the name in the header row. Note: The column’s header row cell is not included as part of the named range. 2. Formula Bar Highlight a column of the data range not including the column label cell. In the Formula Bar’s “name box”, enter a name for the range. Hit the Enter key. Repeat for each column of data. * Select any cell in the range. Then hit CTRL+SHIFT+8 to select the entire range. Return to Contents

Name the piedata ranges The Insert, Names, Create method is the fastest way to

Name the piedata ranges The Insert, Names, Create method is the fastest way to name this data since the data is together and already has headers. Return to Contents

Check the names Then start the regression. Return to Contents

Check the names Then start the regression. Return to Contents

Step 3: Invoke Regress Note: Fuqua. Net’s installation of Regress requires a different start

Step 3: Invoke Regress Note: Fuqua. Net’s installation of Regress requires a different start method. Menu command: Tools, Regress A few seconds of the Regress “Splash Screen” will display. . . Return to Contents

Step 3: Invoke Regress The Regress Toolbar displays Start a regression analysis. Return to

Step 3: Invoke Regress The Regress Toolbar displays Start a regression analysis. Return to Contents

While you start Regress… If you encounter this dialog, you must click the “Enable

While you start Regress… If you encounter this dialog, you must click the “Enable Macros” button for Regress to run. Return to Contents

Step 4: Define the Regression Select the Independent and Dependent variables from the drop-down

Step 4: Define the Regression Select the Independent and Dependent variables from the drop-down lists of named ranges in the dialog. Return to Contents

Step 5: Run the regression (or Choose options first & then run) Return to

Step 5: Run the regression (or Choose options first & then run) Return to Contents

Step 6: Choose Reports Return to Contents

Step 6: Choose Reports Return to Contents

Step 7: Manage Reports Use the plus and minus icons at left just as

Step 7: Manage Reports Use the plus and minus icons at left just as you would in Windows Explorer to display or hide report elements. Return to Contents

Further exploration. . . At this point, you might want to rerun the regression

Further exploration. . . At this point, you might want to rerun the regression adding a new variable, dropping a variable, changing a variable value, providing data for a nonlinear regression, etc. Keep results you want to save by providing a new name in the “Regression Results” dialog. Return to Contents

Regress helps keep track of versions of your analysis If you’re about to overwrite

Regress helps keep track of versions of your analysis If you’re about to overwrite a previous analysis, Regress displays this warning/options dialog. You also have these choices: 1. Append new results. 2. Overwrite old results with the new results. Return to Contents

Continue running exploratory regression analyses. . . Save as many versions of the regression

Continue running exploratory regression analyses. . . Save as many versions of the regression as you like. Return to Contents

Regress Notes & Tips Return to Contents

Regress Notes & Tips Return to Contents

Regress Notes & Tips Limits on the data set size Results 16 variables and

Regress Notes & Tips Limits on the data set size Results 16 variables and 250 records. Regress results are static. Regress text results are not dynamically linked to the data. Regress chart results are not dynamically linked to the data. If you change data values, you must re-run the regression to see new results. Formatting Because the results environment is an Excel worksheet you can reformat the results in any way you like. Return to Contents

More notes/tips Missing values A record with one or more missing values is ignored

More notes/tips Missing values A record with one or more missing values is ignored in the regression processing. A missing value is a completely empty cell. A zero in a cell is not considered a missing value. A space in a cell (“ “) is seen as a text value, and cannot be used in the regression. For a complete discussion, see the author’s user’s guide, p 46. Data location Simplify processing by keeping your data on a single sheet in a workbook. Return to Contents

Step 8: Outputs to Word For a Text report From Excel: SHIFT + Edit,

Step 8: Outputs to Word For a Text report From Excel: SHIFT + Edit, Copy Picture, As Shown on Screen Into Word XP: Edit, Paste For a Graphic report From Excel: Edit, Copy Into Word: Edit, Paste Return to Contents

To Quit Regress Removes Regress from the Excel workspace. Deletes the Regress toolbar. Re-establishes

To Quit Regress Removes Regress from the Excel workspace. Deletes the Regress toolbar. Re-establishes the Regress menu option on Excel’s Tools menu. Return to Contents

At the end of the tutorial in the author’s guide, the author writes: Good

At the end of the tutorial in the author’s guide, the author writes: Good luck with regression modeling and. . . “May the High R-Square Be With You. ” Return to Contents

For Practice with Regress Return to Contents

For Practice with Regress Return to Contents

A practice problem File: Calculus. xls Holds data about student courses, test scores, gender,

A practice problem File: Calculus. xls Holds data about student courses, test scores, gender, high school rank, etc. Return to Contents

The Calculus practice problem The question Can high school data predict performance in a

The Calculus practice problem The question Can high school data predict performance in a college class? Setup Name the data ranges. Dependent variable = “Term 1 Calculus Grade” Independent variables – all the other variables. Run the regression. Choose outputs. Rerun, stop, analyze, etc. Return to Contents

END of Regress Introduction Return to Contents

END of Regress Introduction Return to Contents