Transcript Notes for Excel

Notes of Econ424
Part 1: Excel
Fall 2009
Ginger Z. Jin (Session 1)
Paul Bailey (Session 2)
http://www.glue.umd.edu/~ginger/,
click on “Econ424” at the bottom
1
Class 1: Introduction
•
•
•
•
•
Goal of this class
Logins
Syllabus
First-class questionnaire
A peek at the data collected by the
questionnaire
2
"There are three kinds of lies –
lies, damned lies and statistics."
--- (?) Mark Twain
Winston Churchill
Benjamin Disraeli
3
Statistical manipulation
– Randomness behind each statistics
• News tend to report outliers or new observation that deviates
from the status quo
• Newsworthy is not equal to good statistics
– Correlation and causation
• Harvard graduates earn more than high school dropouts, this
does not mean these dropouts, if given a Harvard diploma,
will earn as much
– Report favorable information while hide measurement
error / variable definition
4
Examples of statistical
manipulation
•
Executives at XYZ Corporation make an average annual salary of
$250,000.
– one earns $2 mill, the other 11 earn $90,000.
•
88% of those surveyed prefer QRS brand potato chips.
– only surveyed 9 people, 8 said yes.
•
ABC ink jet printers use 22% less ink.
– compared to what?
•
One drug treatment program has a 90% success rate.
– Drug free at the end of the program, or x months after finishing the program?
•
Graph manipulation:
– same data but different scale
Source: http://www.effectivemeetings.com/productivity/communication/statmanipulation.asp
5
Real examples
•
McCain Radio Ad in Florida – “Colombia Trade”: Three-quarters of
Florida’s exports are with Latin America.
– According to trade statistics generated from the U.S. Dept. of Commerce,
Florida’s international exports totaled nearly $45 billion in 2007, while exports to
Latin America and the Caribbean totaled nearly $24 billion, comprising only 53%
of exports. The three-quarters figure comes from the share of exports that
originates in Florida or passes through Florida on its way out of the country.
Source: http://www.factcheck.org/elections2008/errors_en_espantildeol.html
•
Democratic National Committee TV Ad: 1.8 million jobs lost (as
compared to 2001).
– In truth, total nonfarm employment was up by nearly 5.4 million compared to
when Bush took office. The 1.8 million figure comes from the number of people
without jobs which was higher by 1.8 million than when Bush took office.
However, this drop only means that job growth did not keep pace with the
number of people seeking work.
Source: http://www.factcheck.org/elections-2008/dnc_vs_mccain.html
6
My goal
At the end of the semester:
• You feel comfortable collecting, locating
and analyzing real data
• You are able to read, interpret and criticize
statistics generated by other people
• Given a real data set, you are able to
generate basic statistics by yourself and
give them meaningful interpretations
7
Our approach
• Get your hands dirty
• There will be a lot of trial and error
• We expect you to be:
– detail oriented
– patient
– creative in tying economics with real data
– cooperative with your classmate / team partner
• Let us know when you need help
8
Syllabus is at:
http://www.glue.umd.edu/~ginger/
Click on “ECON424” at the bottom
Assign project 1
9
Introduction to Excel
Handout from UMD peer training is available at
http://www.glue.umd.edu/~ginger/teaching/econ424spring2008/exceltutor.html.
Main contents:
• Open a file in N:\ and save it (as .xls or .csv) in M:\.
• Define observation and variable
• Hide/unhide rows/columns, freeze panes
• Change excel settings (Tools+options)
• Change cell/column/row formats
• Highlight (shift+, ctrl+)
• Formulas/dragdowns
• Charts
10
Extra excel to be covered in class
• Text to columns
• Insert text box in excel
• Transport excel table and chart into MS
words
• Generate random numbers
11
Class 2: Data Collection
(e.stat chapter 3)
• Start with a question
– characterize music downloads among 18-25
• Define observation
– an individual, a class, a school?
• Define variables
– use or not, intensity of usage, scope of usage
• Define sample
– Students enrolled? Students attending the
first class? Students attending the first class
and love music?
12
Data Collection
(continued)
• Methods of data collection
– Field collection by hand
– Experiment
– Survey / Questionnaire
•
•
•
•
In class
By phone
By email
Follow up survey
– Existing data sources (library, internet, etc.)
13
Data Collection
(continued)
• Things that need special attention
– Measurement error
• data collector’s preference, if revealed in the survey, may
bias respondent answer
• self report may be biased in a specific direction
• anonymous vs. identity-revealing
– Sample selection
• sample not representative by design (those attending first
class are different from those who don’t attend)
• missing values generate sample selection (need a follow
up?)
– Sample size and balance
• variations in the studied variables
• trade off between statistical power and cost of data collection
• similar size of comparable subsamples
14
Data Description
Before summarizing the data, clean it first!
• Sample – every student that attended 1st class
(may be different from the official roster)
• Unit of observation?
• # of observations?
• Variable(s)?
• Missing values?
• Abnormal values?
– Delete them, clean them? Be aware of the
assumptions you are making.
• What’s the # of observations after all the cleaning?
• Take a note of all the above!
15
Data Description
(e.stat chapter 4)
• Mean
N
unweighted:
x
x
Excel: =average(data)
i
i 1
N
N
weighted:
w x
i
x
i
i 1
N
w
i
i 1
Example: compute GPA (e.stat Figure 4.7)
16
Class 3: Data Description
• Median
Define: middle point in the data set
50% observations >= median
50% observations <= median
Excel: median(data)
If the distribution is symmetric, median=mean.
Unlike mean, median is insensitive to outliers.
Example: e.stat Figure 4.8
17
Data Description
• Trimmed mean:
Ignores a percentage of values that are
extreme and compute mean for the rest.
Excel: trimmean(data, percent)
Example: e.stat Figure 4.10
18
Data Description
• Order statistics
1st quartile (25% obs below)
=quartile(data,1)
2nd quartile = median
=quartile(data,2)
3rd quartile (75% obs below)
=quartile(data,3)
4th quartile = maximum
=quartile(data,4)=max(data)
60th percentile (60% obs below) =percentile(data,0.6)
0 percentile = minimum
=min(data)
range = max - min
Interquartile range = 3rd quartile – 1st quartile
Interquartile ratio = 3rd quartile / 1st quartile
Example: e.stat Figure 4.15
19
Data Description
• Sample variance and standard deviation
N
Var(x):
2
(
x

x
)
 i
