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
(upbeat music)
- [Instructor] In the previous movie,
I invited you to analyze data
using the normal, Poisson,
and exponential distributions.
In this movie, I will show you one way
to approach these problems.
I'm in Chapter_4_Challenge.
And you can find that in the Chapter04 folder
of the exercise files collection.
I'm on the normal worksheet
and I want to work with data that is normally distributed.
And in column B, you see that I have a mean
or average of 145
and a standard deviation of 35.
In cell E3, I will calculate the percent of values
that are less than 119.
So in other words, about one standard deviation
below the mean.
So in E3, I'll type equal NORM.DIST
because we're working with the normal distribution.
Our comparison value's 119, then a comma.
The mean is in B3.
Standard deviation is in B4.
You can see those highlighted over on the left,
then a comma, and I do want to work
with the cumulative distribution,
so I'm looking for all the values below 119,
not the point probability of getting 119 randomly.
So I'll do true.
Right parentheses and enter.
And I get 23%.
So a little bit less than 1/4 of all values
in this distribution are less than 119.
Now we can do a similar calculation
for percent of values greater than 185,
except we need to subtract the value from one
because instead of looking to the left of the value,
less than, we're looking to the right.
So for E4, type equal one minus,
and then a very similar calculation to what we had before.
NORM.DIST, 185, comma, mean's in B3.
Standard deviation's in B4.
Again, looking for the cumulative.
Right parentheses, enter.
And we get 13%.
That's good.
So we've accounted for 36% of our values.
Now we can calculate the percent of values
between 119 and 185.
So I'll type an equal sign.
To perform this calculation,
we need to find the number of values
that are less than 185
and subtract the number of values
or percent of values
that are less than 119.
So I will type NORM.DIST
and then we'll use the higher number first.
So that's 185.
B3 for the mean, B4 for the standard deviation, comma,
and then true.
Right parentheses and then minus the same calculation
for 119, so 119, B3, B4, true .
Right parentheses and Enter.
And we get 64%.
And one way you can check your work
if you're doing this type of calculation
is to press Alt + equal,
which creates an AutoSum formula
and Enter, and we get 100%.
So all of the values are accounted for.
We can also use NORM.INV or the inverse norm function
to find the cutoff where a certain percentage
of values are below that cutoff.
In this case, we'll look for 42%.
So in cell E8,
I'll type equal NORM.INV.
The probability is 42%,
then a comma, the mean, B3, standard deviation, B4.
Don't need anything else.
Right parentheses, Enter.
And I get 137.93.
And that makes sense.
It's not that far below the average.
If we want to calculate the cutoff
for 18% of values about a particular value,
in other words, 82% below,
in cell E9 equal, and then NORM.INV
and here we want to subtract the probability from one.
So instead of subtracting this entire formula from one,
we want to subtract the probability from one
so we get numbers to the right of the cutoff.
So one minus 18%,
comma, mean, B3, standard deviation, B4.
Right parentheses, Enter.
And 177.04,
which is almost one standard deviation above the mean
and that does make sense.
Right, that's the normal distribution.
Now let's switch over to the exponential
and Poisson worksheet.
And from here, we have a worksheet
that calculates customer arrivals
and the amount of time the service takes,
the time the service starts
and when the service is complete plus any wait time.
And the key to this worksheet
is to define probability curves
for the Poisson and exponential distributions.
So we have a lambda of eight for Poisson
and then for the exponential distribution,
we have a lambda of four.
And this is a good case for a business
because customers arrive typically about every eight minutes
and service usually only takes four
but of course, there can be complications
and that's why there are other times
beyond four and beyond eight
or you could get lucky and it doesn't take as long
and that's why we have minutes one through seven.
I'll go ahead and fill
in the first probability calculation for Poisson in cell B7,
so I'll type an equal sign.
And my formula
is POISSON.DIST and the x is in cell A7.
I'm going to leave that as a relative reference
so it can change when the formula's copied.
Then a comma.
The mean is in B4.
I do not want that to change so I'll press F4
or Command + T on the Mac.
Then a comma,
and I do want the cumulative distribution function.
So I will press Tab to accept true.
Right parentheses and Enter.
And there's the lookup
and I will double click the fill handle
at the bottom of B7 after I select the cell.
And the formula copies down
and you can see the probabilities
for getting a particular time or less.
And the lookup functions that I use over here
for interval uses B7, B8, B9,
all the way down to B21
to look up a value and find an interval.
So in the first case, the interval is seven,
which means that the value was between .45 about and .31.
Now we can do the same thing
for the exponential distribution.
I'll click in cell E7 and then equal.
And then EXPON.DIST.
So we have our x again.
That is in D7.
Again leaving it relative so it will change,
then a comma.
The lambda is in cell E4
but remember, for the exponential distribution,
it is one divided by lambda.
So one, forward slash and then E4.
I don't want that reference to change, so F4,
again Command + T on the Mac.
Comma.
Then true.
Right parentheses and Enter.
There we go.
And I will click E7, double click its fill handle
and there we have the values.
And you can see over on the right
that our service table has shown
that we only have a total wait time shown here
in cell M22 of two minutes.
That's pretty good.
And if I look at the service times,
I see that the largest one, we have a seven
and the customer after that had to wait
for two minutes but that's the only time.
But you can see here that we got lucky
that we have fairly large intervals in column H
and fairly small service times.
We're not always going to be that lucky.
So I'll press F9 to recalculate the workbook.
This time, we got 20 minutes of wait time
and you can see that we have a couple of cases
with small intervals,
and even though we only had one long service time
at the top, in fact, it's the only one over 10,
you can see how the backup went through the rest of the day.
So it goes to show, and I'm sure
that you have experienced this in your own life,
that a small delay at the start of a day
can lead to longer delays later.
I hope you enjoyed this challenge
and please do look at the workings
and formulas in this part of the worksheet.
It's a good tool that you can use
for a single service point
to find wait time, service time and arrival time.
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.