Welcome to Excel 2013, statistical analysis video
number 54.
And if you want to download this workbook, business 210
chapter 9 second, we already had one workbook
for the last field.
This is the second one.
Or you want to download the PDFs, click on the link
below the video.
Now, here's our second example of a hypothesis test.
And I just want to remind you that over in the PDFs on page
30, 31, 32, 33, there's some great notes on Excel functions
and the different types of hypothesis tests
and stuff like that.
All right.
Last video, we did hypothesis testing.
We knew sigma population standard deviation,
and we did a one tailed test to the right.
This one, we're going to know sigma.
That's going to be a one tailed test to the left.
Here's our example.
A bottle of yummy red ketchup is labeled
as having 16 ounces of ketchup.
A consumer group in the Green Valley area
believes that the bottles are filled with less than 16 ounces
and ask a government agency to investigate the under filling.
At the 0.05 significance level, can the government agency
conclude that the bottle is being under filled
in the Green River Valley area?
From the company data, we estimate the population
standard deviation is 0.05.
All right.
Now, the first thing is we want to figure out
who's point of view this is.
In this example here of ketchup, a bottle, a product that's
filled, we're going to do it from, in this example,
the consumer point of view.
Next example, we'll do it from the manufacturer point of view,
and you'll see that the way you set the hypothesis test
is slightly different.
All right, so the point of view.
Well, we have a consumer group and a government agency,
and they're concerned about under filling and label
accuracy.
We are considering the population of ketchup bottles
in the Green Valley area.
And our goal is to run a hypothesis test
to provide statistical evidence to support
the claim that the bottles are being under filled.
Or that's the way we're going to set the test to support
whatever our conclusion is.
Now, this example here.
Someone thinks that the bottles are being under filled.
So the way you think of this is the mu from our population
in Green Valley is less than the hypothesized mu of 16 ounces.
Now, 16 ounces comes from the label, right?
So you assume that that's true for the test.
But in order to set this test up, you've got to think.
OK, less than 16 ounces.
Notice the less than symbol would be pointing this way,
so that would make it a one tailed test to the left.
Now, as soon as you do that, for some people,
it's easier just to look at a picture.
There is a picture.
So something it would be tested on the low end.
But what you do to set the official hypothesis in step
one, the null hypothesis, the original claim,
and the alternative hypothesis.
As we saw in last video, you put h sub zero, column mu,
h sub a colon mu.
We have our comparative operators and then our numbers.
Now, our numbers are going to come from the label,
so 16 ounces.
I'm going to come down to step three.
Our hypothesized mean is going to be 16 ounces.
I'm just going to put that right in here.
I'm going to lock it with F4, Control, Enter.
And notice, I highlighted both cells.
Control, Enter puts the formula into both cells.
All right.
I'm going to put ounces.
Notice I'm going to highlight both cells and Control, Enter.
It puts the thing into both cells.
Now, just as in last video we saw,
the trick is you set everything up,
and then the only hard part is figuring out
the comparative operators.
Well, no problem.
We already know this is less than,
so we're going to go to the alternative first--
Space, less than symbol.
Now, I'm going to come here.
Once you get the comparative operator
for the alternative hypothesis, you
flip it and add an equal sign.
Space, greater than or equals sign,
the null hypothesis always gets the equal sign.
All right. and notice that comparative operator is
pointing that way, so that means it'll
be a one tailed test in that direction.
We have alpha, that's the risk of rejecting
our null hypothesis when it's true, 0.05.
Now, we can come down here.
Sigma standard deviation of the population is known,
so we'll put 0.5.
These are both in ounces.
We don't know that, so I'm going to put a N/A. A
couple of videos ahead, we'll definitely
see how to use the t distribution for sampling
distribution because most of the time you don't have sigma.
The test statistic to be used.
Well, we know sigma, so that means it's z.
Sample size, I'm going to calculate my sample over--
I have my data, I have a government agency
in the consumer group or whoever did
this test went out in the Green Valley area
and randomly selected different bottles of ketchup
from different retailers.
So we have the sample data.
Those are numbers, so I'm going to use the count function,
Control, Shift, down arrow.
All right.
Now, I'm going to do x bar, that's average.
We know it calculates the mean, Control, Shift, down.
All right.
So we have 15.958.
Now, just for kicks--
sometimes it's easier to deal with a certain number
of decimals--
I'm going to round this.
Comma two.
So round some number, whatever it is, a number, a formula,
function, we have average.
I'm going to select two because I wanted
to round to the second digit.
Here, you could see over here there's the decimal,
and you count one, two.
So that's the position I want, a round two.
And that is a two.
So I get 15.96.
So our sample shows a little bit of under filling.
Now, our alpha-- and I have that up here--
the type of test, this is a one tailed to the left.
Right now, I've got to calculate our standard error.
We know for the sampling distribution of x bars,
the variation is less than in the population,
so we calculate our standard error.
We know sigma, so we take that divided by the square root
of our sample size.
And there it is, 100.
Now, we can calculate our test statistic.
In our test-- let's look at this picture, though.
Scroll over.
OK, so here's our picture.
We have one tail to the left.
Our alpha determines the hurdle--
and we'll determine that actual critical value
point in just a moment.
Anything to the left, we're going
to reject our null hypothesis, except the alternative,
which yes, it is under filling.
Anything to the right of this line, we fail to reject,
each sub zero.
Now, for the under filling, we have this point, right?
We take a sample.
We need to determine whether the difference
between this particular x bar and our hypothesized
population, mu 16 ounces, is that difference
statistically significant, or is it statistically insignificant?
Everything this direction is statistically insignificant.
Everything this direction is statistically significant.
All right, so that's step three.
Now, let's go ahead and calculate our z.
Remember the z?
This distribution is z.
We have in our zero point, z.
That's where 16 equals 16, so there is no difference.
But z-- our test statistics tells us how many standard
deviations above and below, so let's do it--
equals-- we take our x bar minus our hypothesized mu divided
by our standard error.
And we just calculate.
All right.
So minus 0.8.
So it's somewhere along there, right?
Minus 0.8.
Now, what we need to do is calculate our p value.
We have two methods of determining
whether this number of standard deviations
is statistically significant or not.
P value-- when we calculate our p value,
we will compare it directly to alpha.
Here's our rule, if p value is less than or equal
to alpha, reject the null hypothesis
except the alternative.
Otherwise, fail to reject.
So let's calculate our p value, norm dot s.
Dist goes from z to probability.
Inverse goes from probability to z.
So I'm going to select dist.
It wants a z.
And then, comma one.
Now remember, these normal distributions,
it calculates from negative infinity
all the way up to your z.
So when I enter-- wow, 0.21.
So that's huge compared to our alpha.
Now, let's go ahead, and I'm going to right click
here and show this one.
So this is step four, where we actually
go ahead and calculate.
So here's our alpha.
That's our hurdle.
When you're using the p value, alpha
has a probability that's 0.5, that's our risk.
But when our p value, our calculated p value--
because our z test statistic is minus 0.8,
all that probability says the probability
of getting minus 0.8 or less.
So our probability, our area here is much bigger than alpha.
So we know we're going to fail to reject.
The second way to do this is to calculate our critical value--
that's that point, critical value--
and compare it directly to our test statistic--
equals norm dot s.
Inverse gives us probability from probability to a z.
So I'm going to take this probability of alpha.
Remember, alpha determines the hurdle.
And that's what we want right now.
We want the hurdle.
These two methods always lead to the same conclusion.
So now, we compare, and it's all about is it over the hurdle?
Point a, it's not over the hurdle, right?
So we compare these two, and we come to the same conclusion,
fail to reject.
And then, we always very carefully write
our conclusions.
Because our p value of 0.21 is bigger than our alpha of 0.05,
we fail to reject h sub zero.
Notice we don't say it's true.
Our test isn't going to prove that.
We've controlled for alpha but not for beta.
Because our test statistic of minus 0.8
is bigger than our critical value of minus 1.645,
we fail to reject.
Probably, we want to say more than either one
of those, that's kind of the basics.
The sample evidence suggests that the mean amount
of ketchup in the yummy red ketchup bottles
is not less than 16 ounces.
Another way to say this, at alpha of 0.05,
the sample mean of 15.96 ounces does not
provide statistically significant evidence
to suggest that the bottles are being under filled
in the Green Valley area.
And finally, we'll mention beta risk.
Because in our last video, we actually
had statistical evidence to reject the null hypothesis.
Here, we don't.
So we run the risk of a beta error,
or beta risk type II error.
That is, we do run the risk of failing
to accept the alternative under filling
when h sub zero was false.
In essence, not accepting the alternative when in fact,
it was true.
All right.
That's our second example for hypothesis testing--
sigma known.
And we did a one tail on the left.
When we come back, we'll do sigma known, two tail.
All right, see you next video.