Adding-In the Data Analysis Toolpack to Excel 2007 and 2003

ADDING-IN THE DATA ANALYSIS TOOLPACK TO EXCEL

Statistical analysis such as descriptive statistics and regression requires the Excel Data Analysis add-in.
The default configuration of Excel does not automatically support descriptive statistics and regression analysis.
You may need to add these to your computer (a once-only operation).

Excel 2007: The Data Analysis add-in should appear at right-end of Data menu as Data Analysis.
If not then

1. Click the Microsoft Office Button , and then click Excel Options.
2. Click Add-Ins, and then in the Manage box, select Excel Add-ins.
3. Click Go.
4. In the Add-Ins available box, select the Analysis ToolPack check box, and then click OK.
   Tip If Analysis ToolPack is not listed in the Add-Ins available box, click Browse to locate it.
   If you get prompted that the Analysis ToolPack is not currently installed on your computer, click Yes to install it.
5. After you load the Analysis ToolPak, the Data Analysis command is available in the Analysis group on the Data tab.
6. If you have trouble see: http://office.microsoft.com/en-us/excel/HP100215691033.aspx:

Excel 2003: The Data Analysis add-in should appear in the Toools menu.
If not then

1. On the Tools menu, click Add-Ins.
2. In the Add-Ins available box, select the check box next to Analysis Toolpack, and then click OK.
   Tip If Analysis Toolpack is not listed, click Browse to locate it.
3. If you see a message that tells you the Analysis Toolpak is not currently installed on your computer, click Yes to install it.
4. Click Tools on the menu bar. When you load the Analysis Toolpak, the Data Analysis command is added to the Tools menu.
5. If you have trouble see http://office.microsoft.com/en-us/excel/HP011277241033.aspx

Regression with Microsoft Excel 2007

This is from http://cameron.econ.ucdavis.edu/excel/excel.html
Klik pada gambar untuk melihat lebih jelas.

TWO-VARIABLE LINEAR REGRESSION

The population regression model is: y = β₁ + β₂ x + u

We wish to estimate the regression line: y = b₁ + b₂ x

REGRESSION USING THE DATA ANALYSIS ADD-IN

This requires the Data Analysis Add-in: see Excel 2007 and 2003: Access and Activating the Data Analysis Add-in

The data used are in carsdata.xls

• In the Data Group select the Data Analysis Add-in
• Select Regression Analysis
  

We select OK and fill out the dialog box as follows















We obtain





INTERPRETING THE REGRESSION SUMMARY OUTPUT

The key output is given in the Coefficients column in the last set of output:

• b₁ = 0.8 (the Intercept coefficient)
• b₂ = 0.4 (the Coefficient of HH SIZE : the slope coefficient)
  

Thus the fitted line is: y = 0.8 + 0.4 x
or CARS = 0.8 + 0.4 HHSIZE

The regression statistics outyput gives measures of how well the model fits the data. In particular

• R² = 0.8 which measures the fit of the model
  This means that 80% of the variation of y_i around ybar is explained by the regressor x_i
• Standard error = 0.365 which measures the standard deviation of y_i around its fitted value.

The remaining output (ANOVA table and t Stat, p-value, .... ) is used for statistical inference.

Regression with Microsoft Excel 2003

Here is step by step to regress with Microsoft Excel from Jeremy Miles

To do regression in Excel, you need the Analysis Toolpak add-in to be installed in Excel. This was an option when you installed Excel, but you might not have selected it. If you didn't install it, Excel will ask you for the CD, when you try to add the toolpak.

Check that the add-in is installed, and added-in, by choosing Add-ins from the tools menu (as shown below).



Then ensure that "Analysis ToolPak" is selected, as shown below.


You can now use the data analysis functions in Excel, which include multiple regression.
The example that we will work through is taken from dataset 6.1b in the book "Applying regression and correlation" (if you jumped straight in here, that is what these web pages is about. Don't worry about that though, you can download the data from here .)

To get to the data analysis function in Excel, you select the Tools menu, and then choose Data Analysis.

This gives the following Dialog, click on Regression and then click OK.



The following dialog appears:



In here, we tell Excel about the data that we would like to analyze.
The first box is the input Y range. Here, we tell Excel about our dependent variable. The dependent variable must be a column, 1 cell wide, and N cells long (where N is the number of individuals that we are analyzing).
The the dataset we are using, the dependent variable is Anx, which is the column which goes from cell D1 to Cell D41. You can either type this information in directly as D1:D41, or you can select the appropriate data from the spreadsheet.

Because we have included row 1, which includes the variable name, we are going to have to tell Excel this, by clicking on the "Labels" check box.

The next stage is to input the independent variables. The independent variables must be a block of data, of k columns (where k is the number of independent variables) and N rows (where N is still the number of people). In the dataset we are using we have three independent variables: hassles, hassles2 and hassles3. (These represent the linear, quadratic and cubic effects of hassles - we are analyzing a non-linear relationship here,) These are held in rows 1 - 41 of columns A, B and C. Again, we can type in A1:C41 or select the data from the spreadsheet - it will have the same effect.

Next we tell Excel where we want the results to be written. It is best to ask for a new sheet - you don't want to accidentally overwrite some of your precious data, and have to go to all of the effort of restoring it from a backup, do you? (You do have a backup, don't you?)

