Prepare for your exams
Get points
Guidelines and tips
Sell on Docsity
Docsity AI

Prepare for your exams

Study with the several resources on Docsity

Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips

Sell on Docsity

Docsity AI

Log in Sign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents

20 Points

For each uploaded document

Answer questions

5 Points

For each given answer (max 1 per day)

All the ways to get free points

Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

Data Analysis: Regression and Residuals Diagnostics using EXCEL, Exams of Statistics

University of Florida (UF)Statistics

A step-by-step guide on how to perform data analysis for regression and residuals diagnostics using microsoft excel. It covers topics such as sorting data, obtaining histograms, calculating residuals, and assessing normality. The document also includes instructions on how to obtain residuals in excel and interpret regression statistics.

Typology: Exams

Pre 2010

Uploaded on 09/17/2009

koofers-user-jo7 🇺🇸

10 documents

1 / 16

This page cannot be seen from the preview

Don't miss anything!

Example of Model Diagnostics

Calculator Maintenance Data Using EXCEL

First, we begin with the original data. I have sorted it with respect to the predictor

variable (X = number of machines serviced). Note that in this case we wish to preserve

the pairs (Xi,Yi). To do this:

Move the cursor into the field of data

Click on Data on the main toolbar, then Sort

Select Column 2 (X) and Ascending. If you have already placed headers on the

columns, make sure you click on the correct option regarding headers.

Y (minutes) X (Machines)

10 1

17 1

33 2

25 2

39 3

62 4

53 4

49 4

78 5

75 5

65 5

71 5

68 5

86 6

97 7

101 7

105 7

118 8

Diagnostics for the Predictor Variable (Section 3.1)

X-values that are far away from the rest of the others can exert a lot of influence on the

least squares regression line. A histogram or bar chart of the X-values can identify any

potential extreme values. The following steps in EXCEL can be used to obtain a

histogram of the X-values. A copy of the histogram is given below the instructions.

Discover Exams of Statistics University of Florida (UF)

Partial preview of the text

Download Data Analysis: Regression and Residuals Diagnostics using EXCEL and more Exams Statistics in PDF only on Docsity!

Example of Model Diagnostics

Calculator Maintenance Data Using EXCEL

First, we begin with the original data. I have sorted it with respect to the predictor

variable ( X = number of machines serviced). Note that in this case we wish to preserve

the pairs ( X i, Y i). To do this:

 Move the cursor into the field of data

 Click on Data on the main toolbar, then Sort

 Select Column 2 (X) and Ascending. If you have already placed headers on the

columns, make sure you click on the correct option regarding headers.

Y (minutes) X (Machines)

Diagnostics for the Predictor Variable (Section 3.1)

X -values that are far away from the rest of the others can exert a lot of influence on the

least squares regression line. A histogram or bar chart of the X -values can identify any

potential extreme values. The following steps in EXCEL can be used to obtain a

histogram of the X -values. A copy of the histogram is given below the instructions.

 Select Tools on the header bar, then Data Analysis (you may need to add it in from

add-ins ), then Histogram

 For the Input Range , highlight the column containing X (if you have included the

header cell, click on Labels ).

 Click Chart Output then OK.

 You may experiment and make the chart more visually appealing if preparing

reports, but for investigating the model assumptions, this is fine.

Residuals (Section 3.2)

The model assumptions are that the error terms are independent and normally distributed

with mean 0 and constant (with respect to levels of X ) variance ^2. The errors are:

 (^) i  Yi  E { Y (^) i } Yi (  0   1 Xi )

Since the model parameters are unknown, we cannot observe the actual errors. However,

if we replace the unknown parameters, we have an “estimate” of each residual by taking

the difference between the actual and fitted values. These are referred to as the residuals:

( 0 1 )

^

ei  Yi  Yi  Yi  b  bX i

Histogram

0 1 2 3 4 5 6 7 1 2.75 4.5 6.25 More Bin Frequency Frequency

 Choose Tools, Data Analysis, Regression

 Highlight the column containing Y , then the column containing X , then the

