Welcome to Excel 2013 Statistical Analysis
video number 67.
Hey, if you want to download this Excel Workbook
file or the PDF files, click on the link below the video.
Hey, in this video we want to talk about hypothesis testing
about population difference when the standard deviations
for the population are not known.
Now, in last video we did confidence intervals.
This video, we're going to do hypothesis testing.
Now luckily, the same five steps for hypothesis
testing that we used in Chapter 9 can be used here.
Our hypothesized difference, that is,
mu sub 1 minus mu sub 2-- the two-population means--
the difference between those will be D sub 0.
And we will assume that they are 0 in our examples.
Our test statistics-- oh, yeah, we're
going to use t because we don't know the population standard
deviation.
And we'll calculate that by taking the point estimate
for the population difference.
That will be x-bar 1 minus x-bar 2
minus our hypothesized difference of 0.
That will be the numerator.
And we divide it by the standard error or the standard deviation
for the sampling distribution of x-bar 1 minus x-bar 2
for the t-distribution.
Now, just as in last video, we take the square root
of the variance from the first divided
by sample size of the first plus variance of the second divided
by sample size of the second.
And remember, we don't know standard deviation
from the population.
So we have to use s, which is sample standard deviation,
then square it to get sample variance for both samples.
Hey, and here's our degrees of freedom.
We saw last video, yes, this looks big and scary.
But when we break it apart in Excel,
it's pretty straightforward.
Not only that, but here's a big hint over here--
and we'll see it after we do our calculations manually--
that we can use the t-test two-sample assuming
unequal variance feature in our data analysis added.
Now, if you go ahead in the PDFs to page 18, 19, and 20,
there's the written description of our problem
we're going to look at.
But we're going to jump over to Excel.
Now, this is the same example we did last video
with confidence intervals.
But now we're going to do hypothesis testing.
And here it is.
A recent EPA study compared the highway fuel economy
of domestic and imported compact cars.
Samples are independent.
That means we took two samples, and they're not
related in any way.
Distributions for the sample are normal.
We'll assume the miles per gallon
are normally distributed.
At the 5% significance level, can the EPA
conclude that the miles per gallon
is higher on the imported cars?
Now, we're going to take this statement right here.
And remember, we first set up the alternative hypothesis,
then the null hypothesis, then pick our alpha.
So if we're looking at this, can the EPA
conclude that miles per gallon are higher
on the imported cars?
Well, look at this.
We set this up as sample 2.
So if we take the mean of this, we're
assuming that the mean of sample 2
is going to be bigger than the mean of sample 1.
So when we look at our alternative, remember,
oftentimes it's easier to set up the alternative hypothesis
first.
Well, if we're taking mu sub 1 minus mu sub 2,
that means the bigger one is the second one.
Smaller number minus bigger number.
That ought to come out negative, right?
So if our original statement is, can we
conclude the mile per hour is higher
on the imported car, that means this better come out
negative, right?
This is the bigger average.
This is a smaller average.
So smaller minus bigger, we'd get negative.
So check this out.
The hypothesized difference is going to be 0.
I'm going to have that number set up
for both null and alternative.
So I say equals--
click on the 0--
F4, and Control Enter.
Now we're going to set this one up.
Well, if this is coming out negative as compared to 0,
I better type a space and the less than symbol.
So there's our alternative.
Once we know the alternative, that's
what we hope to conclude after our test here.
Then this is the opposite space.
And I simply flip it around.
Greater than and the equals sign always
goes with the null hypothesis.
Now we can come down to step 2 alpha.
Well, it was given at 0.05.
Remember, that's the risk that we reject the null hypothesis
even though it was true.
That would be a type I error.
Now, step 3, let's sample, calculate, draw our picture,
and calculate our test statistic.
Well, we better get busy calculating
sample 1 and sample 2.
So our sample size equals count because we're counting numbers.
Highlight all these, Control Enter, and Copy it over.
F2 to put it in Edit Mode.
And sure enough, the relative cell references moved.
So we have a sample size of 20 for each one.
We're going to need, for our degrees of freedom,
we're definitely going to need n.
We notice there's an n right there, right there,
right there, and right there.
But we're also going to need n minus 1.
So I'm going to calculate those in separate cells.
Equals n minus 1, Control Enter, and I can Copy that over.
If I put it in Edit Mode, we can see the relative cell reference
moved.
Now, the average for each one of those
equals average, highlight, Control Enter, so 33.82 miles
per gallon for the domestic.
And when I copy this over, F2 to check it, sure enough,
the relative cell references.
Looking at the right numbers, looks like we've got
36.52 miles per gallon.
So the averages from our samples are there.
Now we need to calculate our sample standard deviation
equals STDEV.
And it's dot S for sample.
Highlight these.
We actually, we don't ever need to really calculate
our standard deviation.
We could've just calculated our variance.
But standard deviation we've calculated so many times
that we will go ahead and calculate, so 2.3 and 3.43.
Once we have our standard deviation,
we can go ahead and square it to get our sample variance.
That's the number we'll use over and over in our formula.
So I'm going to take standard deviation for the first
and square it, Control Enter, so 5.44.
That's the variance for the first sample, and 11.78.
Remember, we did all of this in the last video for confidence
intervals and got all of the same numbers.
Now, the key to all this-- and setting it up 1, 2 very
carefully-- is this right here.
We're going to have to use variance
from the first divided by sample size of the first,
variance of the second, sample size of the second,
in a few different places.
So I'm calculating it in one cell.
And it will make our later formulas easier.
So are you ready?
Equals-- I take the variance of sample 1
divided by the n for sample 1.
Notice that that calculation right there
is right there and right there, Control Enter.
Those are relative cell references,
so I can copy it over.
If I put in F2, sure enough, we've
got variance of the second divided
by sample size of the second.
If I'm looking over here, that's that little bit
and that little bit.
By setting it up in individual cells,
it will make this formula much easier.
Now, we can come and continue step 3, our standard error.
Now, remember, the test statistic,
whatever our point estimate is for the population difference,
x-bar 1 minus x-bar 2 minus or hypothesized 0.
And then divide by number of standard deviations
our standard error.
So let's go ahead and calculate standard error, no square root.
And see the plus?
Those two things are right next to each other, right there.
Just as we did last video, we're going
to do square root and then some function and add them.
So you ready down here for standard error?
Equals square root and then the sum function.
And because they're right next to each other,
I simply highlight the range, close parentheses,
close parentheses.
And as we mentioned last video, it's nice--
we see square root sum, square root sum.
It's a nice, easy-to-read formula.
--Control Enter.
And that is our standard error.
Now, we can calculate our test statistic.
We take the point estimate.
Notice in the last video, we calculated the point estimate
in a separate cell.
But here we didn't do that.
But no problem.
I'm simply going to equals sign, open parentheses.
Be sure and get the 1 and 2 in the right order.
I'm going to take the smaller one minus the bigger one--
so that ought to come out negative--
minus my hypothesized difference.
Now close parentheses because we definitely
want to force all that subtraction before dividing it
by the standard error.
So that little bit in the numerator
divided by the standard error, and Control Enter-- wow.
So remember, the standard error tells us
how many standard deviations.
So if we're looking at a picture here,
there's our hypothesized difference of 0.
That means minus 0.29 standard deviations below.
We've done a lot with standard deviations in this class.
And we know that about 3 standard deviations
is a far way away from whatever that middle point is.
And the probability of that is pretty small.
So just from our general knowledge in this class so far,
it seems like that's too far out to just assume that this
is sampling error, right?
But let's go ahead and finish this.
Oh, we've got to calculate degrees of freedom.
That's this thing right here.
Just as we did last video.
Notice we're adding these two bits, and then square them.
So I'm going to do sum of those and then square them.
Then I'm going to divide.
Again, I'm going to use sum because I'm
adding these two things.
I'm going to take that little bit, square it,
and then divide by this n minus 1.
So are you ready?
Here it is, degrees of freedom.
The numerator-- the sum of these two bits right here.
All we've done so far is that little thing inside there,
close parentheses, caret 2.
Now we've added the caret 2.
Now we divide it.
And we want to add these two.
So I'm going to use this sum function.
Now, number 1, number 2, just as we did last video,
I have two different calculations.
So I'm going to take--
in number 1, there is variance divided by n caret 2.
So so far, I got that little bit.
Now I'm going to divide it by n minus 1,
making sure I'm all doing it from the first sample, right?
So in number 1, that whole little thing is that.
Click at the end, comma.
Now I'm going to do that little bit.
There it is, variance divided by n caret
2, that's that little bit.
I'm going to divide it now by n minus 1.
So that whole bit right there is that bit.
Now I simply close parentheses, Control Enter.
And there are the degrees of freedom.
Remember, we round down with our degrees of freedom
to be more conservative.
And lucky for us, we don't have to do it in the cell itself
with the Round function because the T functions will do it
automatically.
Now, let's look at this picture here, 0.
We already calculated 2.9.
We actually have to calculate this.
Now, this is the hurdle.
So we calculate critical value.
Now, this is T, and we're trying to get a value.
So it's the T--
not DIST, that's for probability-- but T
dot inverse.
That is for the actual number, our T.
The probability, remember, this is on the low end.
So we take our whole 5%.
These functions always go from negative infinity
up to whatever point we want.
Comma, and there's our degrees of freedom.
It will truncate that, meaning it will take just the 33.
Close parentheses, Control Enter.
There is our hurdle.
Anything past this line--
minus 1.69-- anything past, we reject the null
and accept the alternative.
Anything that way, we'd fail to reject.
Well, we already know we're going to reject it.
It is way past.
Now, we can check this with p-value.
Remember, p-value, if we're looking right here,
it's the probability of getting minus 2.9 et cetera or less.
So it's T. We're using something T, and it's dot DIST.
The distance is what gives us the probability.
Our x, that really means the T. So we're going to give it that.
Comma, our degrees of freedom.
Remember, it will truncate.
--comma and cumulative.
Remember, they always go from negative infinity
up to whatever value we give it.
That's the true.
We've been putting a 1 this entire class for true.
Control Enter, and there it is.
Less than 1%, about 3/10 of a percent.
Very, very small probability that we
could go out and get a sample test statistic like this.
So our conclusion, the statistical evidence strongly
suggests that the miles per gallon for the import
compact car is greater than for the domestic compact cars.
The test statistic was well beyond our critical value.
And the p-value was much smaller than our alpha.
And so we reject the null and accept the alternative.
Now, notice we did all of this with formulas, which is good
when you're learning how all of this works.
We did it manually, right?
Not only that, but formulas are awesome if this data
is changing.
And so if we've changed any of these numbers,
all of these calculations automatically update.
But if you're going to do this over and over,
we've got to look at the awesome automatic feature
for comparing two samples when we don't know sigma.
And remember from our PDF, this degrees of freedom formula
assumes that the standard deviations from the population
are not equal.
So there is an automatic feature.
Notice sample 1, sample 2.
We simply go up to Data, Data Analysis.
And we've seen this many times in this class.
Scroll down.
And at the bottom, we saw a z-test for two-sample
means a couple of videos ago.
But now we're going to do t-test two-sample
assuming unequal variances.
Now, we saw formula in our PDFs that we're not
going to use that assumes the equal variances.
This is the one we're going to use, unequal variance.
And actually, we'll come back in our next video
and talk about paired samples.
All right, so we have the right one.
I click OK.
And the similar dialog box that's popped up many times,
it's going to ask us some questions.
Variable Range 1, I'm definitely highlighting
the label at the top or the field name in our records.
The next one, field name and our values or records.
Hypothesized Difference, notice there's no collapse button.
So we can't link it.
We definitely have Labels because we've
included them there.
That helps in the output because we'll know
which numbers come from where.
There is our Alpha.
We could change it.
Output range, and when I click in here,
this is collapsed because we have to tell it where to go.
I'm going to click D42.
And when I click OK, you are not going to believe it.
It calculated the means, the variance,
the number of observations.
There is the degree of freedoms.
And there's our t-stat on the lower end.
For a one-tail, which is what we did, there's our p-value.
Notice this feature is nice.
It gives you the one-tail and the two-tail down here.
There is our critical value.
We know it's on the low end.
So we would slap a minus sign in front of that.
Wow, that's pretty amazing.
So in this video, we talked about comparing the difference
between two population means when
standard deviation from the population is not known.
We saw this automatic two-sample assuming unequal variance.
And we also saw our formula method.
Next video, we'll talk about matched samples or paired
samples.
All right, we'll see you next video.