=var(data)
i 1
N 1
Std dev:
var( x)
=stdev(data)
Note: sample variance not equal to population
variance
Example: e.stat Figure 4.15
20
Class 3: Data Description
• Other jargons
Mode: the most common value
=mode(data)
Skewness: asymmetry,
long right tail = positively skewed
=skew(data)
Kurtosis: peakedness, positive if peakier than normal
distribution
=kurt(data)
Example: e.stat sections 4-13, 4-18, 4-20.
21
Data Description
Summary: N:\share\notes\data-summary-formula.doc
Exercise: N:\share\notes\data-summary-exercise.xls
Variables already summarized: age, gender, music_source,
ipod_own, num_cds
Your exercise: num_song_purchased, last_acq, where_acq
Optional: num_free_download, num_songs
22
Class 3: Histogram
(e.stat Section 4-3)
• Histogram: A column chart in which line segments are graphed for
the frequencies of classes across the class intervals (bins) and
then each segment is connected to the X-axis to form a rectangle.
Count of obs in each bin
Frequency of scores
8
6
4
2
0
25
50
75
100
Upper limit of each bin
23
Class 4: Histogram
• Steps in drawing histogram:
– Define bins
• Must cover all the data (start from min or less, stop at max or more)
• Equal width
• The number of bins is between 5 and 20
– Count frequency in each bin
• Method 1: =countif(..)
• Method 2: =frequency(data, bins)
• Method 3: Tools – data analysis – histogram
– Plot histogram
• Note: histogram is a frequency chart that shows the
distribution of the raw data. It is not equal to highlighting the
raw data and plotting them directly.
24
Relative frequency polygon
•
•
•
(Absolute) frequency: number of observations per bin
 draw histogram as a column bar chart