appropriate Labels option

 Click on Residuals and Standardized Residuals

 Click OK

 The residuals will appear on a worksheet below the ANOVA table and parameter

estimates. Also printed are observation number, predicted (fitted) values, and

standardized residuals.

Regression Statistics Multiple R 0. R Square 0. Adjusted R Square 0. Standard Error 4. Observations 18 ANOVA df SS MS F Significance F Regression 1 16182.6 16182.6 806 4.09733E- Residual 16 321.4 20. Total 17 16504 Observation Predicted Y (minutes) Residuals Standard Residuals 1 12.41610738 -2.416107383 -0. 2 12.41610738 4.583892617 1. 3 27.15436242 5.845637584 1. 4 27.15436242 -2.154362416 -0. 5 41.89261745 -2.89261745 -0. 6 56.63087248 5.369127517 1. 7 56.63087248 -3.630872483 -0. 8 56.63087248 -7.630872483 -1. 9 71.36912752 6.630872483 1. 10 71.36912752 3.630872483 0. 11 71.36912752 -6.369127517 -1. 12 71.36912752 -0.369127517 -0. 13 71.36912752 -3.369127517 -0. 14 86.10738255 -0.10738255 -0. 15 100.8456376 -3.845637584 -0. 16 100.8456376 0.154362416 0. 17 100.8456376 4.154362416 0. 18 115.5838926 2.416107383 0.

Diagnostics for Residuals (3.3)

Obtaining a Plot of Residuals Against X ( e i vs X i)

 Copy and paste the column of Residuals to the original spreadsheet in Column C.

 Highlight Columns B and C and click on the Chart Wizard icon

 Click on XY (Scatter) then click through the dialog boxes

 Using all default options, your plot will appear as below.

Y (minutes) X (Machines) Residuals 10 1 -2. 17 1 4. 33 2 5. 25 2 -2. 39 3 -2. 62 4 5. 53 4 -3. 49 4 -7. 78 5 6. 75 5 3. 65 5 -6. 71 5 -0. 68 5 -3. 86 6 -0. 97 7 -3. 101 7 0. 105 7 4. 118 8 2.

Residuals

 Type in desired upper endpoints of bins in a new range of cells

 Choose Tools, Data Analysis, Histogram

 Highlight the column containing the Residuals

 For Bin Range highlight the range of values you’ve entered (include a label)

 Choose appropriate Labels choice

 Click on Chart Output then OK

residual

The ranges will be: (^ ,^7.^5 ] (^7.^5 ,^2.^5 ] (^2.^5 ,^2.^5 ] (^2.^5 ,^7.^5 ] (^7.^5 ,)

Computing Expected Residuals Under Normality

 Copy the cells containing Observation and Residuals to a new worksheet in

Columns A and B , respectively.

 Highlight the column of Residuals then select Data and Sort then click on

Continue with Current Selection then OK. Note that the residuals are in ascending

order and the observation number represents the rank now, as opposed to i

 Compute the percentile representing each residual in their empirical distribution.

Go to Cell C2 (assuming that you have a header row with labels). Then type:

=((A2-0.375)/(n+0.25)) where n is the sample size (type the number)

 Highlight Cell C2 , then Copy it. Then highlight the next n-1 cells in column C ,

then Paste.

Histogram

-7.5 -2.5 2.5 7.5 More

residual

Frequency

 Compute the Z values from the standard normal distribution corresponding to the

percentiles in column C. Go to Cell D2 (assuming that you have a header row with

labels). Then type: =NORMSINV(C2)

 Highlight Cell D2 , then Copy it. Then highlight the next n-1 cells in column D ,

then Paste.

 Compute the Expected residuals under normality by multiplying the elements of

Column D by MSE. This could be done in Column E.

The results of the steps are shown below:

First, put observation number and residuals in a new worksheet:

