All language subtitles for 07 - Solution Model operational data using distributions

(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.