Welcome to Excel 2013 statistical analysis video
number 51.
Hey, if you want to download this workbook,
business 210 chapter 8, or our PDFs, click on the link
below the video.
In this video, we want to talk about how
to estimate a population proportion by building
a confidence interval.
Here's our formula.
And it looks very similar to the formulas
we used for sigma known and sigma not-known when we
use the Z and T distribution.
Here we're going to use the Z distribution, our normal NORM.S
inverse functions, for example.
We're going to have a sample P, a sample proportion,
called Pbar plus or minus some margin of error.
Now we've seen the Z sub alpha divided by 2,
that's our upper Z, times--
and there's our standard error.
Square root of Pbar times 1 minus Pbar divided by N.
So this is the formula we're going
to use for building our confidence interval to estimate
our population proportion.
Let's go over to Excel.
All right.
So here's our example.
A large Seattle coffee company says
that Excel skills are important for the 600-plus employees that
work in the marketing and accounting department.
A sample of potential employees who
said they were knowledgeable with Excel is below.
So what they did is they went out
and they asked potential employees,
are you knowledgeable?
Now notice the employee is answering either yes or no.
So let's think about this.
This is a binomial experiment.
This is a nominal variable, and there's two possibilities.
Now, in order to use the normal distribution,
we have to make sure and check that our binomial tests are
met, and there are four of them.
Experiment consists of sequential and identical
trials.
So in essence, fixed number of identical trials.
Yes, that's true.
Two outcomes.
Identical in each trial.
Each trial only results in a success or failure.
So they either answer yes, they say
they're knowledgeable in Excel, or no, they're not.
And you can imagine that this is a question on some document
that they're submitting as a potential employee, right?
Because they want to know if you're
going to work in marketing and accounting,
you better be good with Excel.
This would probably a first step.
Then once you say yes, maybe you get an interview or a test.
All right.
So yes, there's a success or failure on each trial.
Probability of success.
Yeah, you can either get a yes or no.
So that's the same each time.
The trials are independent.
So each potential employee is not affecting the next.
We'll say yes.
So all the tests are met.
Now one, two, three, four.
There is actually one further test.
We're going to have to check whether N times P is greater
than 5 and N times 1 minus P is greater than 5.
If both of those are true, then where we can use the normal.
All right.
Here's our data set here.
I'm going to Control-Shift-Down.
So there's-- wow.
Calculate our sample size.
So =COUNT.
Now count counts numbers and COUNTA counts non-empty cells.
And since we have words here, we want
to count how many cells have words in them.
So we use COUNTA.
All right.
Now we have to count yes and count no.
Our success will be yes.
We use the count if.
So don't count all of them like COUNTA did, but count only some
of them.
So we give it the range Control-Shift-Down Arrow.
F4 to lock it.
Notice the range, there it is.
Comma, and then the criteria.
Now I said no here and yes right there.
I can't quite click on it so I'm going to use my arrow
key to go one cell to the left.
That'll count what?
The yes's.
Control-Enter, and there we have it.
All right, now the probability.
We have our total number in the sample.
Our number of successes.
So we'll do equals the number of people that responded
yes divided by the sample size.
Now I'm going to lock this with the F4 key
so I can copy this down.
The blue boxes on the yes, when I copy it down,
the blue box will move down to no.
So 32 percent of these people have the potential
of getting an interview or whatever
it is this company is going to do with this.
That's the sample proportion.
It's our point estimate.
It's the best estimate we have of our population parameter.
We'll call this Pbar, and this the proportion
of potential employers who say they know Excel well.
Now let's test to see if we can use the normal distribution.
Equals sample size times our P. And I'm going to lock this.
F4.
Because the green box is P. This one down here is 1 minus P.
So in both cases, we're greater than 5.
We can use the standard normal curve.
All right.
Let's calculate.
We'll do a confidence interval of 95.
We'll say equals 1 minus confidence interval
or confidence level to give us our alpha.
Alpha divided by 2, just as we've done so many times so far
in this chapter.
That's the risk on the upper end.
We'll calculate our Z on the upper end.
And we can use equals norm.
There's NORM.INVERSE.
But NORM.S.INVERSE gives us the Z.
Now the probability-- we need all the probability
minus the little bit on the upper end.
So we'll say 1 minus.
So there is our upper--
1.96 approximately.
Our standard error, we have our formula right here.
We saw it over in the PDF.
P times 1 minus P divided by N. So I'm
going to get the P all in the square root.
The P times 1 minus divided by our N.
Now the order of operations will work fine here
because the multiplication will be done first and then
the division.
And we get 0.014.
That's our standard error.
Now our margin of error.
We're going to say Z times the standard error.
And then there we have our margin of error.
That's the amount to add on either side of our Pbar right
here.
So the lower limit.
I'm going to subtract.
Equals the upper limit.
So we have our Pbar plus our margin of error.
So now we have an estimate.
Our conclusion-- the 95% confidence interval
for the proportion of potential employees
who say they know Excel, well, is 0.2942 to 0.34.
So about almost 30% to almost 35%.
So 30% to 35%.
Somewhere in that interval, we're
estimating at 95% confidence that our population proportion
exists.
Let's go look at a second example.
Let's go over to Pbar 2.
So here's our second example.
Furniture Land South surveyed their customers N equal 600--
that was a sample size-- to see if they
liked the new line of durable foam furniture decorate
in bright colors.
414 said they were excited about the new line.
All the binomial tests are met.
All right.
So our variables.
We have x number of successes.
That is, we like it.
Sample size 600, so we can figure out
the Pbar for our sample proportion
of the number of people that said they liked it.
414 divided by our N--
0.69.
So our sample proportion is the best estimate
for the population proportion, right?
And so the owner of Furniture Land is saying well, hey.
That's pretty good.
Maybe this is a potential product we can launch.
Now let's come down here.
We have our Pbar up here.
Level of confidence-- let's do 0.99.
We want to be really sure.
Have 1% risk of that alpha.
So 1 minus that.
So alpha divided by 2, a half a percent, or 0.005.
So that divided by 2.
Our Z, we're going to do not norm as there.
It's NORM.S.INVERSE.
Without the dots is the function used in earlier versions.
So NORM.INVERSE.
So inverse wants to probably-- now,
notice this is a teeny little bit of risk on the upper end.
So I'm going to say 1 minus that.
All these probability functions go from negative infinity
up to whatever x bar or probability marker.
So there we go.
So 2.57.
That's our Z. So our standard error--
we'll go ahead and do that same square root.
We have P times--
and here we're going to have to solve for 1 minus,
because in the last example we had it, but here we don't.
So 1 minus that.
Divided by our count, which is 600.
So 0.018, our margin of error.
We'll take our standard error times our Z. Equals
standard error times Z. Now we have our margin of error.
We just simply take our Pbar.
Subtract the margin of error.
Take our Pbar and add the margin of error.
OK.
So we have two values.
A lower and an upper that span an interval
where we're 99% sure that our population proportion exists.
So the owner can be 99% sure that the population proportion,
the percentage of customers excited about the new product,
is between about 64% and about 74%.
The margin of error is about 5%.
So there is our margin of error.
So a pretty good proportion of customers that are excited.
And so then the owner says, well, hey.
Maybe we should launch this product.
All right.
So two examples in this video of estimating the population
proportion based on sample data.
We have one more topic in this chapter-- determining
sample size.
See you next video.