(light music)
- Hello, I'm Professor Rick Jerz,
and I welcome you to this session
titled "Confidence Intervals Using Excel."
In this session, I'm going to show you
how to use several of my Excel models
to calculate probabilities
in confidence intervals for sample data.
You will see my Excel T Distribution model
is much easier to use than the T Distribution tables
at the back of most textbooks.
And you will see that my Excel models
are very similar to what we have learned so far.
Let's get started.
For any questions about confidence intervals,
we will always be in my confidence interval column
in either my normal distribution model or t Distribution model.
Let's do some of the problems
we saw in the previous lecture session about confidence intervals.
Remember this problem about the American Management Association?
We had a mean of $45,420,
and a population standard deviation of $2,050.
What is our 95% confidence interval?
For problems from this chapter, you always have to ask if the problem
provides the population standard deviation or the sample standard deviation.
When given the population standard deviation, use the normal distribution model.
When given the sample standard deviation, use our t distribution model.
This problem provides the population standard deviation,
so we use my normal distribution model.
I put this data into my model, and guess what?
I get the same answer that the author gets, plus or minus $251.
As you can see, our Excel model quickly solves this problem.
No z-value calculations and no table lookup.
Let's now try a problem that involves the t distribution model.
Here is our problem with tire tread depth.
We use the t distribution model for this problem because the problem gives
the sample standard deviation, not the population standard deviation.
Here is my model for the t distribution.
You might notice that it looks nearly identical to my model
for the normal distribution, and in terms of functionality,
it essentially is identical to my normal distribution model.
What makes it different is that the Excel functions for this model
relate to the t distribution and not the z, or normal distribution.
The various columns in the upper left-hand side do three kinds of common calculations.
Below the calculations, I show you the area under the curve.
Before Excel 2010, I had to fake this chart
because I didn't have the right kinds of t distribution functions to be precise.
But Excel 2010 gave me some new t
distribution functions, and now this chart can be accurate.
The red area gives you
an excellent visual depiction of your probability of interest.
At this point, this figure only works for the confidence interval column.
Perhaps someday, I'll make it more flexible, as I did
for the normal distribution.
To the right, I duplicate student t distribution table found in textbooks.
I will put this data from the problem into my model and see what we calculate.
To no surprise, we get the same results.
The confidence interval is from .256 to .384.
And we did it without equations
and learning to read the t distribution table at the back of the book,
which is harder to use than the normal distribution table.
Many students will get incorrect answers to problems
because they failed to read this table correctly.
Let's do our sample size problems.
The first problem was this student
in public administration wanting to determine the sample size.
The problem provides the population
standard deviation, so we use the normal distribution model.
I put this data into my Excel model and calculates the sample size
to be 385, matching the textbook's answer.
My Excel model makes this result easy to derive.
In this second example, we want to be within plus or minus $5 of the mean,
with a 99% confidence level, knowing that
the population standard deviation is $20.
My model calculates the sample size to be 107, again matching the textbook answer.
How would we tackle this last example if the standard deviation
was for a sample instead of the population?
This would involve the t distribution, but textbook authors
cannot solve this problem with t distribution tables
because you need the sample size to read the table.
I'm going to use Excel's Goal Seek to solve this problem.
Watch carefully.
In the Confidence Interval column, I enter the standard deviation of $20,
and 99 percent for the probability.
The sample mean does not affect sample size, so I can enter anything.
I enter 1.
We normally use this column by entering the sample size, n, and then let the model
calculate the plus or minus error, but we want just the opposite.
We want this model to calculate sample size
by making the plus or minus error equal to five.
Saying this in Goal Seek terms, we want to seek
plus or minus error by varying sample size.
Begin by entering any reasonable value for n.
I enter 50. For Goal Seek to work, the cell
you are going to vary must not be an equation.
Click the Data ribbon, then the What-If Analysis tool, and pick Goal Seek.
The hardest part of using Goal Seek is knowing how to fill out this dialog box.
We want to set cell E9 to 5 by varying cell E10.
Click OK, and Goal Seek seeks an answer,
which could take a little time depending on the speed of your computer.
Goal Seek finds a solution, click OK.
Well, look at this, we would need a sample size of 110.
100 is not dramatically different than 107.
In fact, some might say just round 107 up to 150.
But depending upon the cost of obtaining samples,
the difference could have a significant cost difference.
This illustrates that we must hedge a little
when we do not have the population standard deviation.
One assumption of using this Goal Seek approach is that sometimes
the standard deviation changes when your sample size changes.
We have assumed that the standard deviation remains at 20.
To be on the safe side, always round up.
And if sampling is inexpensive, always use with larger sample size.
Let me end by doing a more difficult real problem
that combines many Excel model features.
Our college will ask you to take a special quiz
called Assurance of Learning, or AOL, at the end of this course.
This college-designed
quiz attempts to validate learning across all course sections.
It is optional, and I find that not all students choose to do it,
probably because their final course grade validates their learning, which is fine.
The folks who put this AOL together are not statisticians,
so they state their goal as: "We encourage at least a 70% completion rate
along with at least a 70% student average score in each course."
In one course, assume
that 9 out of 44 students take this quiz, and here are their results.
We are asked to develop a 95% confidence interval, apply
the finite-population correction factor, and say something about dispersion.
I begin by entering these values into our descriptive statistics model.
Since no one provided the population
standard deviation, I will use our t distribution model.
In the Confidence Interval column, I will reference
the descriptive statistics model results.
In the sample size cell, I enter equal,
and then point to the mean on the descriptive statistics model.
I do the same for the sample mean, the sample standard deviation,
and enter 95 percent for our desired confidence interval.
Well, I have good news.
Not only is my sample mean better than 70%, it is 81%,
but the 95% confidence interval goes from about 71% up to about 90%.
But the 70% participation is not satisfied.
What do we do?
I would argue that a sample size of 9 is fine.
Who says that 70% of the population needs to participate?
Don't they understand sampling theory?
But one could argue that this confidence interval is too wide, nearly 20%.
If they knew statistics, they might even say that
the relative standard error of 5.22% is too high.
The first thing that I might do is realize
that we do not have an infinite population.
My population size is 44.
I can make the finite-population factor correction
by entering 44 into this cell.
Sure enough, the confidence interval gets smaller,
and the relative standard error is now below 5%.
Most administrators would not know how to make this correction.
If I want the plus or minus error to be, oh, let's say, .5,
I can calculate the sample size using Goal Seek, just as I showed you.
Before using Goal Seek, I manually type 9 into the sample size
cell so that it no longer contains an equation.
Now I can use Goal Seek, which shows that I need a sample size of 18,
given that students' scores have the same score profile.
Well, this could be a valid statistical rationale for saying
we need a sample size of about 18 to 20, or about 40% participation.
Doing so will reduce the relative standard
error to under 3%, which is good,
and it will reduce the overall confidence interval to a more reasonable value.
But administrators don't tend to think this way.
So I am fine arguing that a sample size of 9 for a class
size of 44 is fine, and that my students exceed the learning target.
I hope you are appreciating that using Excel
and my models allows us to tackle difficult problems with ease.
But make sure to practice.
The more problems you do, the better.
(instrumental music)
Thanks for taking time to spend some moments with me.
I hope that you have enjoyed what I have shared with you.
If you ever have any questions or need my help,
please let me know.
(upbeat music)