Welcome to Excel 2013, Statistical Analysis video 49.
Hey, if you want to download this workbook,
Business 210 Chapter 8, click on the link below the video.
In this video, we're going to talk
about creating confidence intervals in Excel when
sigma is known.
When sigma is known, we're allowed
to use the z-distribution, the standard normal distribution.
Here's our formula-- we have x-bar plus or minus--
this is called the margin of error.
This is called the standard error.
And since we're trying to estimate the population mean
using sample statistics, we can't just
say sample statistics is equal to population mean.
We calculate the sample mean, add some margin of error
on either side, and then we give a range of values
as our estimate for our population mean.
All right, an insurance company would
like to estimate the average amount paid
per year for insurance policy.
They have past data that can provide
a good estimate of the population standard deviation.
Create a 95% confidence interval so the company
has a range of values to estimate the unknown population
mean.
Now, actually, in last video, we saw the final result
written down on paper of this.
But here, we'll do all the calculating.
So we went out and got this sample.
First, let's calculate our sample size, our n.
So we use the COUNT.
COUNT is great because it can count numbers.
Control-Shift-Down Arrow and then Shift-Enter.
Now, x-bar-- we're going to use average.
Control-Shift-Down Arrow, Shift-Enter.
So we have our x-bar and our n from sample data.
Sigma is known.
Now the standard error.
Sigma, and divided by the square root of our n.
So there it is, the standard error
or the standard deviation, the distribution of sample means--
11.66.
Our confidence interval is going to be 0.95.
We can get our alpha from that-- equals 1 minus.
And alpha divided by 2.
The z on the upper end is always--
if we're given confidence interval of z-- alpha
divided by 2.
Because 95% needs to be in the middle, right here.
And then we need to chop our risk that the population
parameter's not in our interval a little bit on either side.
So that is the alpha divided by 2.
We need the z up here, alpha divided by 2.
And then we'll use that probability
to help us get our z.
Now, we're going to do three methods.
We saw in our last video PDF, page six
there had a summary of our three methods using Excel.
All right, so method one, we're going to see the NORM.INV.
Now the great thing about NORM.INV
is we can directly calculate the lower limit and upper limit.
It'll calculate the two values--
the upper, which is the plus, and the lower,
which is the minus.
So we're going to use the NORM.INV.
All right, so it wants a probability.
Well, on the low end, all these functions
work from negative infinity up to the x-bar value
or the probability.
So we're just going to put in that probability right there.
We need to tell it the mean--
we've calculated x-bar there-- and our standard deviation,
the standard error.
So our lower value for our confidence interval
would be approximately $814.
Our upper NORM.INV, we give it our probability.
Now, since we need all the probability
from negative infinity up to the last little 0.025,
we do 1 minus, comma, and then we have our mean
and our standard error.
So directly using NORM.INV, we can calculate
the lower and upper limits.
So between these two limits, we're
95% sure that our population mean occurs.
Method number two, we'll do the NORM.S.INV method.
Now, that will calculate the z.
And then, given the fact that we already have standard error,
we'll simply multiply them to get our
what's called margin of error.
All right, so the z equals NORM.S,
and then we'll do the inverse.
This pops out z on the upper end.
And so it's upper end.
So I'll do 1 minus the alpha divided by 2.
That gives this our z.
Now we can calculate our margin of error.
Remember, what does z tell us?
How many standard deviations above or below, right?
We have our standard deviation up here.
So we simply say, hey, z, number of standard deviations,
times the standard deviation.
That gives us the margin of error.
That's this whole amount right there.
Now we simply do the lower.
And we're going to subtract our margin of error.
So there's our x-bar, right?
So we subtract the margin of error.
And then we'll take x-bar plus.
So, again, we have 814 and 16.
Now I'm going to increase the decimals
so these are the same numbers, just with different formatting.
All right, so method one and two-- and for the class,
I may ask you specifically to do one or the other.
If I don't ask, you can do whichever one you want.
Method three-- this is the CONFIDENCE.NORM.
This a brand-new-- actually, all of these functions
are new in 2010.
So CONFIDENCE.NORM, that directly
calculates the margin of error.
So it'll calculate the whole little thing here.
All right, let's see if I can get this on the screen.
Equals CONFIDENCE.NORM.
So it wants alpha, the whole alpha.
Standard deviation-- it wants the sigma.
And then the size, the sample size.
As we said last video, this formula
says sigma divided by the square root of n.
Well, this function just needs the inputs.
It's programmed underneath to calculate the standard error.
So that's why we give it directly standard deviation
from the population and n.
It'll calculate it.
It can also calculate whatever it needs-- alpha divided by 2
or whatnot.
So that'll give us our margin of error.
And then from that, we simply say x-bar minus and x-bar
plus our margin of error.
Again, these are just formatting.
Now, what can we say?
Our 95% confidence interval has limits of 814 and 860.
We are about 95% sure that the population
mean lie within our interval.
We are 5% sure that the population mean will not
lie within our interval.
As we also talked about last video,
technically, if we were to construct
100 similar intervals, we would expect 95 of them
to contain the population mean and 5 to not contain
the population mean.
Still another way to say this--
we estimate the population mean to be about 837
with a margin of error of approximately 23,
plus or minus.
Another way to say this-- a reasonable range of values
for the population mean is 814 to 860.
Still another way to describe what we did--
we are 95% sure that the population
mean for annual insurance cost is between 814 and 860.
I kind of like that last one the best.
Now, we, last video, saw--
actually, we talked about these pictures here.
This is what we did in Chapter 7.
We did x-bars, and we directly compared them
to the sampling distribution of x-bar.
Over here, in Chapter 8, we see that we're creating
confidence intervals, right?
And 95 of them should contain the population parameter,
and 5 of them not.
Well, let's go ahead, and let's say we had an x-bar of 850.
So here, we calculate this interval.
Well, 850 minus our margin of error.
That's the same margin of error we just calculated down there.
And then 850 plus.
So now we say we're 95% sure that the pop mean
for an annual insurance--
population mean for annual insurance cost
is between 827 and 872.
Now, we wouldn't know that this hits the 5% case here.
But if we did--
because we kind of did both of these examples similar.
If we did, that 822, we can see it's not in this interval.
So we wouldn't know this, right, because we're
dealing with sample data.
We don't know the population mean.
But occasionally, you're going to get an interval,
and it's going to be one of those ones that
doesn't have it.
Again, why do we do 95%?
Well, if you're don't like that, maybe jump up to 99%.
But you really can't do 100%, because then you're
covering all the values.
And then the estimation doesn't become of much use.
So in this video, we saw all about the formula
for calculating confidence interval when sigma is known.
We saw NORM.INV, NORM.S.INV, and even the CONFIDENCE.NORM.
The next video, we will talk about what
to do when sigma is not known, and we'll
learn a new distribution called the t-distribution.
See you next video.