Welcome to Excel 2013, Statistical
Analysis video number 65.
Hey, if you want to download this workbook file or the PDF
file, click on the link below the video.
Hey, in this video, we want to talk
about hypothesis testing about population difference
when sigma is known.
Now, last video, we did confidence intervals
when sigma was known.
This video, we want to see hypothesis testing.
Now, last chapter, chapter nine, we
learned the five steps for a hypothesis testing.
And guess what?
It's exactly the same here.
The only difference is we're going
to be testing the difference between mu sub 1 and mu sub 2.
Now, we'll set up the hypothesize difference
as d sub 0.
And that means we can have, just as we
did last chapter, two tail-- one tail upper or a one tail lower
test.
Now, let's look at this one right
here and see how we set it up slightly differently
than last chapter.
H sub 0 null hypothesis, colon.
And then we set it up as a subtraction,
so mu sub 1 minus mu sub 2 less than
or equal to the hypothesized difference.
That means the alternative would be
H sub a colon, the difference between the two population
means greater than the hypothesized difference.
Now, just as we mentioned last video,
it's very important to get the order of whatever population
and sample you set up as the first one and the second one.
You've got to get it right throughout all
of the calculations because, oftentimes, we'll
be doing some calculations starting
with the first one and the second one.
Now, the reject region and the fail to reject reasons
are exactly the same as last chapter.
Here is reject the null and accept the alternative.
Here it's fail to reject.
Now, down here is going to be our test statistic.
Hey, it's z because we know sigma population
standard deviation.
Now, we're going to take, from our sample,
the difference between x bar sub 1 minus x bar sub 2.
And we'll subtract the hypothesized difference.
And then divide it by our standard error.
That will give us our value to compare
against the critical value.
Or we can calculate from the z, which
is number of standard deviations,
we calculate p value.
Now if we go to the next page in our PDF.
There's all of the variables defined.
And we're going to see something cool here.
We're going to do this with form,
and then we're going to see there's
a totally awesome built-in feature called Z Test Two
Samples for Mean.
Now, I want to jump to page 18.
Now on page-- it says 16 on my PDF,
but it's actually 17 written on the slide.
We want to look at a general note about hypothesis testing.
If we would like to conclude that one mean is bigger
than another, we have to be careful in how we set
our alternative hypothesis up.
So if we want to test whether the first population mean is
greater than the second population mean,
then when we get to our alternative,
because we we're going to set it up 1 minus 2
and then compare it to our 0, notice if we're asking,
is one bigger than 2?
If we did subtraction bigger minus smaller, well,
that would come out positive.
So when we set up our alternative, we want to say,
hey, the difference between these two is greater than 0.
Then we can do this at the end, after we
reject the null hypothesis and accept the alternative.
And in fact, we will have this example in just a moment
over in Excel.
Hey, let's go over to Excel and here's our example.
Remember, we did this example--
two cities, Bradford and Kane last video when
we built a confidence interval.
But here, we're going to do hypothesis testing.
So two cities, Bradford and Kane are separated
by only the Conewangle River.
A random sample of annual incomes from both city
was taken.
Sigma, a population standard deviation
is known from past data.
Bradford, standard deviation for the population is 6,000.
Kane is 7,000.
At an alpha of 5%, can we conclude that Bradford's annual
income is more than Kane's?
Now remember, what do we do?
When the question pops up, that's
going to help us define our alternative hypothesis.
We'll put the comparative operator here.
And then whatever comparative operator
we have here, we'll just do the opposite
for our null hypothesis.
Now I'm going to come down here the hypothesized difference.
We're just going to assume that to be 0.
So I'm going to put that right there.
And remember, just as we did in Chapter 9
with hypothesis testing, we set these two up and both of them
will be compared against some number,
in our case the hypothesized difference.
So I'm going to highlight both cells and in the active cell
say equal.
Click on that 0 F4 to lock it, and Control
Enter to populate those two 0's down there.
Now we could type these in but how do we do Excel?
If this was ever to become 5, we'd
change it in the one original place
and it would flow through to the rest of the spreadsheet,
Control Z. All right.
Can we conclude that Bradford's--
and notice, we have to consider sample one or sample two--
Bradford is sample one.
So I'm thinking, Bradford's annual income more--
so our symbol would go like this, greater than,
pointing towards mean of sample one.
So I'm going to take this and put it down here.
Oh, but wait a second.
Before, remember what we said?
If we're considering a bigger than question,
if we get mu sub 1, that's the first one, our first sample,
that's Bradford.
If we had bigger minus smaller, this would come out positive.
So I want to say is this number right here that I'm thinking
might come out bigger, is it bigger than 0?
So that's what's going to help us determine how
we put our comparative operate.
Now I'm going to put a space and then greater than symbol,
Enter.
If we know the comparative operator from the alternative,
we simply come to the null space, less than
and remember, equal sign always goes with the null hypothesis.
Now step two, alpha, 0.05, Enter.
Step three, sample, calculate, draw picture,
calculate test statistic.
Well, you know we can just sketch out a picture.
I have all the details from the final calculations here.
But as soon as you see this, remember,
it's pointing to the upper end.
So we know it's going to be something like this.
Our normal curve, a line, everything above it
is going to be reject the null hypothesis
and accept the alternative.
Everything below is going to be fail
to reject the null hypothesis.
All right, so that's our general sketch.
Now let's come and calculate.
Well first, Bradford known standard deviation
from the population is going to be 6,000, tab, Kane is 7,000,
Enter.
Now we can calculate our sample mean for sample 1 equals
average.
Now I'm going to click in the top cell and watch this.
Control, Shift, down arrow-- and so many times in this class
I tend to do Shift, Enter, because it puts it in a cell
and jumps up.
But I think I like something else better.
Let's try Control, back space.
Now what's different about Control backspace
is that it jumps back to the active cell
but keeps the whole surrounding spreadsheet in view.
And I just want to compare this, average, Control Shift down.
Now if I did Shift Enter, yes, it's polite.
It puts it into the cell.
But notice then I have to use my mouse to scroll up.
So I think I like average, click Control Shift, Down Arrow,
Control Backspace.
All right.
And now, another little trick.
If you have a simple aggregate function,
you don't have to put the closing parentheses.
When I hit Enter, it puts it in for us.
Equals count-- we need to know the sample size.
Control Shift Down Arrow, Control Backspace,
and then Enter
Now we need to calculate population variance.
Why?
Here's our formula.
We saw the standard error, or standard deviation
of the sampling distribution of differences.
And inside our square root symbol,
we're going to have to take variance of the first divided
by sample size of the first plus variance of the second divided
by sample size of the second.
Now, this formula here seems complicated.
When we get to the T-test, man, it's
going to be incredibly complicated.
And we're going to have to use this little variance divided
by sample size many times.
So we're setting ourselves up for efficient Excel spreadsheet
construction.
We want to put both of these calculations in the cell
so later when we have to calculate
that, it's easy to just go grab these two values.
So population variance.
Well, I'm given standard deviation.
Take that and square it.
Now, if you think about this earlier in the class,
we calculated variance and then did square root
to get to standard deviation, right?
Here we're doing the opposite.
Now variance of population 1 divided by sample size 1,
that's that little bit right there, and Enter.
Now, we can continue our calculations for sample two.
Average, click in the top cell, Control Shift
down arrow, Control backspace, and Enter, equals count.
To get sample size, click in the top cell, Control Shift Down
Arrow, Control Backspace, and Enter.
Now we can calculate population variance.
We were given standard deviation,
so we simply square it and Enter.
Now we can calculate that little bit right there--
equals variance from population to divided by sample size
2, Enter.
Now we've calculated for sample 1 and sample 2.
Now let's come down here and continue Step 3.
We need to calculate standard error-- that's
the standard deviation of the sampling distribution of x
bar 1 minus x bar 2.
Oh, that's this little bottom denominator part.
So ready?
Equals square root-- now, notice, square root, and inside
we're simply adding.
But we have the two amounts right next to each other,
so I'm going to use the Sum function
and add those two parts right there,
close parentheses, close parentheses, and Enter.
So that's our standard error.
Now we can calculate our test statistic.
That's this up here, z number of standard deviations.
The difference-- oh, and that's the point estimate,
and we didn't actually calculate it in a separate cell
like we did for confidence interval.
But no problem, we can calculate it in our numerator
and subtract the hypothesized difference
and then divide by the standard error.
So are you ready?
Equals, open parentheses-- I'm going to take sample
mean 1 minus sample mean 2 minus--
and we actually don't need the parentheses.
We can just do straight subtraction left to right.
So we get our hypothesized difference, close parentheses.
So that little bit right there is the top divided by--
oh, we already calculated our standard error--
and Enter.
That is our test statistics, number of standard deviations
to the right on the upper end.
Now we can come down.
And step four, we can calculate our critical value
and then compare it directly against this.
And then we'll calculate our p-value based on that
and compare it to our alpha.
So are you ready?
Critical value is that, and it will be
determined by the probability.
So if we have 5% on the upper end, this is 95%.
So equals NORM.S. And remember, dist gives us probability,
INVERSE gives us a value.
So we want INVERSE to get our z, critical value.
1 minus 0.05, close parentheses and Enter.
Wow, so 1.6 compared to 1.9.
We visually can see it up here.
There's the hurdle, the critical value, 1.645.
And the calculated test statistic is out here.
So we're out in this region.
We're going to reject the null and accept the alternative.
If we do it with p equals NORM, and it's still NORM.S,
but we want probability, so we use the dist.
That z means our test statistic.
Comma, and now we want cumulative.
Remember, these functions always go from negative infinity
up to some point.
Oh, but wait a second.
That would give us the probability all the way up,
and we want it on the upper end.
So I have to go 1 minus Control Enter.
Now comparing our p-value, that's
the probability of getting 1.98 or greater, right?
From that line up, all of that area is the probability.
That's what we just calculated.
Because it's a 1-tail, we don't have to double it.
We just directly compare it to alpha.
It's smaller, so we reject the null
and accept the alternative.
Now our conclusions-- and notice up here, we've mentioned this.
This came out positive.
So we say, is that amount greater than 0?
This is what we accept.
If we did a little algebra--
mu sub 1 minus mu sub 2 is greater than 0,
well if we add a mu sub 2 to both sides,
we would be left with mu sub 1 is greater than mu sub 2.
So we can conclude this way.
Because the calculated test statistic
is greater than our critical value, we reject the null
and accept the alternative.
It is reasonable to assume that the mean income in Bradford
is more than Kane.
Because the p-value is smaller than the alpha,
we reject the null and accept the alternative
and we say the same thing.
It's reasonable to assume that the mean income in Bradford
is more than Kane.
Another way to say it, the sample evidence
seems to suggest that the mean income in Bradford
is more than in Kane.
But don't forget, we do run a 5% risk
that we have rejected the null even though it was true.
Now we want to look at a second way to make our calculations.
And we're going to go up to the Data ribbon,
over to the Analysis group.
And Data Analysis, we've used this feature many times.
Down here at the bottom we have a bunch
of options for two sample tests.
We want z-test Two Sample for Means and click OK.
Variable range one, I'm definitely
going to click on the label at the top, Control Shift
down arrow.
Tab, and then I want to get the variable range 2, Control
Shift down arrow, tab.
The mean difference, we have to type this in.
The variance for population 1, standard deviation was 6,000,
so that makes it 36 with six 0's, tab.
Population variance known for population 2, 7,000,
so it will be 49 with six 0's.
Tab.
I definitely want to say labels because I included my labels up
here.
There's our alpha output range.
I'm going to click and maybe select cell E41 and click OK.
And just like that, there are the averages.
There's the sample size.
There's our calculated test statistic.
There's the critical value on the upper end.
And there is our p-value, so certainly, a much faster way
to go.
Now in this class, I might ask you to do either one.
In general, if you're doing individual tests many time,
certainly that is the fast way to go.
As we've mentioned before, the advantage to formulas
is oftentimes you may be dumping data here,
multiple data sets, and you want it just
to automatically calculate.
All right, so in this video, we did
hypothesis testing for the difference
between two population means.
When we come back next video, we'll
talk about confidence intervals when we don't know sigma,
and we'll have to use the t-distribution.
All right, see you next video.