Welcome to Excel 2013 Statistical Analysis
video number 59.
Hey, if you want to download this workbook,
Business 210 Chapter 9 second file, click on the link
below the video.
Hey, we're talking about hypothesis
testing for the t-distribution.
This is our third example, and we're
going to do a two-tail test.
Here's our situation.
A report stated that the national mean price
for used cars is $9,500.
You want to determine if the Seattle
area mean price of used cars is different
from the national mean.
At an alpha of 0.01, is the mean price for Seattle
used cars different from the national average?
All right, as always, we want to talk
about our point of view, what we're considering in our goal.
The point of view--
Seattle area researcher who wants to compare national mean
used car prices to Seattle's.
We are considering the population
of Seattle used car prices.
And our goal is to run a hypothesis test
to provide statistical evidence to determine whether Seattle
used auto prices are different from the national mean.
Now, we're determining different from,
so that means if it's below a significant amount
or above a significant amount, then
we want to reject the null hypothesis.
So that means we're really interested in starting
with the null hypothesis.
Now, a lot of the examples we've done--
I think this is the sixth example--
we've always started with the alternative,
meaning that we start with the comparative operand.
But here, this is different from,
so we just say, hey, well, if it's different from,
then the null is going to be equal.
So the mu used auto price in Seattle will be equal
to, and we'll have our hypothesized mean--
and I'll do that down here--
of 9,500.
And that means that this is equal.
This is going to be not equal.
I'm going to put space, and then I'm
going to use the Excel symbol less than, greater than.
That means not.
All right, so there's our null and alternative.
We're going to do alpha.
We're going to be really sure.
Remember, the lower the alpha, the less chance we have
of making a type one error, which
is rejecting the null hypothesis even though it's true.
All right, let's go ahead and make some calculations.
We don't have sigma, so we're going
to use our t-distribution.
We have to calculate sample standard deviation,
STDEV.S. Shift-Enter.
Looks like we have a sample standard
deviation of almost $2,406.
Our sample size, we'll use COUNT,
because we're counting numbers.
Control-Shift-Down Arrow, Shift-Enter.
75 degrees of freedom.
That will help us determine which t-distribution
we're going to use.
So we take n minus the number of samples.
Our mean-- Control-Shift-Down Arrow, Shift-Enter.
Alpha, we have that from up here, 0.01.
And be real sure.
Type of test-- this is going to be a two tail.
Our standard error, we take our s divided by square root.
How about S-Q, Tab.
Square root of our n.
That's the standard deviation for the sampling distribution
of x-bar.
So when we calculate, in the numerator, our sampling error,
we divide it by that standard deviation.
And that gives us our test statistic,
our t-test statistic, which tells us
how many standard deviations above or below a hypothesized
mean we are.
So we're 1.52.
We're above, right?
Now, here's our picture.
Right-click, Show/Hide.
We haven't calculated our critical values,
but there's our alpha.
And it's very small.
0.01 divided by 2 is 0.005.
So anything between these two markers,
we fail to reject the null hypothesis.
Anything above or below, we reject the null
and accept the alternative hypothesis.
All right, let's come down here.
Let's scroll up just a little bit.
And we want to calculate our p.
Now, remember, for p, we have a 1.52.
So if we say p-value, it's going to say wherever
it is, that value or more.
Because it's a two tail, we have to double it.
All right, so are you ready?
Equals-- and we're going to have to do 1 minus t dot,
and we'll use the DIST.
Our test statistic is that.
Remember, we have to do 1 minus, because if we put this in,
it'll calculate from negative infinity
all the way up to there.
And we were interested in the upper part.
Comma our degrees of freedom, and it's cumulative.
Now, that gives us just the upper amount--
0.066.
We have to double that, and we have
to do the subtraction first, so we're
going to have to put this in parentheses.
Now I kind of like this way, because we
know we have to double it, but this 1
minus the DIST, whether we're using t or the NORM.S, this 1
minus is how we've been doing it.
So it's consistent.
However, there is a new function in 2000--
I mean, this T.DIST is new because there's a dot.
But this is brand new.
This didn't ever exist before 2010.
It is specifically for the T-DIST.
There's a two tail.
So this two tail, it's going to require simply
the test statistic and the degrees of freedom.
Now, there is a possibility, when you're running a two tail,
that this could be negative or positive.
And this function-- and in my notes and over here,
this t-statistic has to be on the upper end.
So if you are running an experiment
or you have a template, this could be negative or positive.
So you could put the ABS function around it.
ABS is just absolute value, which means distance from zero.
Comma-- and we're not going to run into a situation
here, because this is on the upper end.
But it just as well could have shown up on the lower end.
All right, and then we have our degrees of freedom.
So one advantage of T.DIST.2T is that you just
have to throw in the t from the upper
and the degrees of freedom.
All right, and you get the same thing.
You don't have to double it.
You don't have to remember to multiply it by 2.
Either way you do it, we can clearly
see the p-value is gigantic compared to the 0.01.
So we will fail to reject the null hypothesis, right?
So we're in this region right here.
We can use the critical value.
We want to determine this value and this value.
Equals t dot, and we want the inverse.
We want the probability.
Now, we're given 0.01, and I need half of that.
So I'm going to say the alpha divided by 2.
Now, this gives me from negative infinity
up to that point, which is great,
because this will give us the lower critical value.
I forgot the degrees of freedom.
Degrees of freedom tells the t function
which of the many t-distributions
to go look at to calculate the test statistic.
Minus 2.64.
That's this hurdle here, and the hurdle on the upper end
is 2.64.
So our test statistic is not past that.
Now, to list the one on the upper end--
the curves are symmetric--
I'm going to say minus that.
Now, notice I didn't type in equal sign.
But Excel will know to put that equal sign in.
Now, this is fine.
This is consistent with the way we've been doing it
so far in this class.
There is a T.INV.2T, so equals--
brand new in 2010--
T.INV, and there's a two tail.
This one, the cool thing about this
is you don't have to divide by 2,
because the function explicitly is for two tail.
So the probability means please give me alpha
and then the degrees of freedom.
And this is going to calculate the positive.
So that's why I put it in the upper critical value.
And then to get the one on the low end,
since it's symmetrical, I say minus that.
Exactly the same numbers.
Let's look at our conclusion.
Because our p-value is bigger than our alpha,
we fail to reject-- clearly, that is gigantic compared
to that-- our critical value.
Because our test statistic is between our critical values.
There's our test statistic, and it's between these.
We fail to reject.
The sample evidence does not suggest
that the mean price for used cars in Seattle area
is different from the national average.
Said a different way--
at a point 0.05--
oops, that 0.01.
We wanted to be super sure.
Significance level, our sample mean of 9,923
does not provide statistical evidence
to show that the mean Seattle used
price is different from the national mean.
And finally, we do run this risk of a type two error--
not accepting the alternative hypothesis
the mean is different from when, in fact, it is different from.
All right, so those were, the last three videos,
three examples of the t-distribution
and the t functions.
In our next video--
we have two more videos for this chapter.
I'll see you next video.