Welcome to Excel 2013 Statistical Analysis
video number 55.
If you want to download this workbook or the PDFs,
click on the link below the video.
OK over in the PDFs near the end, pages 30 through 33
it has all of the Excel functions
for all the formulas we're doing for hypothesis
testing in this chapter 9.
So that's a good one to download.
OK this is our third video.
In this video, we're going to see
how to do a hypothesis test when sigma is known
and it's a two tail test.
Now last video we did, we looked at bottles of yummy red ketchup
that had labeling of 16 ounces.
And we did it from a consumer group's point of view
where they thought they were potentially under-filling.
This example is going to be from the manufacturer's
point of view.
And we'll see that how we set up the hypothesis test
is different than the consumer.
This is the manufacturer, so the point of view
will be different, and the setup will be different.
So we have this yummy red ketchup.
The label says 16 ounces.
The bottle filling factory wants to make sure the filling
machine is filling accurately.
They do not want to fill too little
and make the customer unhappy.
And they do not want to fill too much and thus decrease profits.
At the alpha of 0.05, is the process out of control?
I mean, is the machine filling correctly?
And we know the population standard deviation
from past data.
All right so, always important to know your point of view.
The point of view of this example
here is manufacturers want to make sure that the machine is
filling accurately.
We're considering the population of all possible bottles that
could come off the filling line.
And our goal is to run a hypothesis test
to provide statistical evidence to show whether a machine is
filling accurately, yes or no.
This is a two tail, right?
Because we care on either end, if it's too much or too little.
The first two examples we did in the last two videos,
we cared about too much or too little.
This one we care about both.
So I'm going to go straight to the null hypothesis.
And then I'm going to type space equals and 16 ounces.
Now I'm going to put hypothesized
mean down here, 16 ounces.
All right, and then I'm going to come up
here and make a link to that.
All right and last two examples, we
started with the comparative operator
for the alternative hypothesis.
But this is a two tail, so we go hey, we start with that one
if this one's equal, this one is not equal.
So I'm going to space, less than, greater than.
Now that's just how you do not comparative operator in Excel.
All right and this one's 16 ounces also.
So there's our alternative, our null.
The null's going to indicate that it seems reasonable
that the machine is filling accurately.
If we accept the alternative, this
will indicate the machine is not filling accurately.
If it's not filling accurately, then you
need to shut it down and fix the filling equipment.
And this is a common example for machines
that fill things, whether it's lettuce, or bags
of M&M's, or whatever it is.
All right our alpha, we're going to use 0.05.
We'll come down here, we'll put in our details
and then do some calculating.
We know that population standard deviation is 0.5 ounces.
We don't have sample, we will next
video when we do the t-distribution.
The statistic to use, well, it's z, because we know sigma.
The sample size equals count because we have numbers.
Control, shift, down arrow.
Shift, enter.
So we have a sample of 36.
So we went out there and took right off the filling machine
line a sample of 36.
Our x bar, we use the average function to calculate the mean.
Control, shift, down arrow.
And then enter.
Alpha, the type of test this is a two tail.
All right, our standard error that's
our standard deviation for the sampling
distribution of x bars.
We're going to say our sigma divided by the square root
of our n, 0.083.
Our test statistic, particular x bar minus hypothesized mean.
Actually, I'm going to round this
just like we did in the last video,
and round it to the whatever formula
it is, or you use the round function.
That number just means the thing and this
can be as small or as big, because sometimes they're big.
And then you comma and tell it how many digits.
We're going to round to the second digit.
All right now let's do our test statistic.
The numerator is x bar minus mu.
That tells us the actual sampling error, or the point
estimate of the difference.
Now we're going to divide it by our standard error.
So we get 1.44.
So that's 1.44 standard deviations above.
Let's look at this picture here.
All right so there is a picture of the situation
when you're doing a two tail.
You have these two hurdles.
Now we're given alpha of 0.05.
So the alpha on each end has to add up to 0.05.
So we have to divide by 2, so it's going to be 0.025.
That determines the hurdle there.
0.025 determines the hurdle there.
Anything to the left or to the right we reject.
Our null hypothesis accept the alternative.
If we reject, it means the machine is not
filling accurately.
Any value within here, it seems reasonable
that the machine is filling accurately.
All right so let's calculate our p value.
Now when you get to two tail tests,
you've got to think about this.
Here's this 1.44, and the way p value works,
is wherever it is here, wherever it
is, it tells you with a positive number
the probability of getting 1.44 or more.
That's just on this side.
When there's a two tail test, you
have to double whatever single p value you get,
and we'll see a picture of that down here in just a moment.
Now there's two different ways we can do this.
I'm going to start with the idea that 1.44, well these functions
go from negative infinity up to that 1.44,
and I'm interested in the upper part.
So I'm going to say equals 1 minus
and then do my norm s dist, our z, comma 1 cumulative member.
That 1.4 calculates everything from here up to that 1.44.
But since I'm interested in the upper end, I do 1 minus.
Now that gives me 7.5% or 0.075 approximately.
But this is a two tail.
That test says the probability in the upper end,
you have to double it for a two tail.
Now if I come to the end and multiply by 2,
that will not work because we have to force
that subtraction first.
So I'm going to force the subtraction first.
Now actually this formula that I'm just
did right there is not in the PDFs.
Here's the one that's in the PDF.
And either one is fine, because this curve is symmetric not
the way I draw it.
But the standard normal curve, or the sampling distribution
of x part is symmetric.
If we have 1.44 here, the 1.44 on the low end
is symmetrically placed and the probability below it
is the same as the probability above the 1.44 here.
So we can not use them 1 minus and all that parentheses.
If we choose to use norm dot s dist and trick it.
If it's a positive here, I'm just going to see a negative.
OK, so what that's going to do, if there's a 1.44 here,
probability above, I'm tricking it.
I'm going to say the probability below
and that gives me that same 7 and then you can multiply by 2.
Either one is fine.
Now clearly with our p value rule,
it's always going to be any time p
value is less than or equal to alpha, reject h sub 0,
accept h sub a, otherwise fail to reject.
So in this case, we just flat out fail to reject.
14 is bigger than 5.
Now the critical value method, that's
where we calculate based on our alpha z
and then compare it straight to here.
This will be the hurdle.
If this is past, this 1.44, is past the hurdle,
we know to reject h sub 0.
So, we're going to use norm dot s inverse.
Now I want to calculate the low end, right,
so this is alpha divided by 2.
So I'm simply going to take alpha divided by 2 minus 1.96.
And if we want, since it's symmetric,
we simply have to take the opposite of that.
Now watch this.
Usually we go equals minus.
If you just type minus and click right here,
control enter, what it does is, if you put a minus or plus
as the first character in the cell,
Excel knows it's a formula and puts in the equal sign for you.
All right, so this rule, critical value rule,
is the test statistic if it's between those two,
not including the ends, then you fail to reject.
So that's this region right here.
Now actually I wrote this rule slightly
different than the book and also in my PDFs
because I talked about this region here.
It's just a little bit shorter.
If I say if the test statistic is between these,
I fail to reject h sub 0, otherwise I reject h sub 0,
accept h sub a.
It's just a little bit shorter when you write it out.
So I put the test statistic is between the negative
and the positive critical value, we fail to reject h sub 0,
otherwise reject h sub 0 and accept h sub a.
All right, so in either case, we have
come to the same conclusion.
Let's look at our picture here.
Again, here's that picture of the p value.
We have a 1.44.
The p value tells us the probability
of getting that or greater.
We have to double it.
So we double it, times 2.
So we get about 0.15, if you round that right there.
Here's our critical values include the test statistic is
between those.
This is not symmetric, I didn't draw that one very well.
And now our conclusions.
Based on the p value, because the p value of 0.15
is greater than alpha 0.05 we fail
to reject the null hypothesis.
Critical value because the test statistic of 1.4
is between our two critical values,
we fail to reject h sub 0.
That means the sample evidence suggests that the machine is
filling properly.
Set another way, at alpha of 0.05,
the sample mean of 16.12 ounces does not
provide statistically significant evidence
to suggest that the process is out of control.
In essence, our sampling error was not
significant enough for us to stop the machine,
take it apart, fix it.
We do run a 5% risk of a type one error, which
means we might say the machine is filling accurately
when it is not.
All right so that's the third example of hypothesis testing.
All three of our examples so far have been sigma known.
We've seen upper tail, one tail to the right,
lower tail, one tail the left, and two tailed.
In our next video, we'll talk about what
we do when we don't know sigma, we use the t-distribution.
All right, see you next video.