Topic:Excel for Confidence Intervals
From SharedExperienceProject
Contents |
Topic Highlights
(What you will learn)
- The basics of how Microsoft Excel is used for confidence interval problems
Introduction and Motivation
(Why learn it)
Not only is Microsoft Excel a very useful tool for statistical analysis, but it will be used extensively in the course Business Statistics I. If you have not already become familiar with it, you will need to do so.
By following the examples in this topic, you will learn how to solve basic confidence interval problems using Excel's built-in functions. Most important among those functions are NORMSINV() and CONFIDENCE().
Learning Activities
(What I need to do to meet the objectives given below)
| Type | Name | Direction |
| In-class activity |
| Self-directed |
| Review and reflection |
| Self-directed |
Exercises
Getting Started
Open a new Excel worksheet. If you can't remember how to do this, then refer to our earlier topic on Introduction to Excel for Statistics.
Now put in an appropriate heading for each of the questions you are asked to answer. An example is shown below, although yours may look a little different from this, depending on what you are asked to do.
| Sample 1 |
|---|
 |
Don't forget to save your Excel worksheet somewhere where it can be accessed next time you need it. You might also e-mail it to yourself for safe keeping.
Exercise A: Finding z-scores in Excel
The ability to use Excel to find a z-score for a given probability is a fairly basic function.
Let's take a look.
Example 1
Let's assume you are given a problem that gives you a desired confidence level, say 99%, and requires you to come up with the z-score values for the corresponding range centered around z=0.
In the topic Confidence Intervals, we learned to draw the following sketch to represent this situation, and to look up the values of z in our tables:
If you do this using the table for the probability 0.495, you will get z = 2.575.
You can do the same thing using Excel's NORMSINV() function, as long as you realize what area the function is expecting you to enter: you are required to enter the area under the curve to the left of the upper z.
For a confidence level of 99%, the area to the left of the upper z is 0.50 + 0.495 = 0.995. This is the area shown below:
So, the function you would enter is:
=NORMSINV(0.995)
Which returns the value z = 2.575829304.
Example 2
What is the Excel formula to find the z-score for a confidence level of 80%?
| Solution |
|---|
|
The area you need in this case is 0.500+0.400 = 0.900 So, the Excel formula is: =NORMSINV(0.900) Which returns the value z = 1.281551566 |
The spreadsheet corresponding to Example 2 might look something like the following:
Exercise B: Finding the Standard Error in Excel
Once you're able to find z, the next step is finding the standard error, E. We'll do this here for cases where we seek to quantify the precision of an estimate of the population mean.
What is the Standard Error?
For this, let's recall the following general form of the confidence interval for the mean of a normal population:
CI = [- z (
),
This can be written in terms of the standard error as follows:
CI = [
which means the standard error is given by the following:
E = z (
Check the topics Confidence Intervals and More on Confidence Intervals if you need a refresher on what the symbols mean.
In Excel, there are two methods of finding the standard error:
- Computing it with the above formula, as in Example 3 and Example 4
- Computing it with the CONFIDENCE() formula, as in Example 5
Example 3 - Computing E directly
The above equation can be computed directly if you know z, 
and n.
For example, if z = 1.96, =12.4 and n = 25, then you can write the following formula:
=1.96*12.4/SQRT(25)
which gives E = 4.8608
Example 4 - Computing E directly
You won't be given z in many cases, and will have to compute it.
For example, you may be told that =12.4 and n = 25, and asked to compute the standard error corresponding to a confidence level of 80%.
In this case you would first need to compute z, as we did in Example 2, using the following formula:
=NORMSINV(0.900)
and then compute E as in Example 3.
Your spreadsheet for doing this might look as follows:
Example 5 - Computing E with the CONFIDENCE formula
Another way to compute the standard error for an estimate of the mean is by using the following Excel formula:
CONFIDENCE(,
, n)
where 
is one minus the confidence level expressed as a decimal, e.g.for a confidence level of 80%:
For the situation in Example 4, for example you would use the formula:
=CONFIDENCE(0.20, 12.4, 25)
which would give a value E = 3.1782
Your spreadsheet for doing this might look as follows:
Exercise C: Computing the Confidence Interval
Once you have the standard error, E, by either method shown above, it's quite simple to compute the upper and lower bounds of the confidence interval for the mean. Just remember the following equation:
CI = [
Example 6
What are the lower and upper bounds of the confidence interval for Example 5 if 
= 34.4?
You compute the bounds as follows:
lower =
upper =
To do this, your spreadsheet might look as follows:
Final note
Note that for some questions like this, you may not be given the sample mean, . Instead you will need to compute it from some raw data points that you are given. You should already know how to do this using the AVERAGE() formula .