Observation Residuals 1 -2. 2 4. 3 5. 4 -2. 5 -2. 6 5. 7 -3. 8 -7. 9 6. 10 3. 11 -6. 12 -0. 13 -3. 14 -0. 15 -3. 16 0. 17 4. 18 2.

Second, sort only the residuals:

Fifth, multiply the residual standard error ( MSE^ ) by the Z -values to obtain the

expected residuals under normality.

Obtaining a Normal Probability Plot

 Highlight these 2 columns

 Click on Chart Wizard , then XY (Scatter) , then click thru dialog boxes

Observation Residuals percentile z(pct) expected Residuals 1 -7.63087 0.034247 -1.82175 -8.16142 -7. 2 -6.36913 0.089041 -1.34668 -6.03315 -6. 3 -3.84564 0.143836 -1.06324 -4.76334 -3. 4 -3.63087 0.19863 -0.84652 -3.79243 -3. 5 -3.36913 0.253425 -0.66375 -2.97361 -3. 6 -2.89262 0.308219 -0.5009 -2.24405 -2. 7 -2.41611 0.363014 -0.35041 -1.56986 -2. 8 -2.15436 0.417808 -0.2075 -0.92962 -2. 9 -0.36913 0.472603 -0.06873 -0.3079 -0. 10 -0.10738 0.527397 0.068728 0.307903 -0. 11 0.154362 0.582192 0.207503 0.929616 0. 12 2.416107 0.636986 0.350415 1.569858 2. 13 3.630872 0.691781 0.500904 2.244051 3. 14 4.154362 0.746575 0.663752 2.973607 4. 15 4.583893 0.80137 0.846524 3.792426 4. 16 5.369128 0.856164 1.063245 4.763337 5. 17 5.845638 0.910959 1.346684 6.033145 5. 18 6.630872 0.965753 1.821745 8.161418 6.

As always, you can make the plot more attractive with plot options, but it is unnecessary

for our purposes of assessing normality. For this example, the residuals appear to fall on a

reasonably straight line, as would be expected under the normality of errors assumption.

Correlation Test for Normality (3.5)

 H^ 0 :Error terms are normally distributed

 H A :Error terms are not normally distributed

 TS : Correlation coefficient between observed and expected residuals ( ree^ *)

 RR : ree^ * Tabled values in Table B.6, Page 1348 (indexed by  and n )

We can obtain the correlation coefficient between the observed and expected residuals as

follows.

 Select Tools, Data Analysis, Correlation

 Highlight the columns for Residuals and Expected

 Click on Labels if they are included

 Click OK

expected Residuals expected 1

Residuals

 Obtain the mean and sum of squared deviations of the absolute difference from the

median in the previous step.

In Cell F2 type: =average(C2:C9) (this computes d 1 )

In Cell F3 type: =devsq(C2:C9) (this computes (^)  ^ 1 2

( di 1 d ) )

In Cell G2 type: =average(D2:D11) (this computes d 2 )

In Cell G3 type: =devsq(D2:D11) (this computes (^)  (^ di 2 ^ d 2 )^2 )

 Compute s^2. In Cell H2 type: =(F3+G3)/(18-2) (18= n )

 Compute t * L. In Cell I2 type:

=(F2-G2)/sqrt(H2*((1/8)+(1/10))) (since n 1 =8 and n 2 =10)

The result of the steps on the calculator maintenance are shown below.

First, separate the residuals into Columns A and B :

Group 1 Group 2 -2.41611 6. 4.583893 3. 5.845638 -6. -2.15436 -0. -2.89262 -3. 5.369128 -0. -3.63087 -3. -7.63087 0.

Second, obtain the median residuals for each group:

Group 1 Group 2 -2.41611 6. 4.583893 3. 5.845638 -6. -2.15436 -0. -2.89262 -3. 5.369128 -0. -3.63087 -3. -7.63087 0.

-2.28523 0.

Third, obtain the absolute difference between the actual residuals and the group

medians:

Group 1 Group 2 d1 d

Data Analysis: Regression and Residuals Diagnostics using EXCEL, Exams of Statistics

