Welcome to Excel 2013 statistical analysis,
video number 66.
Hey, if you want to download this workbook
file and follow along or the PDF file, click on the link
below the video.
Hey, last two videos, we talked about hypothesis testing
and confidence intervals for the difference
between two population means when sigma was known.
In this video, we want to talk about building a confidence
in overall for the difference between two population means
when we don't know sigma population
standard deviation from either one of the populations.
Hey, guess what?
Just like in earlier chapters and videos, when we don't
know sigma, what do we use?
We use the t distribution.
Now for a confidence interval, since we're
talking about the difference between two population means,
we're going to go out and get two samples, x
bar 1 minus x bar 2.
That will be some amount representing the difference,
and then we'll add and subtract what's
called the margin of error.
Now in the last couple videos, when
we calculated standard error, standard deviation
of the sampling distribution of x bar minus x bar,
we knew sigma.
Here we don't know sigma.
So what do we use?
S, remember, s is the sample standard deviation.
S squared is the sample variance.
So in the last couple of videos, we took population sigma
and divided by the n and took the square root
after we added them, but here since we don't know sigma,
we use our sample variance.
Now, this will be our estimate for the standard deviation
of the sampling distribution of x bar
minus x bar for the t distribution.
Remember when we're calculating margin of error,
if this is the standard deviation or standard error,
we have to multiply it by t, which represents
number of standard deviations.
Once we get our margin of error, we
just add it and subtract that and boom,
we get our confidence interval for the population difference.
Well, this looks exactly like what
we did in the last couple of videos except we had z,
and then we knew sigma.
So this isn't very difficult to calculate.
However, we're going to have one extra calculation,
and if I go to the next page in our PDFs,
the thing that makes this calculation hard
is degrees of freedom.
Oh, man, that looks totally complicated,
but notice we're going to have to use
the variance and the sample size multiple times.
So the key to this formula is that we're
going to build a little template in Excel
to calculate variance from the first sample divided
by n from the first sample.
And in a separate cell, we'll calculate variance
from the second sample and divide it
by the sample size for the second sample.
We'll have these two numbers in cells,
and notice since we have to use them a lot,
it'll make this calculation much easier.
Now there's three little notes here,
and note two is just what we mentioned.
Excel hint because the variance divided by n is used so often,
we'll use up what's called a helper cell
and calculate this and then refer to it in formulas
with cell reference.
Now two other hints.
When we're making this calculation,
oftentimes we're going to get some integer and some decimals.
We're always going to round down because that'll give us
a more conservative interval.
Now, something about this formula, this formula
is more complicated.
If we were to look down here, this
is a formula we're not going to use.
This formula only works when you can
assume that the population standard
deviations are the same.
This formula works in all cases.
Formula works whether the standard deviations
are equal from the population or not.
Now I want to jump over to Excel and look at an example.
Now we mentioned this exact example in our first video
for this chapter.
We're going to compare the miles per gallon
for imported compact cars and domestic compact cars.
We obviously went out and got a sample.
And we want to calculate the mean for each, the point
estimate of the difference, and then a confidence
interval to estimate the population difference.
So here's our example.
A recent EPA study compared highway fuel economy
of domestic and imported compact cars.
A sample of 20 imported compact cars
revealed a mean of about 36 miles per gallon
with about a standard deviation of 3.4.
For the domestic compact cars, again, we had a sample of 20.
The miles per gallon were about 33.8
and the standard deviation of 2.33.
We're going to assume that the samples are independent.
They're not related in any way, and the distributions
for the samples are normal.
At a 95% confidence level, construct a confidence interval
for the population difference between imported auto
MPG and domestic auto MPG.
Now here's our formulas off to the side,
but let's go ahead and get busy calculating.
Let's first calculate the sample size,
so equals count because we have numbers.
And notice, 1, 2, 1, 2, very important
that we keep the ones and the twos
straight because our calculations will depend on it.
So I'm going to highlight the first one.
And unlike last video when we did a similar example,
notice that this column and this column are next to each other,
and those are relative cell references.
So when I control enter and copy it over,
if I put it into edit mode with F2, you can see,
sure enough, the relative range of cells moved.
Now we can calculate our n minus 1.
Now notice for our degrees of freedom,
sometime we're going to need n and sometimes we're
going to n minus 1.
So we're going to make the calculating of this easy
by calculating each one of the pieces in separate cells.
So here I'm going to go equals n minus 1, control enter,
and I can copy that over because that's
a relative cell reference, F2.
Now our sample mean equals average.
Highlight the range for the first sample,
control enter and copy it over, F2.
So we can see that that moved relatively.
All right, so 36.5 to 33.83 miles per gallon.
So the question is is the difference
between these significant or is it insignificant?
Well, we're going to build a confidence interval that
will help us decide.
Sample standard deviation equals st dev,
and we're going to use the s because, of course,
this is a sample.
Highlight relative cell references, control
enter, and copy it over.
I put this one into edit mode with F2.
You can see, sure enough, it got the correct domestic MPG sample
variance.
Now we square it.
I could use the function to go look at these,
but I'm going to simply go a equals
the standard deviation caret 2.
That will square it and give us our variance.
Control enter and copy it over.
So we have the two variances.
Now here's the Excel part of it that
makes something complicated like this much easier.
So we're just going to calculate that little bit,
the variance for each sample divided
by the n for each sample.
So equals variance divided by the sample size n.
Control enter, those are relative cell references,
so I can copy it over.
If I put it in edit mode with F2,
I can see, sure enough, whoop, it's
got the right variance and sample size.
Now let's go calculate our point estimate, our best guess
for the population difference.
Oh, hey, we're going to take and make sure it's 1 minus 2.
We're going to take the first 1 minus the second one.
That's the MPG for imported compact cars minus the MPG
for domestic compact cars.
And when I hit enter, it looks like there's
a point estimate of 2.69 miles per gallon difference.
And since we took the first one and the second one,
it looks like our point estimate is that the imported is
2.69 miles per gallon higher.
All right, so the standard error.
Here it is.
It's similar to what we did last time, the square root
of the variance divided by n plus the variance of the second
divided by the sample size of the second,
except for here, we're using our sample variance.
So you ready?
And let's notice something here.
We're adding this, so just like we did in last video
when we calculated standard error with population,
we did square root and then the sum of those
because notice that little bit right there is right there
and this little bit is right there.
So let's do it.
Equals square root, that's this part.
Now inside I'm going to say the sum,
and now because these two little bits, same as there,
are right next to each other.
I simply highlight them.
Close parentheses.
We definitely could have used parentheses and pluses
or just done the pluses, but I kind of like the sum
because when I look at it, I know that the square root
of the sum.
Now I'm going to close parentheses and control enter.
The estimate of the standard deviation
of the distribution of x bar 1 minus x bar 2
for the t distribution.
Now, degrees of freedom.
That's this formula.
We can even bring this over here.
Right now I'm going to notice something up in the top.
I'm adding and that thing is right there,
and that little bit is right there.
So if I just sum it and square it, that will be the top.
Then I'm going to do a division, and because I'm
adding down here, I'm actually going
to use the sum function again.
Now notice that little bit is that 0.58.
So I'm going to click on that cell and then square it
and divide by our n minus 1.
And then we simply add it to the second [? pitfall ?]
for the sample, too.
So you ready?
Equals sum.
That's this part right there, so I'm simply
going to add these two numbers.
Close parentheses, caret 2, so right here, it has A2,
so we're going to square it divided by--
and now I'm to use the sum again.
We don't have to.
You could use parentheses down here and use a plus symbol,
but I'm going to use the sum function.
Now that little number 1 is going
to be variance 2 divided by n.
Oh, yeah, that's that square divided by n minus 1.
So we ready?
Boop.
Caret 2 divided by our n minus 1.
Now that's number one.
I could use a plus symbol, but I'm
going to use a comma here because inside the sum
function, you can put as many little things as you want.
I'm going to put my second thing to add,
which is that whole little bit right there, boom.
Caret 2.
That's that so far.
I just need to divide it by n minus 1.
So by having our little helper cells here,
it certainly makes this formula less difficult to construct.
Close parentheses and enter.
So degrees of freedom, remember, degrees of freedom
helps us choose which t distribution to look at.
When we use our t function, we will
use that degrees of freedom.
Now remember in our notes, we said
we want to round down to the 33 to be more conservative.
Well, guess what?
The t functions automatically do that,
so we do not have to round this.
Now we'll get to our t function right down here.
But first we need to talk about our confidence interval, alpha
and alpha 2.
Our confidence even interval was given as 0.95.
Enter, that makes alpha equals 1 100% minus 0.95.
That would give us 5%.
And since we're going to be talking about two tails, upper
and lower, I'm going to calculate the upper tail
probability.
Hey, I'm going to take that divide it by 2.
All right, so now we can calculate rt.
Remember that's our estimate for number of standard deviations
equals t.n.
When we're calculating a number, in our case, a t,
it's the inverse.
The disc calculate the probability.
So I'm going to take t inverse probability.
Well, remember these functions go from negative infinity
all the way up to whatever t we have.
So I don't want to click this one.
I want to say 1 minus that.
That will give us 97.5% or decimal 0.975 decimal there.
That will be perfect.
Comma, and the degrees of freedom,
lucky for us, this t function automatically
will truncate this.
If you were to go look at help, it tells you to do that.
Close parentheses and enter.
So our estimate for number of standard deviations
for a confidence interval is 2.0345.
Now our margin of error.
Remember I move this out of the way here.
We already have our point estimate.
Boom, there it is.
And I need to add and subtract so I have some amount above
and some amount below.
That's our margin of error.
So what do I do?
I take oh, the t times the standard deviation.
Estimate of our standard deviation
times the number standard deviation, so check this out.
Our margin of error equals number of standard deviation.
That's our t times our standard error.
And that's in miles per gallon, 0.928 miles per gallon.
So it's going to be 1.88 margin of error in miles per gallon
that will add and subtract the upper.
So you ready?
The upper equals-- and there's our point estimate
and this is the upper.
So I'm going to add margin of error.
Boom, there's the upper, 4.58 miles per gallon
equals, and there's our point estimate
of the difference minus the margin of error and enter.
So it looks like our best estimate for our confidence
interval is that we believe that the population difference is
between 0.8 miles per hour and about 4.58 miles per hour.
Now remember last video, we mentioned
if 0 is not in our interval, if we were doing hypothesis
testing with confidence intervals,
then we would say there was a significant difference.
Now we can say this a few different ways.
We are 95% sure that the population difference
between imported compact auto MPG and domestic compact auto
MPG is between 4.58 and 0.81 miles per gallon.
We are 95% sure that the population difference
between imported compact MPG and domestic compact MPG--
notice that's exactly what we said up here.
But the end is different, is 2.69
with a margin of error of about 1.89 miles per gallon.
Remember though, we do run a 5% risk
that our population difference is not in our interval.
So in this video, we saw how to do this amazingly complicated
formula for degrees of freedom.
And we made it easy on ourself by making sure
that we set it up 1, 2 and made all of our pre-calculations,
all the little pieces here.
And then put it all together to calculate our confidence
interval as an estimate for our population difference
when we don't know sigma.
All right, we'll see you next video
when we'll do the same example, but with hypothesis testing.
All right, see you next video.