Chapter 10, Using Excel: Correlation and Regression

[Pages:4]1

Chapter 10, Using Excel: Correlation and Regression

? Correlation and Regression with just Excel.

Play Video

This video shows you how to get the correlation coefficient, scatterplot, regression line, and regression equation. These tasks do not require the Analysis ToolPak or StatPlus:mac LE. Everything can be done easily with the out-of-the-package copy of Excel. It's one of Excel's best statistical features.

? Additional Regression Information with the Analysis ToolPak (Windows Users)

2

? Additional Regression Information with StatPlus:mac LE (Mac Users)

3

? Getting and Running the Analysis ToolPak and StatPlus LE

4

2

? Additional Regression Information with the Analysis ToolPak (Windows Users)

Correlation coefficient(s) with the Analysis ToolPak. 1. Data tab Data Analysis Correlation 2. Highlight all the columns containing variables you suspect are correlated. 3. Check Labels in First Row only if you highlighted the top row labels. 4. Click the Output Range: and select a place for the output by clicking on a cell. It will create an n x n array where n is the number of variables (columns). 5. Click OK and this will produce an array of correlation coefficients between all of the variables represented by the columns. It's good for seeing which of many variables are most strongly correlated.

The regression option with the Analyis ToolPak: 1. Data tab Data Analysis Regression 2. Enter the X and Y data by highlighting it. 3. Check labels only if you highlighted the top row labels. 4. Don't check constant is zero or confidence level. 5. Click the Output Range: and choose an output location by clicking on a cell. 6. Click OK and you get results like those below.

? Multiple R is the correlation coefficient = r and R-square = r2. ? Adjusted R-square is a more appropriate value when the data comes from a sample. ? Standard Error is the standard error used in calculating a prediction interval of y for a given

value of x. ? Significance F denotes the P -value of the test statistic used in a hypothesis test with H0 : = 0.

If it is less than , we reject the null hypothesis and conclude there is a linear correlation between the population variables. ? Coefficients give the y-intercept and the slope of the regression equation.

3

? Additional Regression Information with StatPlus:mac LE (Mac Users)

1. Statplus Statistics Regression Linear Regression 2. Select the dependent variable data (Y data) 3. Select the independent variable data (X data) 4. Check Labels in the first row if that is the case. 5. Click OK and you get results like the ones below.

? R is the correlation coefficient = r and R Square = r2. ? Adjusted R-square is a more appropriate value when the data comes from a sample. ? Standard Error is the standard error used in calculating a prediction interval of y for a given

value of x. ? p-level denotes the P -value of the test statistic used in a hypothesis test with H0 : = 0. If it

is less than , we reject the null hypothesis and conclude that there is a correlation between the population variables. ? Coefficients give the y-intercept and the slope of the regression equation.

4

? Getting and Running the Analysis ToolPak and StatPlus LE

? Installing Analysis ToolPak (Windows Only) 1. Open a blank Excel spreadsheet. 2. Click on the windows icon (pre 2010) or the file tab (2010+). 3. Choose Excel Options (pre 2010) or just options (2010+). 4. Choose add-ins. 5. In manage (bottom of window), choose Excel Add-ins and click Go. 6. Check the box that says Analysis ToolPak and click OK. 7. After you load the Analysis ToolPak, the Data Analysis command is available under the Data tab. It should be the far right option.

? Getting StatPlus LE (Mac Only) As of this writing, if you are running Excel 2008 or higher on a Mac, the Analysis ToolPak is not available. There is an application called StatPlus:mac LE which is a free version of the full StatPlus application. It can handle most of the tasks performed by the Analysis ToolPak and the full version is probably superior - but that costs money.

Once you download the software from , 1. Run StatPlus from the Applications Folder. 2. If you don't already have Excel open, it will open it for you. 3. You will run the commands from the StatPlus menu (top menu bar). 4. Choose the appropriate cells from the Excel worksheet containing the data. 5. The results are printed to a separate Excel worksheet. 6. You can then cut and paste these results in the Excel Worksheet that contains the data.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download