Related documents

Partial preview of the text

Download Data Analysis: Regression and Residuals Diagnostics using EXCEL and more Exams Statistics in PDF only on Docsity!

Example of Model Diagnostics

Calculator Maintenance Data Using EXCEL

First, we begin with the original data. I have sorted it with respect to the predictor

variable ( X = number of machines serviced). Note that in this case we wish to preserve

the pairs ( X i, Y i). To do this:

 Move the cursor into the field of data

 Click on Data on the main toolbar, then Sort

 Select Column 2 (X) and Ascending. If you have already placed headers on the

columns, make sure you click on the correct option regarding headers.

Y (minutes) X (Machines)

Diagnostics for the Predictor Variable (Section 3.1)

X -values that are far away from the rest of the others can exert a lot of influence on the

least squares regression line. A histogram or bar chart of the X -values can identify any

potential extreme values. The following steps in EXCEL can be used to obtain a

histogram of the X -values. A copy of the histogram is given below the instructions.

 Select Tools on the header bar, then Data Analysis (you may need to add it in from

add-ins ), then Histogram

 For the Input Range , highlight the column containing X (if you have included the

header cell, click on Labels ).

 Click Chart Output then OK.

 You may experiment and make the chart more visually appealing if preparing

reports, but for investigating the model assumptions, this is fine.

Residuals (Section 3.2)

The model assumptions are that the error terms are independent and normally distributed

with mean 0 and constant (with respect to levels of X ) variance ^2. The errors are:

Since the model parameters are unknown, we cannot observe the actual errors. However,

if we replace the unknown parameters, we have an “estimate” of each residual by taking

the difference between the actual and fitted values. These are referred to as the residuals:

^

Histogram

 Choose Tools, Data Analysis, Regression

 Highlight the column containing Y , then the column containing X , then the

appropriate Labels option

 Click on Residuals and Standardized Residuals

 Click OK

 The residuals will appear on a worksheet below the ANOVA table and parameter

estimates. Also printed are observation number, predicted (fitted) values, and

standardized residuals.

Diagnostics for Residuals (3.3)

Obtaining a Plot of Residuals Against X ( e i vs X i)

 Copy and paste the column of Residuals to the original spreadsheet in Column C.

 Highlight Columns B and C and click on the Chart Wizard icon

 Click on XY (Scatter) then click through the dialog boxes

 Using all default options, your plot will appear as below.

Residuals

Residuals

 Type in desired upper endpoints of bins in a new range of cells

 Choose Tools, Data Analysis, Histogram

 Highlight the column containing the Residuals

 For Bin Range highlight the range of values you’ve entered (include a label)

 Choose appropriate Labels choice

 Click on Chart Output then OK

residual

The ranges will be: (^ ,^7.^5 ] (^7.^5 ,^2.^5 ] (^2.^5 ,^2.^5 ] (^2.^5 ,^7.^5 ] (^7.^5 ,)

Computing Expected Residuals Under Normality

 Copy the cells containing Observation and Residuals to a new worksheet in

Columns A and B , respectively.

 Highlight the column of Residuals then select Data and Sort then click on

Continue with Current Selection then OK. Note that the residuals are in ascending

order and the observation number represents the rank now, as opposed to i

 Compute the percentile representing each residual in their empirical distribution.

Go to Cell C2 (assuming that you have a header row with labels). Then type:

=((A2-0.375)/(n+0.25)) where n is the sample size (type the number)

 Highlight Cell C2 , then Copy it. Then highlight the next n-1 cells in column C ,

then Paste.

Histogram

-7.5 -2.5 2.5 7.5 More

residual

Frequency

Frequency

 Compute the Z values from the standard normal distribution corresponding to the

percentiles in column C. Go to Cell D2 (assuming that you have a header row with

labels). Then type: =NORMSINV(C2)

 Highlight Cell D2 , then Copy it. Then highlight the next n-1 cells in column D ,

then Paste.

 Compute the Expected residuals under normality by multiplying the elements of