Relative frequency: percentage of observations per bin
 draw relative frequency polygon as a line chart
Relative frequency polygon is more convenient to compare two data
sets, especially when they differ in the number of observations.
Relative freq
Relative frequency polygons
0.6
0.5
0.4
0.3
0.2
0.1
0
0
25
50
75
100
125
Upper limit of each bin
•
N:\share\notes\data-summary-example-fall2007.xls
25
Prepare for Project 1
• Require original data collected by yourself, no
simple download from a published source
• Submit two files:
– one excel (for data details)
– one word document (for description and summary)
• Common mistakes last semester
– Compare two relative frequencies using different bin
definitions
– Use histograms/relative frequency polygon to infer
correlation
– Treating categorical variable as continuous
– Spelling mistakes, forgot to label data sheets, data
description lack of details
26
Class 4-5: Probability Theory
(e.stat Chapters 5 and 6)
• Population: entire set of events that occur in a given
universe.
–
–
–
–
–
Event probability
Probability density function (PDF, f ( x) )
Cumulative density function (CDF, F ( x) )
Population statistics (mean, variance, etc.)
Certainty about a random process
• Sample: a subset of a population
– Data analysis
– Random by nature
– Sample statistics are random variables, but population
statistics are not!
27
Population mean
• For discrete
x
:
 prob( x )  x
E ( x) 
i
i
events
• For continuous
x
:
E ( x )   xi  f ( x )dx
x
28
Population variance
and standard deviation
• For discrete
2 
x
:
2
prob
(
x
)

(
x

E
(
x
))

i
events
• For continuous
x
:
   ( x  E ( x )) f ( x )dx
2
2
x
29
Compute E ( x),  ,  by hand or excel:
2
• Bernoulli distribution (flip a coin)
1
x
0
With probability
p
With probability 1 
• Flip n coins and define
• Roll a die
x  1,2,3,4,5,6
p
x  x1  x2 .... xn
?
Each value with probability 1 / 6
• Roll two dice?
• Roll n dice?
30
How to simulate in excel?
Bernoulli Distribution
1
x
0
With probability
p
With probability 1 
p
Hint: =rand() provides a random number between 0 and 1.
You can use rand(), but your formula must return integer
0 or 1.
Answer: =if(rand()<p,1,0)
What about flip n coins?
31
How to simulate in excel?
Roll a die
x  1,2,3,4,5,6
Each value with probability 1 / 6
Hint: Your formula must return an integer between 1 and 6,
with equal probability.
Answer: =round(rand()*6+0.5,0), or =randbetween(1,6)
Note: randbetween function may not exist in some versions of Excel.
What about roll n dice?
32
Compute E ( x),  ,  by hand or excel:
2
• uniform distribution between (a,b)
1
f ( x) 
ba
Answer:
ab
E ( x) 
2
2
(
b

a
)
2 
12
pdf
1/(b-a)
a
b
x
33
Normal distribution
• Normal distribution:
x ~ N ( ,  )
prob(     x     )  0.67
prob(   2  x    2 )  0.95
prob(   3  x    3 )  0.99
Example:
x ~ N (100,10)
f(x), cum(p(x))
Normal PDF
1.000
0.750
0.500
0.250
0.000
70. 80. 90. 100 110 120 130
0
0 0 .0 .0 .0 .0
x
34
What about
x  x1  x 2
?
E ( x1  x2 )  E ( x1 )  E ( x2 )
Var ( x1  x2 )  Var ( x1 )  Var ( x2 )  2Cov( x1 , x2 )
 ( x1  x2 )  Var ( x1 )  Var ( x2 )  2Cov( x1 , x2 )
Note: if both x1 and x2 are normally distributed, so is
x1+x2. But if x1 and x2 are uniformly distributed,
x1+x2 is not uniformly distributed.
35
Normalize
• If
x~
uniform on (a,b) then
xa
~ uniform on (0,1)
ba
• If
x ~ N ( ,  ) then
x

