Afrikaans
Akan
Albanian
Amharic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Ewe
Faroese
Filipino
Finnish
French
Frisian
Ga
Galician
Georgian
German
Greek
Guarani
Gujarati
Haitian Creole
Hausa
Hawaiian
Hebrew
Hindi
Hmong
Hungarian
Icelandic
Igbo
Indonesian
Interlingua
Irish
Italian
Japanese
Javanese
Kannada
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kurdish (Soranรฎ)
Kyrgyz
Laothian
Latin
Latvian
Lingala
Lithuanian
Lozi
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Malayalam
Maltese
Maori
Marathi
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Nigerian Pidgin
Northern Sotho
Norwegian
Norwegian (Nynorsk)
Occitan
Oriya
Oromo
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Punjabi
Quechua
Romanian
Romansh
Runyakitara
Russian
Samoan
Scots Gaelic
Serbian
Serbo-Croatian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Spanish (Latin American)
Sundanese
Swahili
Swedish
Tajik
Tamil
Tatar
Telugu
Thai
Tigrinya
Tonga
Tshiluba
Tumbuka
Turkish
Turkmen
Twi
Uighur
Ukrainian
Urdu
Uzbek
Vietnamese
Welsh
Wolof
Xhosa
Yiddish
Yoruba
Zulu
- [Instructor] The data distribution
that you are most likely to use
during your analysis is the normal distribution.
The normal curve or bell curve
has the shape shown in this chart.
This chart indicates probabilities for a curve
with an average or mean of 100
and a standard deviation of 20.
The mean is usually indicated by the Greek letter mu
and the standard deviation by the Greek letter sigma.
the normal curve has some very useful properties.
The first is that approximately 68% of all values
will occur within plus or minus one standard deviation.
So with our mean of 100, that would mean
that about 68% of the values would fall
within 20 above or below the average.
So 80 to 120.
95% of values will be within two standard deviations,
So 60 to 140,
and 99.7% within three standard deviations
plus or minus, so that's 40 to 160.
To see how to work with these values in Excel,
We'll switch over to our practice workbook.
I've switched over to Excel
and my sample file is 04_01_Normal,
and you can find it in the chapter four folder
of the exercise files collection.
I use the values in columns A and B
to create the graph of the curve
that you see at the bottom right.
But let's ask some numerical questions of our data.
For example, we can calculate the probability
of getting exactly 92.
So we have an average of 100,
standard aviation of 20,
92 is close to the middle,
so let's calculate the probability
of getting exactly that value
if we're generating random numbers.
So I'll click in cell E1
and then type an equal sign.
And the function we use is NORM.DIST
and as you might guess,
that stands for normal distribution.
The value we're working with our X is 92.
So I'll type that in, then a comma.
The mean is in B1, comma,
standard deviation in B2, then a comma.
And we are looking for the probability mass function
which is also called a point probability.
And that means that for the last argument,
I need to select FALSE so I highlight that.
Press tab to accept it,
type a right parenthesis and enter.
And we see the probability of getting exactly 92 is 1.84%.
And that might seem pretty low, but remember,
within three standard deviations,
we go from 40 to 160.
So the fact that 92 is as probable
as it is at a random selection
is an indication of how close to the average it is.
Now let's calculate the probability
of getting 92 or more.
And I will do it incorrectly the first time
and then show you how to fix what is a very common mistake.
So in E2, I'll type equal, NORM.DIST.
As before our X is 92,
the mean is in B1, standard deviation in B2
and then a comma, but now we do want to look
for the accumulative distribution function.
And that's because we're looking for 92 or more.
So we want a spread of values instead
of a single point probability.
So I highlight TRUE,
type a right parenthesis,
and again, this is going to be an incorrect result.
I get 34.46 of getting 92 or more.
And here's why that's wrong.
92 is to the left of the mean.
And if you look at the normal curve,
half the values are greater than the mean
and the other half are less than the mean.
So the fact that our calculation shows only 34.46%
of values are greater than 92,
which is less than the mean, must be incorrect.
The way to fix this error
is to subtract that calculation from one.
So I will
double click in cell E2,
and then I will add one minus
our previous calculation.
Now, when I press tab, I get 65.54%
and that makes a lot more sense
because 92 is approximately here,
I've highlighted 90,
and I'll just leave the mouse pointer there
to show you the approximate point.
You can see that about 65.54% of the values
are to the right
so our calculation makes intuitive sense.
We can also ask about percentages of values
within a distribution.
So let's say, what is the value inside this curve
or as part of this data distribution
that 33% of values are below?
So I will click in cell H1
and type an equal sign.
We can't use NORM.DIST for this calculation
but we can use a different function, NORM.INV
and this is the inverse of the normal distribution
where we got probabilities with NORM.DIST,
the inverse
of that gives us values based on a probability.
So our probability is 33%
then a comma,
our mean is still in B1,
standard deviation is still in B2.
We don't need any other arguments
so I'll type a right parenthesis and enter.
And we get 91.2.
And this again makes sense.
About 33% of our values are below 91,
which again is here on the curve, approximately,
and that will show that about 33% of the values
are to the left
so our value checks out.
If I want to find the value for which 90% of values
in this curve are above,
Then I can do NORM.INV.
And if you're suspecting that we need to do
one minus something, as we did with probability
of 92 or more, you are correct
but we put it in a different place.
So in H2 I'll type an equal sign,
NORM.INV.
The result we would get by typing in 90%
would be to return a value that 90% of values are below.
So we need to subtract the percentage from one
as part of the probability calculation.
So for the first argument, I'll type 1 minus 90%,
then a comma, B1 for the mean,
B2 for the standard deviation,
right parenthesis and enter,
and we get 74.37 approximately.
And again, that makes sense.
If I go down to 74,
that's 72,
oh, there's 74.
We can see where it lies on the curve
and it makes sense that about 90% of values,
including the fat part of the curve in the middle
would be above the return value of about 74.37.
So as you can see, you can do a lot with the normal curve,
especially with the functions NORM.DIST and NORM.INV.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.