(gentle music)
- Hello, I am Professor Rick Jerz,
and I welcome you to this session titled,
"Sampling Methods Using Excel."
In this session, I'm going to show you
how to use my Excel model to calculate
the likelihood of a population mean
being within the range of values when we have sample data.
I think that you are going to see that my Excel model
makes these calculations pretty quick.
This Excel model will be similar
to our Excel model for population data.
In many courses, when students get to this chapter,
the equations and burden of doing these calculations
are difficult and often students lose sight
of what we are trying to calculate and conclude.
I hope that with my approach of using Excel,
you will not be burdened doing the calculations
and can concentrate on interpreting the results.
I think that my Excel model
can actually make solving these problems fun.
Okay, let's get started.
Let's begin this session by looking at my Excel model
for generating random numbers.
Remember from our lecture session on this topic
that we need to be able to generate random numbers
in order to use any random sampling method.
We are going to learn about two functions
within Microsoft Excel.
One is the RAND function,
and the other is the RANDBETWEEN function.
One interesting note about these random functions in Excel
is that they do update themselves
whenever they see something in the Excel file changing.
You can also force an update by hitting the F9 key.
My Excel model provides several scenarios
based upon the kind of random data
you're trying to generate.
In the first section, I'm going to use the random function
to show you how it behaves.
I generate 10 random numbers using the random function.
Notice that the results are decimal values
between zero and one.
Noticing this allows us to realize
that the random function will always produce a fraction.
If we want whole numbers,
we're going to have to modify this function
and equation in order to produce our desired results.
Another interesting characteristic
about this random function
is that it does require parentheses,
but nothing goes in between the parentheses.
In this next section of my model,
I am generating numbers between zero and some whole number.
In this case, the number 50.
You can change this 50 to be whatever you want.
This is pretty simple.
All you have to do is multiply the random function
by the largest integer that you are interested in producing.
If you are not interested in starting your values from zero,
let's say some other number like 20,
we can modify our equation once again
to produce the results of interest.
I show you my equation which you should notice
is becoming a little more complex.
You should notice that this form of the random function
produces values between any two whole numbers.
Another thing to notice
about my use of this random function so far
is that every value contains a decimal.
If we want whole numbers,
I can modify our equation once again to produce integers.
In this example, I am simulating the toss of a dice,
which we know can only produce values one through six.
My equation to do this
is getting even more complex but it works.
Whenever your Excel equations become complex,
I recommend that you explore all Excel functions
because there might be a different function
that already does what your complex equation is doing.
It turns out that Microsoft Excel
does provide another random function called RANDBETWEEN
that can do exactly the same thing and it is simpler.
I tend to use this function
more often than the RAND function.
Before I leave this part of the model,
let me remind you once again that this random function
will always regenerate its values whenever it senses
something has changed in the Excel file.
The simple act of opening an Excel file
is considered to be change.
So sometimes when you don't do anything
except open my Excel file, when you close it,
Excel will always ask you
whether or not you want to save it.
Now let's look at my model for analyzing sample data,
and trying to calculate probabilities
for the population mean.
You may not have realized this,
but I have already given you my model for sampling.
On my normal distribution worksheet,
if you click on this graphic in gray,
and hit your delete button,
you will have my model for sample data.
You should now notice that I titled
the columns to the left as Population,
and the columns to the right as Sample.
These two models are very similar.
The only difference is that when we're taking a sample,
we know something about the sample size or N,
so we have one more piece of information
that we must provide to this model.
Additionally, when we have one more piece of information,
sometimes we might want to solve for that particular item.
In this case, sample size.
So this model has four columns instead of three.
In the previous session,
I provided this PowerPoint
showing you the three most common questions asked
for normally distributed population data.
I've revised this a little bit to show you
the fourth question that we might ask for sample data.
The fourth question is, what should the sample size be
to get a probability for a range of data?
Let's see if we could use each column in my model
to answer the question in this example,
which was also provided in my earlier lecture session.
The Quality Assurance Department for Cola, Inc.,
maintains records regarding the amount of cola
in its jumbo bottle.
The actual amount of cola in each bottle is critical,
but varies a small amount from one bottle to the next.
Cola, Inc., does not wish to underfill the bottles.
On the other hand, it cannot overfill each bottle.
Records indicate the amount of cola
follows a normal probability distribution.
Th mean amount per bottle is 31.2 ounces,
and the population standard deviation is .4 ounces.
At 8 a.m. today, the quality technician
randomly selected 16 bottles from the filling line.
The mean amount of cola
contained in the bottles is 31.38 ounces.
Is this an unlikely event?
Is it likely the process is putting too much soda
into the bottles?
In other words, is a sampling error of .18 ounces unusual?
Let's first use column D to address this problem.
First I will change the Plot which Condition box to D
so that I get the proper graph showing below.
Then I'll enter the mean
and the standard deviation and sample size.
Then I will interpret this question to be,
what is the probability of getting 31.38 ounces
or more in a sample?
So I enter 31.38 as my lower number and the upper number?
Well I don't know what it should be,
but I can enter something large like 100.
That should be big enough.
The model calculates
that this would only happen 3.59% of the time.
I can verify this number graphically.
This value 3.59 and the area under the curve
matches what the author got.
Well what can we conclude?
We can conclude that since 3.59 is rather small,
that the sampling error of .18 or a fill of 31.38 ounces
is not very likely and we might want to take action
and adjust the process.
Another way to address this problem is by using column E.
First, I will enter the sample size, mean,
and standard deviation into column E,
and then I will enter the error of .18 ounces
as a plus or minus value from the mean.
The model calculates that 92.81% of the data
should be between 31.02 and 31.38 ounces.
If we look at the graph,
we can see this 92.8% region of interest.
Since the normal curve is symmetrical,
and we are using a plus or minus error,
you have to realize that half of this area
is in the lower region of the curve,
and the other half is in the upper region.
So if we were to take 92.81, subtract it from 100%,
we end up with about 7.2% in the white region.
And then half of this is in the upper region,
which is 3.59%.
We could conclude that only 3.59% of the time
would we likely observe a sample mean of 31.38 ounces.
Again meaning that this is not very likely,
and that something could be wrong with the process.
Column F allows us to ask this question
a little bit differently.
Let's say we want to be 90% sure
that we are not overfilling the bottles.
At a probability of 90%,
what would we expect the underfill
and overfill values to be?
I enter the mean, standard deviation and 90% into column F.
The model calculates 31.036 as the lowest value,
and 31.364 as the upper.
Since our actual sample's value was 31.38,
which is larger than 31.364,
we can conclude that this is rather unusual.
You should realize that whether we state that this is usual
or not usual depends upon the tolerance for our probability.
If I wanted to be 95% certain that I'm not overfilling
or underfilling the bottle, the value 31.38 would be
within our limits of 31.004 and 31.396
for our 95% probability region.
As we increase our acceptance probability,
the region under the curve increases.
Let me show you how we might use column G for sample size.
Let's say we want to establish the upper and lower values
being 95% sure for an error of .18 ounces.
What should the sample size be
in order to make this kind of claim?
I enter the required values into column G,
and the model calculates a sample size of 19 is required.
Although the author in the textbook provides an equation
to calculate sample size,
my Excel model makes this calculation easy.
Had we just taken a guess like 50,
we would be creating more work for ourselves
to measure more bottles,
and we would be destroying more of our production.
This larger than needed sample unnecessarily increases cost.
I think you can see how easy these questions become
when you have a good model.
The key in using this model is to always recognize
whether you have population or sample data.
That will direct you to either the left side,
or the right side of this model.
Once you are on the correct side, learn to use the column
that answers the question being asked.
(gentle 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.