~ N (0,1)
36
How to simulate in excel?
•
Uniform on (a,b):
•
Hint: rand() gives you uniform on (0,1). You need to
adjust it to fit in the range of (a,b).
•
Answer: =a+rand()*(b-a)
•
Normal:
•
Answer: =norminv(rand(),μ,σ)
N( ,  )
37
Class 6-7: Central limit theorem
(e.stat section 10-05)
Given any population with mean  and standard
deviation
, for a large sample (N>30), we
have:

Distribution of the Sample Mean
N
)
probability
x ~ N ( ,

p(x)
p(xbar)
0.2
0.15
0.1
0.05
0
0
50
100
X, Xbar
Simulation: show-central-limit-theorem.xls
38
X2 and t distributions
•
Suppose X1,X2,…Xn are independent, ~N(μ,σ)
•
Then sample variance
 2 
•
1
1

(
nXXinin
)2
1
It can be shown that
n
(n  ) S n

( Xi  Xn )
i
~ X2 distribution of degree of freedom n-1
Xn  
 / n
•
Then
•
~ t distribution of degree of freedom n-1
t
39
How to simulate normal, X2 and t
distributions?
•
normal with mean μ and standard deviation σ
=norminv(rand(),μ,σ)
•
X2 distribution with degree of freedom df
=chiinv(rand(),df)
•
t distribution with degree of freedom df
=tinv(rand(),df)
•
simulation-exercise.xls in N:\share\notes
40
Show CLT in Excel (1)
• Choose a population
– type of distribution, e.g. Bernoulli
– distribution parameters, e.g. p=0.3.
• Simulate data
– 200 samples
– each sample of size N
• Lock in the simulated data so the samples
do not change later on
– copy, paste special
41
Show CLT in Excel (2)
• Calculate sample mean for each sample
• Compare the distributions of (1) all the raw
data and (2) all the sample means
– bin range must be wide enough to cover the
most dispersed distribution
– bin width must be narrow enough so that
there are at least 4-5 bins for the most
concentrated distribution
42
Summary of project 2
Relative Frequency Polygon
0.3
relative frequency for n=20
0.2
relative frequency for n=30
0.1
relative frequency for n=40
68
65
62
59
56
53
50
47
44
41
38
35
32
0
relative frequency for
rawdata
Bins
Relative Frequency Polygon (Uniform)
0.4
0.35
0.3
0.25
0.2
0.15
0.1
0.05
0
relative frequency for n=20
relative frequency for n=30
relative frequency for n=40
Bins
96
88
80
72
64
56
48
40
32
24
16
relative frequency for
rawdata
8
0
• Most students
perform well
• Correct
answer:
43
Common mistakes in project 2
• You should simulate from normal/uniform distribution, not from
Bernoulli.
• You should put raw data and sample mean in ONE relative
frequency polygon. To do so, you must have the same bin definition
for all the data series plotted in the graph.
44
Common mistakes in project 2
•Raw data relative frequency > 1.
Correct: Relative freq=abs freq/(100XN) instead of abs freq/100.
Relative Frequency Polygon for Normal Distribution
Wrong!
5
4.5
4
3.5
relative frequency for n=15
Frequency
3
relative frequency for n=30
relative frequency for n=45
2.5
relative freq. for raw data n=15
relative freq. for raw data n=30
2
relative freq. for raw data n=45
1.5
1
0.5
0
-16 -15 -14 -13 -12 -11 -10 -9 -8 -7 -6 -5 -4 -3 -2 -1
Bins
0
1
2
3
4
5
6
7
8
9 10 11 12 13
45
Common mistakes in project 2
• For normal distribution, bins start from miu-sigma.
Correct: bin should start from miu-3*sigma.
Wrong
Relative Frequency Polygon
0.3
relative frequency for
n=15
relative frequency for
n=30
relative frequency for
n=70
relative frequency for
raw data
0.25
0.2
0.15
0.1
0.05
0
0
6
12
18
24
30
36
42
48
46
Class 8: Mean estimation
• According to CLT, sample mean is an
unbiased estimate of population mean, but
with some errors.
• What is the distribution of x if
E ( x)  0,   10, N  100 ?
Answer: x ~ N ( E ( x),

N
)  N (0,1)
47
distribution of sample mean (xbar)
with E(x)=0
PDF
2
E ( x) 
N
E ( x) 
95% chance within this range
2
N
0.4
0.2
0
-3
-2.2
-1.4
-0.6
0.2
1
1.8
2.6
xbar
48
But …
• Usually we try to guess what E ( x) is
2
2 

prob E ( x ) 
 x  E ( x) 
  0.95
N
N

2
2 

 prob x 
 E ( x)  x 

N
N

• We don’t know  either
• Use the sample to take a guess on

49
Confidence Interval
• Given confidence level
  0.95
(for example)
ts
ts 

prob x 
 E ( x)  x 

N
N

• Where s=est. std. dev.=stdev(data)
t= t value = tinv(1-alpha, N-1)
• Or xbar +/- confidence(1-alpha, stddev, size)
• Note: In Excel, confidence function assumes we know population
standard deviation and therefore does not use t-value
• Exercise: e.stat problems 12.1 and 12.2
50
Class 9: Hypothesis testing
• Null hypothesis
H0: E ( x)    0
• Alternative hypothesis:
H1:
E ( x)    0
two-tail test
E ( x)    0
one-tail test
E ( x)    0
E ( x )    1
51
Logic
•
•
•
•
Assume H0 is right
Choose a confidence level   0.95
Compute prob(get the sample mean)
Reject H0 if prob(..) is too small, otherwise
accept H0
52
Types of Error
• Type I Error: reject correct H0 (false neg)
• Type 2 Error: accept wrong H0 (false pos)
p(x)
PDF of Sample Mean: H0 and H1
0.5
0.4
0.3
0.2
0.1
0
0.94
p(x)-alt
0.96
0.98
1
x
Critical Value
1.02
1.04
p(x)-null
mu's
53
In practice
• Method 1 (two tail test only)
– Compute x and confidence interval
– Accept H0 if  0 falls in the confidence interval
– Reject H0 if  0 falls out of the confidence interval
Accept
(prob=alpha)
Reject
((1-alpha)/2)
((1-alpha)/2)
x
PDF
0.5
Reject
0
-3
-2.2
-1.4
-0.6
0.2
1
1.8
2.6
54
In practice
• Method 2:
x  0
t
s
– Compute t-statistics
N
Degree of freedom
– Compute critical value
=(+/-)tinv(1-alpha, N-1) (if two-tail)
=(+/-)tinv((1-alpha)*2, N-1) (if one-tail)
– Accept (reject) H0 if t falls in (out of) the critical value
55
Two-tail critical values
Accept
(prob=alpha)
Reject
((1-alpha)/2)
Reject
((1-alpha)/2)
t
PDF
0.5
0
-3
-2.2
-1.4
-0.6
0.2
1
1.8
2.6
56
H1:
  0
One-tail critical value
Reject
Accept
(prob=alpha)
(1-alpha)
t
PDF
0.5
0
-3
-2.2
-1.4
-0.6
0.2
1
1.8
2.6
57
In practice
x  0
• Method 3:
t
s
– Compute t-statistics
N
– Compute p-value = prob(t-stat>=|t|)
=tdist(|t|, N-1,2) (if two-tail)
=tdist(|t|,N-1,1) (if one-tail)
– Reject H0 if
p-value<(1-alpha)
Accept H0 if
p-value>(1-alpha)
58
Exercise
•
•
•
•
What is H0? What is H1?
Two-tail or one-tail?
Choose a method
E.stat problems:
– two-tail test: e.stat 13.13 and 13.17
– One-tail test: e.stat 13.20 and 13.E10
59
Two-tail vs. one-tail
• Two-tail test does not indicate which direction to
go if we reject H0:   0 , so the alternative is
H1:   0 .
• One-tail test has a strong view of one direction.
For example, a saleman wants to know whether
sales have increased from the past, in which
case the alternative is H1:   0 . If he is
worried if the sales have decreased, the
alternative will point to another direction where
H1 is   0 .
60
In Excel (alpha=95%):
Two-tail
• H0:  
• H1:  
• T-stat:
0
One-tail
• H0:  
0
One-tail
• H0:  
0
• H1:  
0
• H1:
x  0
s/ N
• T-stat:
x  0
s/ N
0
  0
• T-stat:
x  0
s/ N
• Crit.Val:
(+/-) tinv(0.05,N-1)
• Crit. Val
• Crit. Val:
= + tinv(0.10,N-1)
= -tinv(0.10,N-1)
• Reject if
t > + crit. val.
or t< - crit. val
• Reject if
t> crit. val.
• Reject if
t<crit. val.
61
Three methods, same result
Two-tail
• H0:  
• H1:  
0
Two-tail
• H0:  
0
Two-tail
• H0:  
0
• H1:  
0
• H1:
• Conf. Interval
[ x  ts / N ,
x  ts / N ]
• T-stat:
x  0
s/ N
0
  0
• T-stat: x  0
s/ N
• Crit.Val:
(+/-) tinv(0.05,N-1)
• P-value:
tdist(|t-stat|,N-1,2)
• Reject if
t > + crit. val.
or t< - crit. val
• Reject if
p<0.05
tinv(0.05,N-1)
• Reject if
0 is out of
conf. interval
62
Class 10: two sample testing
• One sample test H0:   0
• What if we don’t know 0 but have two
samples
• Can we compare x1 and x 2 ?
• Yes, must account for errors in both
63
Two independent samples
• H0: 1  2
• H1: 1  2
(two-tail)
1  2 (one-tail)
• Independent  errors in the two samples are
independent
( x1  x 2 )  ( 1  2 )
t
s12
s22

N1 N 2
• Degree of freedom [min(N1,N2)-1]
• Exercise: e.stat problems 14.5, 14.6
64
Two matched samples
(same subjects, N1=N2)
• H0:
• H1:
•
•
•
•
•
•
1  2
1  2 (two-tail)
1  2 (one-tail)
Generate a new variable dx=x1-x2
Transform H0: dx  1  2
Now is a one-sample test
Degree of freedom N1-1
Example: e.stat figure 14.8
Exercise: e.stat problem 14.7
65
How to tell if two samples are
matched or not?
• N1=N2 for matched pairs
• Same subjects?
• If resorting one sample does not affect the
comparison, they are independent
66
Class 11: regression
• “Regress Y on X” means:
Error term
y  x
Independent variable(s)
Dependent variable
coefficients
67
Ordinary least squares (OLS)
Goal: Find a linear line that best fits the data
( yi  y i ) 2   ( yi      xi ) 2
Best: min

 ,
i
i
Scatter Plot: Consumption on
Income
Slope
(random!)
 ( x  x )( y  y )
 
 (x  x)
i
i
i
2
i
i
  y    x
Intercept (random!)
Average point
the line
( x , y)
is always on
Y: Consumption
Solution:
60.00
50.00
40.00
30.00
20.00
10.00
0.00
0.00
20.00
40.00
60.00
X: Disposable Income
68
Test coefficients
• Estimated coefficients are random numbers! – depend
on data
• The point estimates should be judged together with their
standard errors
• Hypothesis test H0:   0
• T-statistics
  0
t
stderr ( )
• Critical values (assuming conf. level=95%)
=tinv(0.05,N-2) for two-tail
Two-tail or one-tail depends
on H1
=tinv(0.10,N-2) for one-tail
• P-values =tdist(|t|,N-2, 2 or 1)
69
Measure the fit of OLS
• Total sum of squared deviations (TSS):
2
(
y

y
)

i
• Decompose TSS:
2

(
y

y
)
– Explained by the model: 
– Unexplained residuals:
i
2

 ( y  y)
i
• R-square: (explained)/(total)
70
F-test
k=number of coefficients
N=number of observations
ExplainedS S /( k  1)
F (k  1, N  k )  - - - - - - - - - - - - - - - - - - - - - - 
Unexplaine dSS /( N  k )
R 2 /( k  1)
--------------------(1  R 2) /( N  k )
H0: all the coefficients except the constant
term are zero.
H1: some of the non-constant coefficients
are not zero.
71
• Correlation coefficient:

 (x
i
 x )( yi  y )
i
2
2
(
x

x
)
(
y

y
)
 i
 i
i
i
Note:
1. Correlation coefficient is between -1 and 1. What does it mean if
correlation coefficient is equal to -1, 0, or 1?
2. correlation coefficient is symmetric, i.e corr(x,y)=corr(y,x), but OLS
coefficients aren’t. This means regress y on x is not equivalent to
regress x on y.
3. Correlation coefficient is always of the same sign as the OLS slope
coefficient.
4. R-square = (correlation coefficient)^2
72
Regression in Excel
• Method 1:
=linest(data of y, data of x, include const?, other statistics?)
No output labels, must be familiar with the output layout
• Method 2: Tools - data analysis – regression
• Note: you could have multiple x, but they must be
adjacent to each other.
• Example: e.stat Figure 19.7
• Exercise: e.stat problems 19.E1, 19.E2, 19.E3
73
Assumptions and Caveats in OLS
(e.stat section 22-04)
•
•
•
•
Errors have mean zero.
Errors have a constant standard deviation.
Errors are drawn independently.
Errors are uncorrelated with x. All the
important x are included in the regression.
(omitted variable bias, see e.stat Figure 22.4.)
• Errors are distributed normally.
74
Class 12: Midterm Review
– open book
– understand the concepts
– use them in real examples
– 9:30-10:45am, Plant Sciences 1129
– If you cannot attend the midterm for reasons
that are consistent with University Policy,
please let me know AT LEAST 12 hours
BEFORE the midterm time, otherwise your
midterm grade will be counted as zero.
75
Concepts to grasp (1)
• Population / sample
• Population
– Cdf (prob(var<x))
– Pdf (first derivative of cdf)
– population mean, population std. dev.
• Sample
– Histogram, frequency polygon, quartiles, percentiles, sample
mean, sample std. dev., skewness, kurtosis
• Population  sample
– Central limit theorem xbar~N(µ, σ/sqrt(n))
• Sample  Population
– Xbar is a proxy of µ with noise
76
Concepts to grasp (2)
• Inference
– Type I error, Type II error
– Confidence level α
– Confidence interval
– Hypothesis testing
•
•
•
•
H0
H1
Accept/reject?
One-tail, two-tail test
77
Summary of Excel (1)
• Basic excel
– open, save and close files
– cut, paste and paste special
– change format for cell, row or columns
– sort data by one or two variables
– chart wizard
– freeze panes
– drag cells
– use excel functions
78
Summary of Excel (2)
• Data description
– mean, median, trimmed mean
– standard deviation, variance
– quartiles
– mode, skewness, kurtosis
– histogram (absolute frequency)
– relative frequency polygon
79
Summary of Excel (3)
• Probability theory
– PDF, CDF
– mean and standard deviation
– bernoulli, binomial
– uniform, normal
– how to simulate them in Excel?
– Central limit theorem
– how to see central limit theorem in excel?
80
Summary of Excel (4)
• Estimation and Hypothesis testing
–
–
–
–
–
–
–
–
–
use sample mean to estimate population mean
confidence interval
type I error and type II error
null hypothesis (H0) and alternative hypothesis (H1)
one-tail vs. two-tail
t-statistics, critical value, p-value
one-sample test
two-sample test (independent)
two-sample test (matched pair)
81
Summary of Excel (5)
• Linear regression
– model
• one variable on the right hand side
• more than one variables on the right hand side
• create and use binary variables
– fit of the model
•
•
•
•
R square
F test
scatter plot
correlation coefficient
– coefficient estimates
• point estimate
• hypothesis testing
• omitted variable bias
82
Class 13: Midterm
Class 14: Midterm grades
83