# Excel 2007 Dataanalysis - Excel

Document Sample

```					Sheet: Directions                                     PredInt.xls                                          Page 1

PredInt.xls       Version 8.3 06 December 2007
Regression with Prediction Intervals
John.O.McClain@cornell.edu
Cornell University
Ithaca NY 14853
This spreadsheet is intended for teaching purposes. You are welcome to use it in any manner,
and change it as you see fit. This model comes without any guarantee whatsoever, and is
distributed free of charge.
New in Version 8: Compatibality with Excel 2007 and 2003
From earlier versions: Easy-to-copy Prediction Interval formulas,Line-fit Plots with y and y-hat,
Standardized residuals, outlier detection, residual plots and line-fit plots,
and a Normal Probability Plot to determine if residuals have the normal distribution.
Prediction Intervals are confidence intervals surrounding a predicted value of Y. The predicted
value of Y is obtained by plugging a set of X values into the linear regression equations, using
the coefficients obtained by doing a regression. For example, suppose you ran a regression that
explains demand as a function of price and advertising, and the result was:
Demand = 500 - 5*Price + 0.01*Advertising
If you wish to predict what demand would be if price = \$10 and advertising = \$20,000, plug them in:
Predicted Demand = 500 - 5*10 + 0.01*20,000 = 650
To quantify how accurate this prediction may be, a prediction interval is often used. Unfortunately,
the Excel regression tool does not have this feature.
To make prediction intervals available for my students, I wrote the PredictionInterval macro, which
is part of this workbook. It automatically adds a worksheet on which the analysis is displayed.

Step 1: Enter your data on a fresh worksheet.
A. Select an empty sheet, or add a new sheet using Shift-F11, or
 For Excel 2003, From the menu bar, select Insert, Worksheet
 For Excel 2007, on the Home tab, click on the down arrow next to Insert, and select Worksheet
B. Type the data manually, or copy/paste it from another document.
The following requirements must be satisfied:
C. The top row of your data should contain names for each of the variables.
Each row below that should contain values for all of the variables. A row is one observation.
D. All of the Variables need to be side-by-side. (That is, they have to be in adjacent columns.)
The "Dependent Variable", Y, (also called the Criterion Variable) may be in any column.
The usual arrangement is to put Y in column A, and the X variables in the remaining columns.
However, this arrangement is not necessary. The program will allow you to select any of
your variables as Y and X.
The figure below shows part of the data from the example on the GPA worksheet.

Dependent variable                Independent variables, X

GPA            Gender          Work          GMAT
4.15               0             0           680
3.76               1             4           630
3.02               0             8           650
2.9               0             3           590
2.96               0             9           660
4.22               0             1           610
3.76                1             4           630
3.02                0             8           650
2.9                0             3           590
2.96                0             9           660
Sheet: Directions                                       PredInt.xls                                                Page 2
4.22                0             1           610

Step 2: Run the PredictionInterval macro
A. If you have not already done so, open PredInt.xls.
B. Go to the worksheet where you entered the data in Step 1.
C. Locate the Prediction Interval macro as follows:
 For Excel 2003, From the menu bar, select Tools, Macro…
 For Excel 2007, Click on the View tab, and click the Macros button (far right)
D. From the list that appears, select PredictionInterval
Note, it might appear something like this: PredInt.xls!PredictionInterval
E. Click Run and follow the directions.
Part of the output for the GPA example is shown below. The rest is on the GPA Results worksheet.
The blue box allows you to set the confidence level . This is used in 3 places:
Confidence Intervals for each of the coefficients
Prediction Interval for a single future observation of Y (given particular values for the X variables)
Confidence Interval for the conditional mean of Y
If you go to the GPA Results worksheet and change the value in the blue box to, say, 0.9,
all of the intervals become narrower, and the headings change as well.
To the right, the yellow boxes are where you put values for the independent (predictor) variables , X.
The macro inserts the average values. Put in the values that you want to use to predict Y.
If you go to the GPA Results worksheet and change the values in the yellow boxes to (for example) 0 for
Gender, 5 for Work and 700 for GMAT, the predicted value for GPA will change, and so will the
confidence and prediction intervals.

Dependent (Criterion)        Coef-     Standard                P-value   Lower     Upper    X Values for
Variable: GPA            ficients      Error       t Stat   (2-tails)  95%       95%      Prediction
Intercept    2.2824953 0.467448 4.88289 2.71E-06 1.358657 3.206333
Gender     0.2559016 0.082582 3.098767 0.002332 0.092691 0.419112 0.253333333
Work      -0.046813 0.014787 -3.16581 0.001883 -0.07604 -0.01759               4.86
GMAT      0.0018435 0.000723 2.548962 0.011837 0.000414 0.003273 628.3333333
Confidence Level        Prediction Interval for a Single Observation            Predicted 3.278133338
0.95             of GPA, with the X Values that you                  Standard Error 0.437520136
enter in the yellow boxes.                             Lower 95% 2.413442329
Upper 95% 4.142824347
Confidence Interval for Expected GPA                           Fit 3.278133338
while holding X constant at the values that you     Standard Error 0.035604884
enter in the yellow boxes.                             Lower 95% 3.207765782
Upper 95% 3.348500895

Notice that the confidence interval is narrower than the prediction interval. This is always true.
There is more uncertainty in predicting a single outcome than in predicting a "large sample" average.

Preparing and Graphing Multiple Predictions:
You may copy the formulas in the column under "X Values for Prediction" and change the X values
to make a series of predictions. For example, on the GPA Results worksheet there are several copies
of that column, and the X values were changed to represent a male with varying GMAT scores.
This handy arrangement makes it easy to plot a series of predictions, together with the upper
and lower values corresponding to the level of confidence that you select.
Sheet: Directions                                      PredInt.xls                                                Page 3

Technical Notes:
There is very little error checking built into the macro, so if your data has problems the
routine could fail. In that case, you might want to run the Excel regression tool, and see if it gives
The Excel regression tool may be found in menu Tools, DataAnalysis…, Regression
which is available if you have installed two of Excel's Add-ins:
Analysis Toolpack and Analysis Toolpack (VBA).

Standardized Residuals & Outliers, Leverage & Influential Observations
Excel's regression tool prints standardized residuals, but they are not the same as the ones used in
MINITAB and other statistical packages. I have added standardized residuals to the output, matching
the ones in MINITAB.
Standardized residuals that exceed 2.0 are indicated as "outliers". If the regression assumption
is satisfied, approximately 5% of the observations should have residuals that large or larger. The output
now "flags" those observations with the word "outlier" in the last column of the residual output.
A term called "leverage" is used to detect "influential observations", those with leverage exceeding
3(p + 1)/2 in which p is the number of independent (predictor) variables. A term with high leverage
has the potential of having an undue influence on the estimates of the regression coefficients.
This is usually the result of having an unusually large value for one or more of the independent
variables. Those observations are flagged "influential" in the last column of the residual output.
Excel also produces residual plots and line-fit plots, which allow you to determine visually the
accuracy of the regression assumptions. However, the scales of those plots are not suitable for
accurate visual assessment. I have added plots of the standardized residuals and line-fit plots to
this program. The vertical axis of a residual plot is the standardized residual.
The vertical axis of a line-fit plot is the actual value of y, the dependent variable.
The output now includes two plots (residual and line-fit) for each independent (predictor) variable.
In addition, it includes the most commonly used residual plot, which uses the predicted (fitted) values
of y on the horizontal axis. Excel's regression tool does not produce that plot.

Normal Probability Plot
Excel's regression tool has an option to do a Normal Probability Plot. However, the plot that they use
is not the same as the ones in most statistical packages.
The Normal Probability Plot produced by this PredictionInterval macro is the same as you would
get from most programs. It includes a set of points and a straight line at about a 45 degree angle.
Ideally, the points should lie very close to the line. Large deviations from a straight line calls
into question the assumption that the residuals have the normal probability distribution. All of the
p-values are based on that assumption, as are the confidence and prediction intervals.
However, modest deviations from the normal distribution are not fatal. The statistics are fairly robust
to this assumption.
SUMMARY OUTPUT: Regression using PredInt.xls                                      Charts ==>
Regression Statistics                         from data in these columns,
Predicted GPA for a Male with 4 Years
Multiple R               0.41833                           which were produced by copying the                              Work Experience
R Square                    0.175                          "X Values for Prediction" column.
Adjusted R Square      0.1580479                           and entering new X values.                      5
Standard Error          0.436069      = Standard Deviation of Residuals                                    4
Observations                  150
3
ANOVA                                                                                                      2
df          SS      MS        F      Significance (p-value) for F             1
Regression                          3 5.889077 1.963026 10.32323 3.33E-06                                  0
Residual                          146 27.7628 0.190156 = Variance of Residuals                                 600    650      700       750
Total                             149 33.65188 0.225852 = Variance of Dependent Variable

Dependent (Criterion)        Coef-     Standard                P-value   Lower     Upper    X Values for
Variable: GPA            ficients      Error      t Stat    (2-tails)  95%       95%      Prediction
Intercept    2.2824954 0.467448 4.88289 2.71E-06 1.358657 3.206333
Gender     0.2559016 0.082582 3.098767 0.002332 0.092691 0.419112 0.253333333                  1           1             1
Work      -0.046813 0.014787 -3.16581 0.001883 -0.07604 -0.01759               4.86          4           4             4
GMAT      0.0018435 0.000723 2.548962 0.011837 0.000414 0.003273 628.3333333                 650         670           690
Confidence Level        Prediction Interval for a Single Observation            Predicted 3.278133333     3.549408    3.586278      3.623147
0.95             of GPA, with the X Values that you                  Standard Error 0.437520138    0.442001     0.44268       0.44383
enter in the yellow boxes.                             Lower 95% 2.413442324      2.675861    2.711389      2.745987
Upper 95% 4.142824342      4.422955    4.461166      4.500308
Confidence Interval for Expected GPA                           Fit 3.278133333    3.549408    3.586278      3.623147
while holding X constant at the values that you     Standard Error 0.035604884    0.072172     0.07622      0.082635
enter in the yellow boxes.                             Lower 95% 3.207765777      3.406771    3.435641      3.459833
Upper 95% 3.34850089       3.692045    3.736914      3.786462

Correlations of the Regression Coefficients
Intercept  Gender     Work    GMAT
Intercept           1 -0.04637 -0.23539 -0.98332
Gender -0.046375            1 0.128259 -0.01862
Work -0.235392 0.128259            1 0.078087
GMAT -0.983321 -0.01862 0.078087             1

Covariances of the Regression Coefficients
Intercept  Gender            Work     GMAT
Intercept 0.2185073 -0.00179            -0.00163 -0.00033
Gender     -0.00179 0.00682          0.000157 -1.1E-06
Work -0.001627 0.000157            0.000219 8.35E-07
GMAT -0.000332 -1.1E-06              8.35E-07 5.23E-07

Standardi
StDev of    Residual    StDev of
Observation Fit of GPA                                              zed      Leverage    Notes
Fit      y - yHat    Residual
Residual
1     3.5360631    0.090894   0.613937    0.426491    1.439508    0.043447
2     3.5125382    0.070899   0.247462    0.430267    0.575136    0.026434
3      3.106254    0.063115   -0.08625    0.431477      -0.1999   0.020948
4     3.2297105    0.058954   -0.32971    0.432066      -0.7631   0.018277
5     3.0778757    0.076719   -0.11788    0.429267      -0.2746   0.030953
6     3.3602063     0.07452   0.859794    0.429654    2.001129    0.029204 Outlier
7     3.3233366    0.079144   -0.15334    0.428827    -0.35757     0.03294
8     3.5947689    0.087699   0.135231    0.427159    0.316582    0.040446
9     3.3488108    0.057328   -0.13881    0.432284    -0.32111    0.017283
10     3.4756686    0.072546   -0.04567    0.429992    -0.10621    0.027677
11     3.5011427    0.096929   -0.22114     0.42516    -0.52014    0.049408
12     3.1161974    0.050451     -0.3262   0.433141      -0.7531   0.013385
13     3.1360843    0.049362   -0.11608    0.433266    -0.26793    0.012814
14     3.0240233    0.059656   -0.06402    0.431969    -0.14821    0.018716
15     3.6699602     0.08418   -0.42996    0.427867    -1.00489    0.037266
16     3.5877296    0.079242   -0.25773    0.428809    -0.60104    0.033022
17     3.2813769    0.101513   -0.33138    0.424089    -0.78139    0.054191
18     3.3786411    0.073178   0.011359    0.429885    0.026423    0.028161
19     3.3388673     0.06665   0.101133    0.430945    0.234676    0.023361
20   3.2736194   0.063564   0.026381    0.431411   0.061149    0.021247
21   3.2013322   0.048785   -0.33133    0.433331   -0.76462    0.012516
22   3.3786411   0.073178   0.241359    0.429885    0.56145    0.028161
23   3.4089686   0.082817   -0.51897    0.428133   -1.21217    0.036069
24   3.4424369   0.069996   -0.26244    0.430415   -0.60973    0.025765
25   2.9587754   0.071086   0.101225    0.430236   0.235277    0.026574
26   3.5763341   0.083713   -0.52633    0.427958   -1.22987    0.036854
27   3.1862987     0.1037     -0.1963   0.423559   -0.46345    0.056552
28   3.3551162   0.083486   -0.12512    0.428003   -0.29233    0.036654
29   3.0041364   0.071404   0.215864    0.430183   0.501795    0.026813
30   3.0325147   0.060675   -0.83251    0.431827   -1.92789     0.01936
31   3.3005456   0.091455   -0.39055    0.426371   -0.91598    0.043985
32   3.5508599   0.082412   -0.92086    0.428211   -2.15048    0.035716   Outlier
33   3.1828974    0.05232     -0.9029   0.432919     -2.0856   0.014396   Outlier
34   3.0708364   0.054788   -0.06084    0.432613   -0.14063    0.015786
35   3.3303759   0.052982   -0.77038    0.432838   -1.77982    0.014762
36   3.3941718    0.08477   -0.08417     0.42775   -0.19678     0.03779
37   3.3701497    0.09118    0.01985     0.42643    0.04655    0.043721
38   3.0424581   0.056097   -0.50246    0.432446     -1.1619   0.016549
39   3.1644626   0.056564   -0.10446    0.432385     -0.2416   0.016825
40   3.2282584   0.042179   0.411742    0.434024    0.94866    0.009356
41   3.0608929   0.053288   -0.13089    0.432801   -0.30243    0.014933
42   3.7167733   0.088213   0.463227    0.427053   1.084704    0.040922
43   3.3303759   0.052982   0.309624    0.432838   0.715334    0.014762
44    3.265128   0.047416   0.354872    0.433483   0.818652    0.011824
45   3.5409165   0.073941   -0.21092    0.429754   -0.49078    0.028751
46   3.0594409   0.074378   0.440559    0.429679   1.025322    0.029093
47   3.3587542   0.055258   0.851246    0.432554   1.967954    0.016057
48    3.153067   0.053592   0.516933    0.432763   1.194494    0.015104
49   3.4203641   0.080012   0.689636    0.428666   1.608797    0.033667
50   3.7635864   0.094414   -0.07359    0.425725   -0.17285    0.046877
51   3.1261409   0.045052   -0.58614    0.433736   -1.35138    0.010674
52   2.9672668   0.073025   -0.20727    0.429911   -0.48212    0.028043
53   3.4155107    0.07261   -0.18551    0.429981   -0.43144    0.027725
54    3.214677   0.097626   -0.78468       0.425     -1.8463   0.050121
55   3.0807798   0.063926    0.34922    0.431358   0.809583     0.02149
56   3.2714335   0.093867   1.138567    0.425846   2.673656    0.046336   Outlier
57   3.0594409   0.074378   1.210559    0.429679   2.817357    0.029093   Outlier
58   3.1913888   0.041572   0.018611    0.434083   0.042875    0.009089
59   3.2098236   0.041247   -0.37982    0.434114   -0.87494    0.008947
60   3.1644626   0.056564   0.425537    0.432385   0.984164    0.016825
61   3.2935063   0.046497   -0.95351    0.433583   -2.19913     0.01137   Outlier
62   3.4104207   0.074613   0.389579    0.429638   0.906761    0.029277
63   3.6345427   0.086769   0.445457    0.427349   1.042373    0.039593
64    3.331828   0.061582   0.588172    0.431699   1.362459    0.019943
65   3.2949584    0.06527   -0.39496    0.431157   -0.91604    0.022403
66   3.1346322    0.05154   0.045368    0.433012   0.104772     0.01397
67   3.5360631   0.090894   0.273937    0.426491   0.642304    0.043447
68   3.1346322    0.05154   -0.21463    0.433012   -0.49567     0.01397
69   3.6047123   0.079162   -0.60471    0.428823   -1.41017    0.032955
70   3.2835629    0.05138   -0.58356    0.433032   -1.34762    0.013883
71   2.8935275      0.084   -0.45353    0.427902   -1.05989    0.037106
72   3.0807798   0.063926    0.58922    0.431358   1.365966     0.02149
73   3.3672456   0.062214   -0.38725    0.431608   -0.89722    0.020355
74   3.5692948   0.080518   0.010705    0.428571   0.024979    0.034094
75   3.0439102   0.075561   -0.07391    0.429473     -0.1721   0.030025
76   3.4807586   0.085659   -0.42076    0.427573   -0.98406    0.038587
77    3.462821   0.125405   -0.01282    0.417648     -0.0307   0.082702   Influential
78   3.6231471   0.082635   0.596853    0.428168   1.393969     0.03591
79   3.2183149   0.050017   -0.68831    0.433191   -1.58894    0.013156
80   2.9403406   0.075217   0.299659    0.429533    0.69764    0.029752
81   3.4155107    0.07261   -0.40551    0.429981   -0.94309    0.027725
82    3.153067   0.053592   -0.05307    0.432763   -0.12262    0.015104
83   3.1814453   0.044711   0.558555    0.433771   1.287672    0.010513
84   3.0240233   0.059656   0.605977    0.431969   1.402824    0.018716
85   3.0992147   0.058583   0.600785    0.432116   1.390333    0.018048
86   3.2183149   0.050017   0.181685    0.433191   0.419411    0.013156
87   3.0608929   0.053288   -0.52089   0.432801   -1.20354    0.014933
88   3.3536642   0.081191   0.036336   0.428444   0.084809    0.034667
89   3.1261409   0.045052   0.583859   0.433736   1.346118    0.010674
90   3.4019293   0.083611   -0.41193   0.427978     -0.9625   0.036764
91   3.7267168   0.089416   0.223283   0.426803   0.523153    0.042045
92   3.0424581   0.056097   0.097542   0.432446   0.225559    0.016549
93   3.3834945   0.087661   0.736506   0.427167   1.724162    0.040411
94   3.0963105   0.079651   0.533689   0.428733   1.244806    0.033363
95   3.3119411   0.049318   0.018059   0.433271    0.04168    0.012791
96   3.0225712   0.071683   -0.01257   0.430137   -0.02923    0.027022
97   3.1630105   0.043642    0.03699    0.43388   0.085253    0.010016
98   2.8297317   0.107151   0.030268     0.4227   0.071607    0.060378
99   3.2765236   0.068198   0.003476   0.430703   0.008072    0.024459
100   3.2282584   0.042179   0.111742   0.434024   0.257455    0.009356
101   3.1913888   0.041572   0.148611   0.434083   0.342357    0.009089
102   3.3233366   0.079144   -0.09334   0.428827   -0.21766     0.03294
103     3.42255    0.07906   -0.02255   0.428842   -0.05258     0.03287
104    3.375737   0.078529   -0.21574    0.42894   -0.50295     0.03243
105   3.0822319   0.101167   -0.51223   0.424172   -1.20761    0.053823
106    3.331828   0.061582   0.548172   0.431699   1.269802    0.019943
107    3.755095   0.096502   0.184905   0.425257   0.434808    0.048973
108   3.2183149   0.050017   0.081685   0.433191   0.188566    0.013156
109   3.3303759   0.052982   0.359624   0.432838   0.830851    0.014762
110   2.9672668   0.073025   -0.41727   0.429911   -0.97059    0.028043
111   2.9687188   0.073637   0.861281   0.429807    2.00388    0.028515 Outlier
112   3.0240233   0.059656   0.125977   0.431969   0.291634    0.018716
113   3.6784516   0.095612   0.961548   0.425458   2.260031    0.048074 Outlier
114   3.2750715   0.044681   -0.11507   0.433774   -0.26528    0.010499
115   3.4288555   0.072861   0.531144   0.429939   1.235396    0.027918
116   3.1176495   0.053684   -0.74765   0.432752   -1.72766    0.015156
117   3.1899367   0.060145   -0.10994   0.431901   -0.25454    0.019023
118   3.1091581   0.072203   0.700842    0.43005   1.629676    0.027416
119   3.2013322   0.048785   0.068668   0.433331   0.158465    0.012516
120     3.48925   0.079762   -0.01925   0.428712     -0.0449   0.033456
121   2.9034709   0.084737   -0.17347   0.427757   -0.40554     0.03776
122   3.2367498   0.053944   -0.44675    0.43272   -1.03242    0.015303
123   3.4742165    0.07844   0.085783   0.428956   0.199982    0.032357
124   3.0608929   0.053288   -0.44089   0.432801     -1.0187   0.014933
125    3.331828   0.061582   0.248172   0.431699   0.574873    0.019943
126   3.3970759   0.072535   0.082924   0.429994   0.192849    0.027668
127   3.1899367   0.060145   0.430063   0.431901   0.995744    0.019023
128   3.2935063   0.046497   0.006494   0.433583   0.014977     0.01137
129    3.377189   0.058409   0.002811    0.43214   0.006505    0.017941
130   3.4962893   0.104347   -0.41629     0.4234     -0.9832   0.057259
131   3.1261409   0.045052   0.153859   0.433736    0.35473    0.010674
132   3.2183149   0.050017   -0.22831   0.433191   -0.52705    0.013156
133   3.7167733   0.088213   0.003227   0.427053   0.007556    0.040922
134   3.4339456   0.073401   -0.49395   0.429847   -1.14912    0.028333
135   3.1899367   0.060145   -0.09994   0.431901   -0.23139    0.019023
136   3.2047335    0.10175   0.235266   0.424032   0.554832    0.054445
137   3.2098236   0.041247   0.060176   0.434114   0.138619    0.008947
138   3.1275929   0.066614   -0.78759   0.430951   -1.82757    0.023336
139   3.4472903   0.071799   -0.03729   0.430118     -0.0867   0.027109
140   3.3034497   0.051056    0.07655    0.43307   0.176762    0.013708
141   3.2083715   0.064397   0.821628   0.431288   1.905058    0.021808
142   3.1913888   0.041572   0.308611   0.434083    0.71095    0.009089
143   3.2069194    0.10626   0.173081   0.422924   0.409247    0.059379
144   3.1545191   0.045781   -0.21452   0.433659   -0.49467    0.011022
145   2.9687188   0.073637   -0.03872   0.429807   -0.09008    0.028515
146   3.4870641   0.110555   -0.09706   0.421822   -0.23011    0.064275
147   3.2466932   0.044287   0.193307   0.433814   0.445598    0.010315
148   3.1814453   0.044711   0.128555   0.433771   0.296365    0.010513
149   3.5692948   0.080518   -0.68929   0.428571   -1.60836    0.034094
150   3.4089686   0.082817   -0.61897   0.428133   -1.44574    0.036069
PA for a Male with 4 Years
ork Experience

Predicted
Lower 95%
Upper 95%

750

1           1
4           4
710         730
3.660017    3.696886
0.445446    0.447524
2.779662    2.812424
4.540372    4.581349
3.660017    3.696886
0.090917    0.100608
3.480333    3.498051
3.839701    3.895722

Normal
Scores
0.641232
0.293553
-0.099057
-0.312071
-0.159925
0.815197
-0.191492
0.199536
-0.183512
-0.04738
-0.266724
-0.302744
-0.152169
-0.069404
-0.438631
-0.284491
-0.33117
0.040069
0.159925
0.069404
-0.321543
0.284491
-0.533349
-0.293553
0.167733
-0.565525
-0.215834
-0.167733
0.258006
-0.861295
-0.361078
-1.00156
-0.919684
-0.062046
-0.743516
-0.091606
0.062046
-0.503841
-0.129176
0.39279
-0.175594
0.450869
0.34096
0.361078
-0.23244
0.426729
0.776738
0.463472
0.663504
-0.076782
-0.601115
-0.224096
-0.207649
-0.776738
0.350926
1.00156
1.150708
0.054705
-0.34096
0.403831
-1.150708
0.381995
0.438631
0.549064
-0.37143
0.099057
0.312071
-0.249389
-0.620517
-0.582835
-0.476476
0.565525
-0.350926
0.032768
-0.084182
-0.426729
-0.010914
0.582835
-0.663504
0.321543
-0.381995
-0.054705
0.518298
0.620517
0.601115
0.23244
-0.549064
0.084182
0.533349
-0.39279
0.266724
0.152169
0.714123
0.489917
0.04738
-0.003638
0.091606
0.076782
0.018193
0.175594
0.207649
-0.106537
-0.025477
-0.258006
-0.518298
0.503841
0.240869
0.129176
0.37143
-0.415137
0.861295
0.183512
0.919684
-0.144461
0.476476
-0.714123
-0.136798
0.687656
0.114049
-0.018193
-0.199536
-0.463472
0.144461
-0.450869
0.302744
0.136798
0.415137
0.025477
0.003638
-0.403831
0.215834
-0.275551
0.010914
-0.489917
-0.121594
0.275551
0.106537
-0.815197
-0.032768
0.121594
0.743516
0.33117
0.224096
-0.240869
-0.040069
-0.114049
0.249389
0.191492
-0.687656
-0.641232
Residual Plot                                                       Normal Probability Plot
4                                                                   1.5
3
Standardized Residual

1
2

Normal Scores
0.5
1
0
0
-0.5
-1
-2                                                                   -1
-3                                                                  -1.5
2.7            3.2                 3.7                                -1.5    -1         -0.5       0          0.5         1   1.5
Fit of GPA                                                                      Residuals

5                       GPA      Fit of GPA
Residual Plot                                           4.5
4
4                                                                   3.5
Standardized Residual

3                                                     GPA             3
2                                                                   2.5
1                                                                     2
0                                                                   1.5
-1                                                                    1
-2                                                                  0.5
-3                                                                    0

-0.3     0.2                 0.7         1.2                          -0.3         0.2            0.7                1.2

Gender                                                                  Gender

5                       GPA      Fit of GPA
Residual Plot                                           4.5
4
4                                                                   3.5
Standardized Residual

3                                                                     3
GPA

2                                                                   2.5
1                                                                     2
0                                                                   1.5
-1                                                                    1
-2                                                                  0.5
-3                                                                    0

-3        2                   7          12                           -3           2                7                12

Work                                                                 Work

5                       GPA      Fit of GPA
Residual Plot                                           4.5
4
4                                                                   3.5
Standardized Residual

3                                                                     3
GPA

2                                                                   2.5
1                                                                     2
0                                                                   1.5
-1                                                                    1
-2                                                                  0.5
Standardized Residu
-2                             0.5
-3                              0

450   550     650   750         450   550     650   750

GMAT                            GMAT
Sheet: GPA                               PredInt.xls   Page 12
Observ   GPA    Gender Work GMAT Major
1     4.15      0    0   680    1
2     3.76      1    4   630    1
3     3.02      0    8   650    3
4      2.9      0    3   590    1
5     2.96      0    9   660    3
6     4.22      0    1   610    2
7     3.17      0    1   590    3
8     3.73      1    5   700    1
9     3.21      0    4   680    5
10     3.43      1    4   610    2
11     3.28      1    7   700    5
12     2.79      0    7   630    3
13     3.02      0    5   590    2
14     2.96      0    7   580    1
15     3.24      1    3   690    5
16     3.33      1    2   620    3
17     2.95      1    5   530    5
18     3.39      0    1   620    3
19     3.44      0    5   700    3
20      3.3      0    6   690    3
21     2.87      0    4   600    2
22     3.62      0    1   620    3
23     2.89      1    7   650    1
24     3.18      0    2   680    1
25     3.06      0    8   570    1
26     3.05      1    5   690    6
27     2.99      1    9   580    4
28     3.23      1    5   570    4
29     3.22      0    9   620    6
30      2.2      0    8   610    5
31     2.91      0    7   730    4
32     2.63      1    2   600    6
33     2.28      0    4   590    6
34     3.01      0    6   580    6
35     2.56      0    4   670    1
36     3.31      0    5   730    5
37     3.39      0    0   590    2
38     2.54      0    7   590    1
39     3.06      0    4   580    7
40     3.64      0    5   640    2
41     2.93      0    7   600    3
42     4.18      1    2   690    4
43     3.64      0    4   670    2
44     3.62      0    5   660    1
45     3.33      1    3   620    4
46      3.5      0    9   650    6
47     4.21      0    3   660    1
48     3.67      0    7   650    1
49     4.11      1    4   580    4
50     3.69      1    1   690    5
51     2.54      0    6   610    6
52     2.76      0    9   600    1
53     3.23      0    1   640    5
54     2.43      1    8   570    6
55     3.43      0    5   560    1
56     4.41      1    6   550    4
57     4.27      0    9   650    1
Sheet: GPA                            PredInt.xls   Page 13
58       3.21   0   5   620   2
59       2.83   0   5   630   1
60       3.59   0   4   580   1
61       2.34   0   4   650   2
62        3.8   1   5   600   2
63       4.08   1   1   620   4
64       3.92   0   2   620   1
65        2.9   0   2   600   1
66       3.18   0   7   640   1
67       3.81   0   0   680   2
68       2.92   0   7   640   3
69          3   1   4   680   1
70        2.7   0   5   670   2
71       2.44   0   9   560   3
72       3.67   0   5   560   1
73       2.98   0   4   690   5
74       3.58   1   2   610   2
75       2.97   0   5   540   2
76       3.06   0   0   650   2
77       3.45   1   9   730   5
78       4.22   1   4   690   4
79       2.53   0   6   660   2
80       3.24   0   8   560   1
81       3.01   0   1   640   6
82        3.1   0   7   650   3
83       3.74   0   6   640   1
84       3.63   0   7   580   1
85        3.7   0   5   570   4
86        3.4   0   6   660   5
87       2.54   0   7   600   3
88       3.39   1   7   620   1
89       3.71   0   6   610   2
90       2.99   1   4   570   4
91       3.95   1   1   670   2
92       3.14   0   7   590   1
93       4.12   1   4   560   4
94       3.63   0   9   670   2
95       3.33   0   4   660   1
96       3.01   0   9   630   3
97        3.2   0   6   630   1
98       2.86   0   8   500   4
99       3.28   0   2   590   6
100       3.34   0   5   640   3
101       3.34   0   5   620   6
102       3.23   0   1   590   1
103        3.4   0   4   720   2
104       3.16   0   5   720   5
105       2.57   0   3   510   2
106       3.88   0   2   620   6
107       3.94   1   0   660   4
108        3.3   0   6   660   6
109       3.69   0   4   670   1
110       2.55   0   9   600   3
111       3.83   0   7   550   3
112       3.15   0   7   580   2
113       4.64   1   4   720   6
114       3.16   0   4   640   5
115       3.96   1   5   610   4
Sheet: GPA                            PredInt.xls   Page 14
116       2.37   0   5   580   1
117       3.08   0   7   670   6
118       3.81   0   4   550   3
119       3.27   0   4   600   1
120       3.47   0   1   680   3
121       2.73   0   8   540   2
122       2.79   0   6   670   2
123       3.56   1   6   660   3
124       2.62   0   7   600   6
125       3.58   0   2   620   3
126       3.48   0   1   630   1
127       3.62   0   7   670   4
128        3.3   0   4   650   3
129       3.38   0   3   670   1
130       3.08   0   4   760   3
131       3.28   0   6   610   4
132       2.99   0   6   660   6
133       3.72   1   2   690   3
134       2.94   0   1   650   1
135       3.09   0   7   670   6
136       3.44   1   9   590   4
137       3.27   0   5   630   3
138       2.34   0   4   560   3
139       3.41   1   5   620   1
140       3.38   0   3   630   3
141       4.03   0   7   680   1
142        3.5   0   5   620   3
143       3.38   0   9   730   2
144       2.94   0   5   600   1
145       2.93   0   7   550   3
146       3.39   1   1   540   1
147       3.44   0   5   650   2
148       3.31   0   6   640   3
149       2.88   1   2   610   2
150       2.79   1   7   650   7
0fa40f21-a3e7-4b7b-a292-300e32ba75bc.xls, Warning, p. 15 of 17

WARNING: YOU NEED TO ENABLE THE MACROS IN THIS FILE
Try these steps. If step 1 does not work, then go to step 2.
STEP 1: Try to enable the macros
 For Excel 2007,
A. If you see the Security Warning in your menu bar, proceed with step B.
If the Security Warning is not there, go to STEP 2.
B.  In the Security Warning, Click Options.
C. In the Alert Window that appears, click Enable This Content and click OK.

 For Excel 2003,
A. Close this file. Then open it again.
B. In the window that appears, click Enable Macros.
If a window like this one
does NOT appear, then go to STEP 2.

STEP 2: If Step 1 does not work
If you DID NOT get a Security Message, then your security setting is too high.
Here is what you should do:

 For Excel 2007,
A. Click the Microsoft Office Button at the top-left of the screen:
B. Click Excel Options.
C. Click Trust Center, then Trust Center Settings, and then Macro Settings.
D. Click Disable all macros with notification
0fa40f21-a3e7-4b7b-a292-300e32ba75bc.xls, Warning, p. 16 of 17
E. Exit from Excel. Closing the file is not enough. On the menu bar, select File, and then Exit.
F. Open this file again and follow the instructions in STEP 1 to enable the macros.
 For Excel 2003,
A. On the menu bar at the top of this page, select Tools, then Macro, then Security.
B. On the Security Level tab, select Medium and click OK.
C. Then exit from Excel. Closing the file is not enough. On the menu bar, select File, and then Exit.
D. Open this file again and follow the instructions in STEP 1 to enable the macros.
0fa40f21-a3e7-4b7b-a292-300e32ba75bc.xls, Warning, p. 17 of 17

```
DOCUMENT INFO
Shared By:
Categories:
Stats:
 views: 65 posted: 2/3/2011 language: English pages: 17
Description: Excel 2007 Dataanalysis document sample