Download Chapter 7, Using Excel: Confidence Intervals and more Study notes MS Microsoft Excel skills in PDF only on Docsity!
Chapter 7, Using Excel:
Confidence Intervals
These pages demonstrate the Excel functions that can be used to calculate confidence intervals.
• Chapter 7.2 - Estimating a Population Mean (σ known) 2
Here, Excel can calculate the critical value (zα/ 2 ) and/or the margin of error (E) defined by
E = zα/ 2
σ √ n
This uses the NORM.S.INV and/or the CONFIDENCE.NORM functions.
• Chapter 7.3 - Estimating a Population Proportion 3
Here, Excel can calculate the critical value (zα/ 2 ) used in the margin of error defined by
E = zα/ 2
√ p ˆ qˆ
n
This uses the NORM.S.INV function.
You must then complete the calculations to get the margin of error (E).
• Chapter 7.4 - Estimating a Population Mean (σ unknown) 4
Here, Excel can calculate the critical value (tα/ 2 ) and/or the margin of error (E) defined by
E = tα/ 2
s √ n
This uses the T.INV and/or the CONFIDENCE.T functions.
Chapter 7.2 - Estimating a Population Mean (σ known)
Here, Excel can calculate the critical value (zα/ 2 ) and/or the margin of error (E) defined by
E = zα/ 2
σ √ n
- Notation:
- E = the margin of error
- zα/ 2 = the critical value of z.
- σ is the population standard deviation
- n is the sample size
- α = 1 − confidence level (in decimal form)
∗ If the confidence level is 90% then α = 1 − .90 = 0.10.
∗ If the confidence level is 95% then α = 1 − .95 = 0.05.
∗ If the confidence level is 99% then α = 1 − .99 = 0.005.
- Finding the critical value zα/ 2
Here we use the NORM.S.INV function.
NORM.S.INV stands for the inverse of the standard normal distribution (z-distribution).
General Usage: NORM.S.INV(area to the left of the critical value)
Specific Usage: zα/ 2 = NORM.S.INV (1 − α/2)
Example: If you want zα/ 2 for a 95% confidence interval, use
zα/ 2 = NORM.S.INV(0.975) = 1.
- Finding the margin of error E
Here we use the CONFIDENCE.NORM function.
CONFIDENCE.NORM stands for the confidence interval from a normal distribution.
Usage: CONFIDENCE.NORM(α, σ, n)
Example: If you want a 95% confidence interval for a mean when the population standard deviation
is 10.2 from a sample of size 35, the margin of error would be
E = CONFIDENCE.NORM(0.05, 10.2, 35) = 3.
Chapter 7.4 - Estimating a Population Mean (σ unknown)
Here, Excel can calculate the critical value (tα/ 2 ) and/or the margin of error (E) defined by
E = tα/ 2
s √ n
- Notation:
- E = the margin of error
- tα/ 2 = the critical value of z.
- s is the sample standard deviation
- n is the sample size
- α = 1 − confidence level (in decimal form)
∗ If the confidence level is 90% then α = 1 − .90 = 0.10.
∗ If the confidence level is 95% then α = 1 − .95 = 0.05.
∗ If the confidence level is 99% then α = 1 − .99 = 0.005.
- Finding the critical value tα/ 2
Here we use the T.INV function.
T.INV stands for the inverse of the t-distribution.
General Usage: T.INV(area left of critical value, degrees of freedom)
Specific Usage: tα/ 2 = T.INV (1-α/ 2 , df)
Example: If you want tα/ 2 for a 95% confidence interval based in a sample of size 20, use
tα/ 2 = T.INV(0.975, 19) = 2.
- Finding the margin of error E
Here we use the CONFIDENCE.T function.
CONFIDENCE.T stands for the confidence interval from a t-distribution.
Usage: CONFIDENCE.T(α, s, n)
Example: If you want a 95% confidence interval for a mean with a sample standard deviation of 10.
from a sample of size 35, the margin of error would be
E = CONFIDENCE.T(0.05, 10.2, 35) = 3.