We can ask fro residuals and standardized residuals to be saved - these will be new columns of numbers created in the the new spreadsheet.


Two types of graphs will be drawn automatically if you ask for them.

• A residual plot will draw scatter plots of each independent variable on the x-axis, and the residual on the y-axis.
• A line fit plot will draw scatter plots of each independent variable on the x-axis, and the predicted and actual values of the dependent variable on the y axis.

You cannot, as far as I have been able to determine, automatically have

• A scatter plot with the predicted values on the x-axis, and the residuals on the y-axis (although you can calculate these values and save them.)

You can also request a normal probability plot. This appears to be a plot of the dependent variable, which is a curious thing to plot - regression analysis does not assume normal distribution of the dependent variable. The usual plot of this type would be the residuals, but this is not possible in Excel.

The dialog box now looks like this:


.
So, finally, we click OK.

And we get a lot of output, written to a new sheet. A note about this output - output from analysis in Excel is usually "live" that is to say, the data are linked to the output. If you change the data, you will change the output. This is not the case for this type of output in Excel. The results of the analysis are "dead" and will not change.

The Results

The results presented below have been copied from Excel to HTML, so they don't look identical. You can download the Excel file exactly as Excel wrote it for me here . Note that the data are not included in this file (it's an Excel 4.0 file, to make it usable by as many people as possible).

Regression Statistics

Multiple R	0.807582
R Square	0.652189
Adjusted R Square	0.623205
Standard Error	6.686129
Observations	40

The first part of the output is the regression statistics. These are standard statistics which are given by most programs.

ANOVA

ANOVA
	df	SS	MS	F	Significance F
Regression	3	3017.745	1005.915	22.50151	2.22E-08
Residual	36	1609.355	44.70432
Total	39	4627.1

The ANOVA table comes next. This gives a test of significance of the R². Note that Excel uses scientific notation, by default, so when it says 2.22E-08 it means, 2.22 * 10^-8 . (i.e. 0.0000000222).


Coefficients

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	12.39	3.90	3.18	0.00	4.48	20.29	4.48	20.29
HASSLES	-0.06	0.34	-0.19	0.85	-0.75	0.62	-0.75	0.62
HASSLES2	0.00	0.01	0.06	0.95	-0.02	0.02	-0.02	0.02
HASSLES3	0.00	0.00	0.51	0.61	0.00	0.00	0.00	0.00

The next stage is the coefficients. Note that here I have converted the numbers to 2 decimal places to save space). It gives the coefficient for each parameter, including the intercept (the constant). The standard errors, and the t-values follow (the t-value is the coefficient divided by the standard error). Next comes the p-value associated with the variable, and the confidence intervals of the parameter estimates (Excel gave these to me twice, even though I didn't ask for them.)

Residuals

Observation	Predicted ANX	Residuals	Standard Residuals
1.00	12.08	-2.08	-0.32
2.00	11.82	0.18	0.03
3.00	15.98	5.02	0.78
4.00	29.86	-13.86	-2.16
5.00	28.56	-1.56	-0.24
6.00	34.16	-4.16	-0.65
7.00	12.32	-3.32	-0.52
8.00	12.37	-5.37	-0.84
9.00	27.31	4.69	0.73
10.00	15.64	-4.64	-0.72

The final part of the output is the residual information. The observation in the left had column is the case number - although Excel never told us about this, it has labeled the first person Observation 1, the second Observation 2, etc. (Note that this is NOT the original row number - Observation 1 was row 2). The predicted anxiety score is the score that was predicted from the regression equation. The residual is the raw residual - that is the difference between the predicted score and the actual score on the dependent variable. The final value is the standardized residual (the residuals adjusted to ensure that they have a standard deviation of 1; they have a mean of zero already).

A Note to End On
I have written this file which shows you how to do regression in Excel, but this does not mean that I think that you should be doing regression in Excel. Regression in Excel has a number of shortcomings, which include:

• No standardized coefficients. It can be very difficult to interpret unstandardized coefficients. You could calculate the standardized coefficients using the unstandardized coefficients, if you really wanted to. But you could have done the regression on your own, if you really wanted to.
• Lack of diagnostic graphs. The standard diagnostic graphs are not available in Excel, e.g. the normality plot of the residuals, the scatter plot or residuals against predicted values. Again, you can work them out, but it ain't easy.
• Lack of Diagnostic statistics. There are no collinearity diagnostics, which would help you to understand what was happening in the data that we analyzed above (highly significant R², but no significant parameters in the model). You could calculate the skew statistics, but not if you have more than 30 cases.
• Lack of features. There is no hierarchical regression, no weighting cases, etc, etc, etc...
• Inflexibility. If you want to run a slightly different analysis, it is hard work, because you have to move your data around, a process which is prone to errors.