Welcome to Excel 2013's statistical analysis video
number 52.
Hey, if you want to download this Workbook Business 210
Chapter 8 or the PDFs for Chapter 8, click on the link
below the video.
In this video here, we want to estimate sample size.
Now, let's go over to our PDFs.
Here's the confidence, the general form of our confidence
interval that we've been using.
Some point estimator plus or minus a margin of error.
And the thing is if we know this,
meaning the margin of error, we can solve for n.
So here's, rewritten, point estimator plus or minus.
We'll just say e for error.
Well, there's our formula for sigma known.
This is our margin of error.
We just set that equal to e.
We can then solve for that little piece right there.
And this is what we get n equals z on the upper end times
sigma divided by e squared.
Similarly, we can do a calculation for n
when we're estimating population proportion.
Now, this right here in the p need to be estimated.
Let's go to our next page.
So here it is, we rewrote it here.
We're solving for n.
How do we get that sigma?
We could use an estimate from previous studies or government
studies, do a pilot study before you run the experiment
or even estimate large and small values and divide by 4.
This is an approximate.
Also when we're doing this, we're
always going to round up when calculating n.
So we use round up.
In previous videos, we use the round function here.
We're going to round up.
And we'll use 0 and say that rounds up to the integer.
Next slide here for estimating sample size n.
When we're doing an interval for a population proportion,
we're going to have to estimate these.
Similarly, we could use previous studies, government data,
run a pilot study beforehand.
However, for the population proportion
when you're estimating, if you don't have any of these,
you could usually do 0.5.
Now, there's a great table in the textbook that shows you
what happens as our p changes.
But let's just think about this, if you
use 0.5, that will yield the biggest sample size.
So if you just have no idea how to estimate it,
you can use just 0.5.
Now check this out, what if p was 0.1.
0.1 times 0.9 is 0.09.
0.2 times 0.8 would be 0.16.
0.3 times 0.7 would be 0.21.
All the way till 0.5 times 0.5 would be 0.25.
So the biggest possible value here you could get is 0.25.
And that would yield the biggest sample size.
Now, let's go over to Excel.
We're over here on the sample size sheet.
Here's our first example, a consumer group
would like to estimate the mean monthly electricity
charge for a single family house in July within $5.
That'll be our error.
Using the 99% level of confidence,
we have our estimate of sigma and we have
to calculate our sample size.
There's our error, our estimate of sigma,
and our confidence level 0.99.
We'll calculate our alpha equals 1 minus this.
That's the total risk.
We've got to calculate the alpha on the upper end.
So we can calculate our z on the upper end.
So we'll take that alpha divided by 2.
Use our NORM.S.INVERSE.
Probability, 1 minus.
We have our z.
Now, we can calculate our estimate of a sample size.
Equals, in parentheses, we'll take our z times our estimate
of sigma divided by our error.
Close parentheses.
And then caret 2, all of that has to be square.
106.
Now, we need to round up.
We're going to use the round up function.
Equals round-- notice there's round down, round up.
We're going to use round up.
It'll always go up.
That's the number column.
And you have to tell it to what position.
We want it to the integer, so we put 0.
Now, we did this calculation in the separate cell.
You certainly don't have to.
You can do it all in one cell if you'd like.
So there's our estimate for our sample size, 107.
Let's look at our second example.
So the American Kennel Club wanted
to estimate the proportion of children
that have a dog as a pet.
If the club wanted to estimate our estimate
to be within 3% of the population proportion,
how many children would they need to contact?
We'll do 95% level of confidence.
The club estimated that 30% of the children
have a dog as a pet.
So that's our estimate for p.
Technically, this isn't p bar because what are we doing?
We're calculating the n before we ever do the sample.
So we don't have p bar yet.
But we have this estimate for population p.
All right, our alpha, alpha divided
by 2, that will help us get our z on the upper end.
equals NORM.S1 minus alpha on the upper end.
So our sample size estimate, we have our p times 1 minus our p.
And then I'm going to have to multiply that.
In parentheses, we have the z divided by our error, z error.
Close parentheses and we'll square that.
896.
So we'll round up.
Now notice if we were doing normal rounding,
it would be 896.3.
So for rounding there, it would go to 896.
But round up.
It's always going to go up.
You tell it to the integer with a 0.
And boom, so we'll take 897, our estimate for sample size.
All right in this video, we saw two different formulas
for estimating sample size.
That's our last video for Chapter 8.
Next chapter, Chapter 9 we'll do hypothesis testing.
See you next video and chapter.