Transcript Using Excel

Using Excel
*Bring Money for Yearbook!
DATA Analysis (Windows 2010)

Go to File
Click on “Add ins”
Check 2nd and 3rd

It will now be found under Data


(Tool Pack)
DATA Analysis (Vista)

Go to Tools
Click on “Add ins”
Check 1st 2

It will now be found under Tools


A study of nutrition in developing countries collected data from the Egyptian village of
Nahya. Here are the mean weights (in kg) for 170 infants in Nahya who were weighed each
month during their first year of life:
Age (mths)
Weight (kg)
1
4.3
Put independent variable in column A.
2
5.1
Put dependent variable in column B.
3
5.7
Go to Data Analysis – to Regression
4
6.3
5
6.8
6
7.1
Hightlight A column for x-range
7
7.2
Click on labels (if you included them)
8
7.2
Click on output – click space to put it
9
7.2
10
7.2
11
7.5
12
7.8
Hightlight B column for y-range.
If you want the normal probability plot,
click on it.
What you get!
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.906552235
R Square
0.821836955
Adjusted R
Square
Standard Error
Observations
0.80402065
0.470339361
12
ANOVA
df
SS
MS
F
1
10.20448
10.20448
46.12836
Residual
10
2.212191
0.221219
Total
11
12.41667
Regression
Significance F
4.79E-05
Standard
Error
t Stat
4.88030303
0.289474
0.267132867
0.039332
Coefficients
Intercept
Age (mths)
P-value
Lower
95%
Upper
95%
16.85922
1.13E-08
4.235315
5.525291
6.791786
4.79E-05
0.179496
0.354769
Scatter plot with regression line:







Click on Chart wizard
Scatter(xy)
1st one click Next
Highlight Data Range (do not include labels) & press next
Type in labels for x & y
Press Next & Finish
To get line – click on point & press add trendline
Normal Probability Plots

Used to determine if the data is approximately
normal

If it appears to be a straight line – then it’s
approximately normal
Weight (kg)
Normal Probability Plot
10
5
0
0
20
40
60
80
Sample Percentile
100
120
Find equation, r2, se for the data below comparing
the intensity of a baby’s cry & their IQ.
ANOVA
df
SS
MS
F
Regression
1
2895.524 2895.524 20.28253
Residual
8
1142.076 142.7595
Total
9
Coefficient
s
Intercept
74.26985
Crying
2.604357
Significance
F
0.001993
4037.6
Standard
Error
t Stat
10.0478 7.391653
P-value
Lower 95%
Upper 95%
7.68E-05
51.09958
97.44012
0.578282 4.503613 0.001993
1.270837
3.937877
Residual Plot

Looking for no – pattern in order for it to be a good linear
model

Click on residual plot when doing the regression to see it.
Homework

Worksheet