Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,000 --> 00:00:05,002
(upbeat music)
2
00:00:05,002 --> 00:00:06,002
- [Instructor] In the previous movie,
3
00:00:06,002 --> 00:00:08,002
I invited you to analyze data
4
00:00:08,002 --> 00:00:10,002
using the normal, Poisson,
5
00:00:10,002 --> 00:00:12,005
and exponential distributions.
6
00:00:12,005 --> 00:00:14,007
In this movie, I will show you one way
7
00:00:14,007 --> 00:00:16,007
to approach these problems.
8
00:00:16,007 --> 00:00:18,009
I'm in Chapter_4_Challenge.
9
00:00:18,009 --> 00:00:21,005
And you can find that in the Chapter04 folder
10
00:00:21,005 --> 00:00:24,000
of the exercise files collection.
11
00:00:24,000 --> 00:00:25,002
I'm on the normal worksheet
12
00:00:25,002 --> 00:00:29,006
and I want to work with data that is normally distributed.
13
00:00:29,006 --> 00:00:32,008
And in column B, you see that I have a mean
14
00:00:32,008 --> 00:00:34,008
or average of 145
15
00:00:34,008 --> 00:00:39,003
and a standard deviation of 35.
16
00:00:39,003 --> 00:00:42,009
In cell E3, I will calculate the percent of values
17
00:00:42,009 --> 00:00:45,008
that are less than 119.
18
00:00:45,008 --> 00:00:49,002
So in other words, about one standard deviation
19
00:00:49,002 --> 00:00:50,003
below the mean.
20
00:00:50,003 --> 00:00:55,002
So in E3, I'll type equal NORM.DIST
21
00:00:55,002 --> 00:00:58,001
because we're working with the normal distribution.
22
00:00:58,001 --> 00:01:01,002
Our comparison value's 119, then a comma.
23
00:01:01,002 --> 00:01:03,002
The mean is in B3.
24
00:01:03,002 --> 00:01:05,006
Standard deviation is in B4.
25
00:01:05,006 --> 00:01:07,005
You can see those highlighted over on the left,
26
00:01:07,005 --> 00:01:10,005
then a comma, and I do want to work
27
00:01:10,005 --> 00:01:12,007
with the cumulative distribution,
28
00:01:12,007 --> 00:01:17,003
so I'm looking for all the values below 119,
29
00:01:17,003 --> 00:01:21,004
not the point probability of getting 119 randomly.
30
00:01:21,004 --> 00:01:23,007
So I'll do true.
31
00:01:23,007 --> 00:01:25,005
Right parentheses and enter.
32
00:01:25,005 --> 00:01:27,006
And I get 23%.
33
00:01:27,006 --> 00:01:31,001
So a little bit less than 1/4 of all values
34
00:01:31,001 --> 00:01:35,001
in this distribution are less than 119.
35
00:01:35,001 --> 00:01:36,008
Now we can do a similar calculation
36
00:01:36,008 --> 00:01:40,000
for percent of values greater than 185,
37
00:01:40,000 --> 00:01:42,007
except we need to subtract the value from one
38
00:01:42,007 --> 00:01:44,009
because instead of looking to the left of the value,
39
00:01:44,009 --> 00:01:47,004
less than, we're looking to the right.
40
00:01:47,004 --> 00:01:51,005
So for E4, type equal one minus,
41
00:01:51,005 --> 00:01:56,006
and then a very similar calculation to what we had before.
42
00:01:56,006 --> 00:02:01,002
NORM.DIST, 185, comma, mean's in B3.
43
00:02:01,002 --> 00:02:03,002
Standard deviation's in B4.
44
00:02:03,002 --> 00:02:06,005
Again, looking for the cumulative.
45
00:02:06,005 --> 00:02:08,001
Right parentheses, enter.
46
00:02:08,001 --> 00:02:10,000
And we get 13%.
47
00:02:10,000 --> 00:02:11,001
That's good.
48
00:02:11,001 --> 00:02:14,004
So we've accounted for 36% of our values.
49
00:02:14,004 --> 00:02:16,002
Now we can calculate the percent of values
50
00:02:16,002 --> 00:02:21,005
between 119 and 185.
51
00:02:21,005 --> 00:02:23,001
So I'll type an equal sign.
52
00:02:23,001 --> 00:02:24,004
To perform this calculation,
53
00:02:24,004 --> 00:02:27,006
we need to find the number of values
54
00:02:27,006 --> 00:02:29,004
that are less than 185
55
00:02:29,004 --> 00:02:31,001
and subtract the number of values
56
00:02:31,001 --> 00:02:32,004
or percent of values
57
00:02:32,004 --> 00:02:35,000
that are less than 119.
58
00:02:35,000 --> 00:02:38,000
So I will type NORM.DIST
59
00:02:38,000 --> 00:02:39,009
and then we'll use the higher number first.
60
00:02:39,009 --> 00:02:42,000
So that's 185.
61
00:02:42,000 --> 00:02:46,004
B3 for the mean, B4 for the standard deviation, comma,
62
00:02:46,004 --> 00:02:48,000
and then true.
63
00:02:48,000 --> 00:02:53,001
Right parentheses and then minus the same calculation
64
00:02:53,001 --> 00:02:58,008
for 119, so 119, B3, B4, true .
65
00:02:58,008 --> 00:03:00,006
Right parentheses and Enter.
66
00:03:00,006 --> 00:03:03,003
And we get 64%.
67
00:03:03,003 --> 00:03:04,007
And one way you can check your work
68
00:03:04,007 --> 00:03:06,006
if you're doing this type of calculation
69
00:03:06,006 --> 00:03:11,001
is to press Alt + equal,
70
00:03:11,001 --> 00:03:12,007
which creates an AutoSum formula
71
00:03:12,007 --> 00:03:15,004
and Enter, and we get 100%.
72
00:03:15,004 --> 00:03:18,008
So all of the values are accounted for.
73
00:03:18,008 --> 00:03:23,002
We can also use NORM.INV or the inverse norm function
74
00:03:23,002 --> 00:03:26,005
to find the cutoff where a certain percentage
75
00:03:26,005 --> 00:03:29,006
of values are below that cutoff.
76
00:03:29,006 --> 00:03:32,004
In this case, we'll look for 42%.
77
00:03:32,004 --> 00:03:35,002
So in cell E8,
78
00:03:35,002 --> 00:03:40,001
I'll type equal NORM.INV.
79
00:03:40,001 --> 00:03:42,005
The probability is 42%,
80
00:03:42,005 --> 00:03:46,001
then a comma, the mean, B3, standard deviation, B4.
81
00:03:46,001 --> 00:03:47,009
Don't need anything else.
82
00:03:47,009 --> 00:03:49,005
Right parentheses, Enter.
83
00:03:49,005 --> 00:03:52,000
And I get 137.93.
84
00:03:52,000 --> 00:03:52,008
And that makes sense.
85
00:03:52,008 --> 00:03:55,009
It's not that far below the average.
86
00:03:55,009 --> 00:03:57,004
If we want to calculate the cutoff
87
00:03:57,004 --> 00:04:00,002
for 18% of values about a particular value,
88
00:04:00,002 --> 00:04:02,005
in other words, 82% below,
89
00:04:02,005 --> 00:04:10,002
in cell E9 equal, and then NORM.INV
90
00:04:10,002 --> 00:04:13,005
and here we want to subtract the probability from one.
91
00:04:13,005 --> 00:04:16,008
So instead of subtracting this entire formula from one,
92
00:04:16,008 --> 00:04:19,008
we want to subtract the probability from one
93
00:04:19,008 --> 00:04:22,008
so we get numbers to the right of the cutoff.
94
00:04:22,008 --> 00:04:26,000
So one minus 18%,
95
00:04:26,000 --> 00:04:30,002
comma, mean, B3, standard deviation, B4.
96
00:04:30,002 --> 00:04:31,005
Right parentheses, Enter.
97
00:04:31,005 --> 00:04:34,008
And 177.04,
98
00:04:34,008 --> 00:04:38,007
which is almost one standard deviation above the mean
99
00:04:38,007 --> 00:04:40,007
and that does make sense.
100
00:04:40,007 --> 00:04:42,004
Right, that's the normal distribution.
101
00:04:42,004 --> 00:04:45,001
Now let's switch over to the exponential
102
00:04:45,001 --> 00:04:47,007
and Poisson worksheet.
103
00:04:47,007 --> 00:04:50,002
And from here, we have a worksheet
104
00:04:50,002 --> 00:04:52,007
that calculates customer arrivals
105
00:04:52,007 --> 00:04:55,000
and the amount of time the service takes,
106
00:04:55,000 --> 00:04:56,005
the time the service starts
107
00:04:56,005 --> 00:05:00,002
and when the service is complete plus any wait time.
108
00:05:00,002 --> 00:05:01,007
And the key to this worksheet
109
00:05:01,007 --> 00:05:04,002
is to define probability curves
110
00:05:04,002 --> 00:05:07,006
for the Poisson and exponential distributions.
111
00:05:07,006 --> 00:05:11,005
So we have a lambda of eight for Poisson
112
00:05:11,005 --> 00:05:14,003
and then for the exponential distribution,
113
00:05:14,003 --> 00:05:16,008
we have a lambda of four.
114
00:05:16,008 --> 00:05:18,006
And this is a good case for a business
115
00:05:18,006 --> 00:05:22,008
because customers arrive typically about every eight minutes
116
00:05:22,008 --> 00:05:25,002
and service usually only takes four
117
00:05:25,002 --> 00:05:27,002
but of course, there can be complications
118
00:05:27,002 --> 00:05:29,004
and that's why there are other times
119
00:05:29,004 --> 00:05:31,004
beyond four and beyond eight
120
00:05:31,004 --> 00:05:33,009
or you could get lucky and it doesn't take as long
121
00:05:33,009 --> 00:05:37,005
and that's why we have minutes one through seven.
122
00:05:37,005 --> 00:05:39,000
I'll go ahead and fill
123
00:05:39,000 --> 00:05:45,000
in the first probability calculation for Poisson in cell B7,
124
00:05:45,000 --> 00:05:47,003
so I'll type an equal sign.
125
00:05:47,003 --> 00:05:49,005
And my formula
126
00:05:49,005 --> 00:05:55,004
is POISSON.DIST and the x is in cell A7.
127
00:05:55,004 --> 00:05:57,003
I'm going to leave that as a relative reference
128
00:05:57,003 --> 00:05:59,004
so it can change when the formula's copied.
129
00:05:59,004 --> 00:06:00,005
Then a comma.
130
00:06:00,005 --> 00:06:03,000
The mean is in B4.
131
00:06:03,000 --> 00:06:06,000
I do not want that to change so I'll press F4
132
00:06:06,000 --> 00:06:09,004
or Command + T on the Mac.
133
00:06:09,004 --> 00:06:10,007
Then a comma,
134
00:06:10,007 --> 00:06:14,005
and I do want the cumulative distribution function.
135
00:06:14,005 --> 00:06:17,003
So I will press Tab to accept true.
136
00:06:17,003 --> 00:06:18,009
Right parentheses and Enter.
137
00:06:18,009 --> 00:06:20,002
And there's the lookup
138
00:06:20,002 --> 00:06:23,007
and I will double click the fill handle
139
00:06:23,007 --> 00:06:28,000
at the bottom of B7 after I select the cell.
140
00:06:28,000 --> 00:06:29,007
And the formula copies down
141
00:06:29,007 --> 00:06:31,006
and you can see the probabilities
142
00:06:31,006 --> 00:06:34,008
for getting a particular time or less.
143
00:06:34,008 --> 00:06:38,003
And the lookup functions that I use over here
144
00:06:38,003 --> 00:06:43,006
for interval uses B7, B8, B9,
145
00:06:43,006 --> 00:06:45,007
all the way down to B21
146
00:06:45,007 --> 00:06:48,003
to look up a value and find an interval.
147
00:06:48,003 --> 00:06:50,005
So in the first case, the interval is seven,
148
00:06:50,005 --> 00:06:56,004
which means that the value was between .45 about and .31.
149
00:06:56,004 --> 00:06:57,009
Now we can do the same thing
150
00:06:57,009 --> 00:07:00,006
for the exponential distribution.
151
00:07:00,006 --> 00:07:04,005
I'll click in cell E7 and then equal.
152
00:07:04,005 --> 00:07:07,009
And then EXPON.DIST.
153
00:07:07,009 --> 00:07:09,007
So we have our x again.
154
00:07:09,007 --> 00:07:12,005
That is in D7.
155
00:07:12,005 --> 00:07:14,009
Again leaving it relative so it will change,
156
00:07:14,009 --> 00:07:16,001
then a comma.
157
00:07:16,001 --> 00:07:18,004
The lambda is in cell E4
158
00:07:18,004 --> 00:07:20,009
but remember, for the exponential distribution,
159
00:07:20,009 --> 00:07:24,003
it is one divided by lambda.
160
00:07:24,003 --> 00:07:29,007
So one, forward slash and then E4.
161
00:07:29,007 --> 00:07:32,003
I don't want that reference to change, so F4,
162
00:07:32,003 --> 00:07:34,000
again Command + T on the Mac.
163
00:07:34,000 --> 00:07:35,003
Comma.
164
00:07:35,003 --> 00:07:36,008
Then true.
165
00:07:36,008 --> 00:07:38,005
Right parentheses and Enter.
166
00:07:38,005 --> 00:07:39,007
There we go.
167
00:07:39,007 --> 00:07:45,002
And I will click E7, double click its fill handle
168
00:07:45,002 --> 00:07:47,001
and there we have the values.
169
00:07:47,001 --> 00:07:48,009
And you can see over on the right
170
00:07:48,009 --> 00:07:51,006
that our service table has shown
171
00:07:51,006 --> 00:07:55,004
that we only have a total wait time shown here
172
00:07:55,004 --> 00:07:58,006
in cell M22 of two minutes.
173
00:07:58,006 --> 00:07:59,008
That's pretty good.
174
00:07:59,008 --> 00:08:02,006
And if I look at the service times,
175
00:08:02,006 --> 00:08:05,006
I see that the largest one, we have a seven
176
00:08:05,006 --> 00:08:08,003
and the customer after that had to wait
177
00:08:08,003 --> 00:08:11,003
for two minutes but that's the only time.
178
00:08:11,003 --> 00:08:13,003
But you can see here that we got lucky
179
00:08:13,003 --> 00:08:16,007
that we have fairly large intervals in column H
180
00:08:16,007 --> 00:08:18,007
and fairly small service times.
181
00:08:18,007 --> 00:08:20,007
We're not always going to be that lucky.
182
00:08:20,007 --> 00:08:25,000
So I'll press F9 to recalculate the workbook.
183
00:08:25,000 --> 00:08:27,002
This time, we got 20 minutes of wait time
184
00:08:27,002 --> 00:08:30,009
and you can see that we have a couple of cases
185
00:08:30,009 --> 00:08:33,000
with small intervals,
186
00:08:33,000 --> 00:08:36,006
and even though we only had one long service time
187
00:08:36,006 --> 00:08:39,002
at the top, in fact, it's the only one over 10,
188
00:08:39,002 --> 00:08:43,003
you can see how the backup went through the rest of the day.
189
00:08:43,003 --> 00:08:45,001
So it goes to show, and I'm sure
190
00:08:45,001 --> 00:08:48,000
that you have experienced this in your own life,
191
00:08:48,000 --> 00:08:50,006
that a small delay at the start of a day
192
00:08:50,006 --> 00:08:53,006
can lead to longer delays later.
193
00:08:53,006 --> 00:08:54,008
I hope you enjoyed this challenge
194
00:08:54,008 --> 00:08:57,003
and please do look at the workings
195
00:08:57,003 --> 00:09:00,008
and formulas in this part of the worksheet.
196
00:09:00,008 --> 00:09:02,007
It's a good tool that you can use
197
00:09:02,007 --> 00:09:04,009
for a single service point
198
00:09:04,009 --> 00:09:09,000
to find wait time, service time and arrival time.
14534
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.