Welcome to Excel 2013 Statistical Analysis
video number 41.
Hey, if you want to download this workbook and follow along,
click on the link below the video.
Hey, we're on the sheet Accounting Data,
and we want to answer a number of probability questions
using the NORM.DIST function.
This is the normal standard curve
that we've been talking about over the last few videos,
and here is some data--
different accounting jobs, different cities,
different mean annual salaries, and the standard deviation
for each mean.
And so we want to ask the question, hey,
if we are trying to find a job between 50,000 and 60,000
in Seattle, in USA in general, and in Oakland,
California, what's the probability that we could find
a job in any one of these places between $50,000 and $60,000?
So we're going to start by building a formula down here.
Notice, we can use the NORM.DIST.
We know the mean, the standard deviation,
and the lower and the upper.
Now, here's a chart just for the Seattle data.
And as we talked about earlier, when
you're doing between probability,
we're going to take the area all the way from the low end all
the way up to the biggest x.
And then we're going to subtract all the way down here
to the second x, that area.
Subtract those two areas, and we'll have our answer.
Equals NORM.DIST.
Our x, we've got to take the bigger x first, comma the mean.
There's the mean for Seattle--
relative cell reference, right?
Comma standard deviation for Seattle,
relative cell reference, comma cumulative 1.
Now, if I enter this, this tells me
the probability of getting a job in Seattle $60,000 or less.
It's all the area from the very small all the way up
to that 60K.
F2.
But now I need to subtract NORM.DIST,
and the second x would be the lower
x, comma the mean for Seattle, comma the standard deviation
for Seattle, comma 1, close parentheses.
All of those are relative cell references,
so when I Control-Enter, I get number formatting as facade.
That is pulling that number formatting there.
That's a currency with no decimal showing.
I'm going to go up to Home.
I can see up here in the number group, I select General,
or I can use the keyboard Control-Shift grave accent
or tilde to apply the general number formatting.
That's the probability in Seattle
of getting a job between $50,000 and $60,000.
If I copy this over--
go to the last cell and hit F2 just
to make sure all the cell references are correct.
67% chance in Seattle, 16% in the USA--
remember, that's a pretty broad swath of accounting jobs--
and 56% in Oakland.
Now we want to go over to our next question, the gas data
sheet.
Now here we have some data about Seattle gas prices
and Burien gas prices.
And we want to ask the question, what is the probability
of finding a gallon of gas in Seattle that is between $2.10
and $2.30?
Well, we have a lower and an upper x,
so we simply enter our x values--
$2.10, Enter, and $2.30.
Now, if we're going to use the NORM.DIST functions
and the normal probability distribution
to solve this problem, we have to assume that gas prices are
normally distributed.
In general, they are, so we're going to use that model.
Equals NORM.DIST.
Oh, yeah, we just did this.
We take the upper, comma, and the mean is our Seattle.
Our standard deviation is for Seattle, comma, and cumulative.
That gives me everything $2.30 or less,
so there's a 36% chance that I would find something for $2.30
or less.
F2, minus, and we'll do our NORM.DIST,
our lower x, and then are mean for Seattle,
our standard deviation for Seattle,
and comma 1 for cumulative.
Enter.
So the probability that we could find a price between $2.10
and $2.30 is about 30%.
Our next question-- what is the probability of finding a gallon
of gas in Burien that is more than $2.50?
Well, we could see $2.50, that's going to be bigger than
our mean.
And it's on the upper end.
So what's true?
Our function, if we give it $2.50--
if we just did straight NORM.DIST,
it would calculate everything from the low end up to that
$2.50.
But I want everything on the upper end.
So I equals 1 minus.
And now I can do NORM.DIST everything up to $2.50.
That's the x comma the mean, Burien,
comma the standard deviation for Burien,
comma 1 for cumulative, and close parentheses,
Control-Enter.
So we have about a 28% chance of finding a gas price in period
of $2.50 or more.
That's the probability for $2.50, but what about $2.59?
Instantly, we get an updated probability.
The probability of getting $2.59 or above in Burien,
hey, about 15%.
All right, let's go over to our next example,
ketchup filling machine.
Now, ketchup bottle lists on the label that it has 20 ounces.
Past historical data for the manufacturing facility
shows that the machine fills with a standard deviation
of 0.2 ounces.
What lower and upper value would we
expect for 2.5 standard deviation range?
What probability is associated with those upper and lower
values?
Well, we can certainly calculate the x lower and upper
given a number of standard deviations.
So for the lower, equals, hey, give me
the mean minus the standard deviation times 2.5 and Enter.
So it looks like on the lower end, we'd have 19.5 ounces.
Equals the mean plus standard deviation times 2.5
standard deviations and Enter.
So 20.5 on the upper end.
And that makes sense.
0.2 plus 0.2 plus half a 0.2 is 0.5.
Now, the probability.
We have our upper and lower equals NORM.DIST.
There's our upper, the mean, the standard deviation,
1 for cumulative, close parentheses.
That's the upper, so I subtract NORM
and I need the lower, comma the mean, the standard deviation,
comma 1, close parentheses, Control-Enter.
The probability that a bottle could be between 19.5
and 20.5 ounces is 98.75%.
Now, if we didn't have to calculate
the lower and the upper as part of our question here,
we could have simply--
the probability of getting minus 2.5 standard deviations.
And x has to be greater than or equal to and less than
or equal to positive 2.5 standard deviations.
Well, the probability of that, could have used NORM.S.DIST.
That just needs the z.
So on the upper end is our 2.5, comma 1
for cumulative, minus NORM.S and then minus 2.5,
comma 1, close parentheses.
And when I Enter, I get exactly the same thing.
I'm going to highlight these two cells, right-click Format
Painter, and boop.
All right, so in this video, we answered a number
of probability questions using NORM.DIST and even one using
NORM.S.DIST.
Here, we had our ketchup example.
We did a question for gas data in Seattle and Burien,
and we even did a question that involved accounting salaries
in Seattle, Oakland, and the United States.
All right, next video, we'll talk
about the exponential continuous probability distribution.
All right, see you next video.