1. No category

Model Regress Linear 3Factor Excel 2013
V0G
Model Regress Linear 3Factor Excel 2013
1
Model using Regress
Linear 3Factor in Excel 2013
by
Milo Schield
Member: International Statistical Institute
US Rep: International Statistical Literacy Project
Director, W. M. Keck Statistical Literacy Project
Slides at: www.StatLit.org/pdf
/Model-Regress-Linear-3Factor-Excel2013-6up.pdf
/Model-Regress-Linear-3Factor-Excel2013-1up.pdf
V0G
Model Regress Linear 3Factor Excel 2013
3
10/9/2014 V0G
V0G
2
Model Regress Linear 3Factor Excel 2013
Goal: Summarize association
before/after control for Gender
Required output: Create and upload your worksheet*:
1. Generate two charts (slides 4 and 15).
Slide 4: Show trend-line, equation and R2.
Slide 15: Show trend-lines. Show regression model.
2. Generate/show averages (slide 3) .
3. Generate/show output from regression (slide 9).
Data: www.StatLit.org/xls/Pulse-Regress-Worksheet.xlsx
Note: Male is already in column D in this worksheet.
Demo output: www.StatLit.org/pdf/Pulse-Regress-Output.pdf
Subjects are college students. Male: 1 for men; 0 for women.
V0G
4
Model Regress Linear 3Factor Excel 2013
Analyze Data:
Enter Formula into K4:L5
Chart #1
.
Weight vs Height
215
y = 5.0918x - 204.74
R2 = 0.616
190
Weight
Actual male-female differences:
• Average weight: 158.3 - 123.8 = 34.5 pounds
• Average height: 70.75 – 65.40 = 5.35 inches
Model Regress Linear 3Factor Excel 2013
5
Decompose Male-Female
Weight Difference: 1st try
Actual male-female differences (slide 3):
• Average weight: 158.3 - 123.8 = 34.5 pounds
• Average height: 70.75 – 65.40 = 5.35 inches
140
115
Question: How much of the male-female weight
difference (34.5#) is due to gender (male vs. female)
and how much is due to the difference in heights?
Analyzing a whole into parts is called “decomposition”.
V0G
165
90
60
65
70
75
Height
V0G
Model Regress Linear 3Factor Excel 2013
6
Model Weight by Height & Sex:
Four Step Process
Step 1. From Data Toolbar, select Data Analysis
(in the Analysis section). Select Regression
Step 2. Regress Weight on Height and Gender
Model Weight on Height (slide 4)
• Expected Weight = -204.74 + 5.09 * Height
Decomposition of male-female weight difference:
• Due to Height difference: 5.09*5.35 = 27.23#
• Due to Sex (Gender) difference: 34.5# – 27.2# = 7.3#
Inadequate!!! Sex and height are confounded in slope.
Model-Regress-Linear-3Factor-Excel2013-6up.pdf
Step 3. Generate Y values given X for models
Step 4. Generate two trend lines on XY plot
1
Model Regress Linear 3Factor Excel 2013
V0G
V0G
7
Model Regress Linear 3Factor Excel 2013
10/9/2014 V0G
V0G
Model Regress Linear 3Factor Excel 2013
1) Data Toolbar, select Data
Analysis. Select Regression
2a) Regress Weight
on Height and Sex
.
.
9
Model Regress Linear 3Factor Excel 2013
V0G
2b) Results: Regress Weight
on Height and Sex (Male?)
Model Regress Linear 3Factor Excel 2013
8
10
3) Expected Weights at selected
Heights for Men and Women
Create formula in L33 predicting weight:
Formatting and
formula are optional
Pull L33 down
Weight = -117.6 + (3.69*Height) + (14.7*Male)
.
V0G
Model Regress Linear 3Factor Excel 2013
11
V0G
4a) Start with new chart:
Select Data; Select “Add”
.
Model-Regress-Linear-3Factor-Excel2013-6up.pdf
Model Regress Linear 3Factor Excel 2013
12
4b) Add Two New Series
.
2
Model Regress Linear 3Factor Excel 2013
V0G
V0G
13
Model Regress Linear 3Factor Excel 2013
10/9/2014 V0G
4c) After Adding Two New Series,
Press “OK”
14
Model Regress Linear 3Factor Excel 2013
4d) Select Data Point. Format
Data Series. Select ‘Solid Line’
.
. Height
Weight vs
215
Weight
190
165
140
115
90
60
65
70
75
Height
V0G
V0G
15
Model Regress Linear 3Factor Excel 2013
Weight. vs Height
Weight = ‐117.6 + 3.69*Height + 14.7*Male
190
Weight
165
140
Male
115
Female
90
60
65
70
75
Height
V0G
Model Regress Linear 3Factor Excel 2013
16
Decompose Male-Female
Weight Difference: 2nd try
4e) Add Regression Equation.
Final Result
215
Model Regress Linear 3Factor Excel 2013
Multivariate ‘regression’ model (slide 9 or 15):
Weight = -117 + (3.7*Height) + (14.7*Male)
Difference in average heights: 5.35” (slide 3)
Difference in average weights: 34.5# (slide 3)
• 14.7 pounds due to gender difference – after
controlling for height.
• 19.8 pounds due to height difference – after
controlling for gender: 3.7 #/inch * 5.35 inches
Moral: How you take things into account matters!
17
Decompose Male-Female
Weight Difference: Summary
Decompose 34.5# male-female weight difference.
1st try: Regress weight on height (R2 = .62)
• 27.2 pounds due to height difference
• 7.3 pounds due to gender difference
Problem: Gender, height and weight are confounded
2nd try: Regress weight on height and sex (R2 = .66)
• 19.8# due to height – after controlling for gender
• 14.7# due to gender – after controlling for height
Moral: How you take things into account matters!
Model-Regress-Linear-3Factor-Excel2013-6up.pdf
3