1
00:00:00,000 --> 00:00:00,566


2
00:00:00,566 --> 00:00:06,732
Hello and welcome to a video on probabilities in Excel. Today we'll be talking about a very specific

3
00:00:06,733 --> 00:00:12,766
type of problem-using probabilities where we're finding the area under the curve in a normal distribution.

4
00:00:12,766 --> 00:00:12,799


5
00:00:12,800 --> 00:00:18,800
So the problem sounds something like this. In a normally distributed distribution with a mean of 80 and

6
00:00:18,800 --> 00:00:18,833


7
00:00:18,833 --> 00:00:25,133
a standard deviation of 12, what is the probability that a randomly selected score will fall below 92?

8
00:00:25,133 --> 00:00:25,333


9
00:00:25,333 --> 00:00:31,399
So here we have a bit of a visual aid and this one's it's not a perfect aid. I got it from another website and

10
00:00:31,400 --> 00:00:31,433


11
00:00:31,433 --> 00:00:37,399
I don't really like the way it's set up, but we'll deal with it. Our mean here is 80. Our standard

12
00:00:37,400 --> 00:00:38,200


13
00:00:38,200 --> 00:00:44,100
deviation is 12 but that's not reflected in the tick marks down here so don't use these as a guideline.

14
00:00:44,100 --> 00:00:44,733


15
00:00:44,733 --> 00:00:50,766
And our x-value is 92 right here on this dotted red line. And what we want to know is

16
00:00:50,766 --> 00:00:57,099
if we randomly select an x-value from this distribution, what are the probability?

17
00:00:57,100 --> 00:00:57,133


18
00:00:57,133 --> 00:01:03,866
What is the probability that it will be in this blue shaded section? So to find the probability

19
00:01:03,866 --> 00:01:08,932
below something, we use equals norm dot dist d-is-t,

20
00:01:08,933 --> 00:01:10,566


21
00:01:10,566 --> 00:01:15,532
open parentheses. And you can see here x-l is prompting us through

22
00:01:15,533 --> 00:01:16,733


23
00:01:16,733 --> 00:01:22,666
some values for our arguments in these parentheses. So the x-value

24
00:01:22,666 --> 00:01:23,432


25
00:01:23,433 --> 00:01:29,533
is 92, because we know that's our randomly selected, or where we would like to find below our randomly

26
00:01:29,533 --> 00:01:29,566


27
00:01:29,566 --> 00:01:35,932
selected score. That's our x-value. Our mean is 80. Our standard deviation

28
00:01:35,933 --> 00:01:35,966


29
00:01:35,966 --> 00:01:41,566
is 12, and cumulative percent is always true. Now I'll hit enter,

30
00:01:41,566 --> 00:01:41,999


31
00:01:42,000 --> 00:01:48,466
and we get 0.841345. So we have an 84% chance, roughly,

32
00:01:48,466 --> 00:01:48,499


33
00:01:48,500 --> 00:01:54,600
of landing below 92 in this distribution. And we can see that that makes sense, because most

34
00:01:54,600 --> 00:02:00,633
of our distribution is to the left of 92. So what if we wanted to find the probability

35
00:02:00,633 --> 00:02:00,666


36
00:02:00,666 --> 00:02:06,966
of landing in the white section of this distribution? Well to do that, we use equals

37
00:02:06,966 --> 00:02:07,166


38
00:02:07,166 --> 00:02:13,366
1 minus norm dot dist, where our x value is still

39
00:02:13,366 --> 00:02:19,766
92, the mean is still 80, and we have a standard deviation

40
00:02:19,766 --> 00:02:19,799


41
00:02:19,800 --> 00:02:25,933
of 12. And we see the probability of above

42
00:02:25,933 --> 00:02:26,099


43
00:02:26,100 --> 00:02:32,266
is 0.158655. Now we did 1 minus norm dot dist, because we know

44
00:02:32,266 --> 00:02:35,499
that this whole distribution equals 1,

45
00:02:35,500 --> 00:02:39,733


46
00:02:39,733 --> 00:02:43,699
and we can test this by adding the two of these together,

47
00:02:43,700 --> 00:02:47,866


48
00:02:47,866 --> 00:02:53,432
and they come out to 1. So we know

49
00:02:53,433 --> 00:02:58,666


50
00:02:58,666 --> 00:03:04,866
that these are the totality of this distribution. Now, remember with probability, you can't

51
00:03:04,866 --> 00:03:04,899


52
00:03:04,900 --> 00:03:11,233
have a number greater than 1, and you also can't have a number below zero. So if you're ending up with negative results

53
00:03:11,233 --> 00:03:16,933
on these problems, you have done something in correct or backwards, and if you are getting a

54
00:03:16,933 --> 00:03:17,966


55
00:03:17,966 --> 00:03:24,032
total or in value greater than 1, then you have also done something in correct or backwards. I've

56
00:03:24,033 --> 00:03:24,066


57
00:03:24,066 --> 00:03:30,466
never seen someone get a value greater than 1, unless they were trying to add probabilities

58
00:03:30,466 --> 00:03:30,832


59
00:03:30,833 --> 00:03:37,133
together somehow, in a way that didn't make sense. So, if you're getting something

60
00:03:37,133 --> 00:03:43,333
like that on your homework and you're not really sure what's happening, reach out to either your instructor or if we have

61
00:03:43,333 --> 00:03:49,499
tutors that semester, reach out to a tutor, and see what's going on. Okay, let's move on to the next

62
00:03:49,500 --> 00:03:55,733
kind of question. What is the probability in a normally distributed distribution of selecting

63
00:03:55,733 --> 00:03:55,766


64
00:03:55,766 --> 00:04:01,866
an x value between 54 and 78 with a mean of 70 and a standard deviation of 8? Okay,

65
00:04:01,866 --> 00:04:02,966


66
00:04:02,966 --> 00:04:09,199
once again, we've got a little visual aid here, our mean is 70, and we would like to find values between 54

67
00:04:09,200 --> 00:04:09,833


68
00:04:09,833 --> 00:04:16,066
and 78. So to find a probability between 2 values, we're actually going

69
00:04:16,066 --> 00:04:16,099


70
00:04:16,100 --> 00:04:22,100
to subtract 1 value from the other. We're going to take the smaller value and subtract

71
00:04:22,100 --> 00:04:28,166
it from the larger one. Now the reason this works is because we want to subtract this white space

72
00:04:28,166 --> 00:04:34,299
here from this space here. So if I were to find the probability below

73
00:04:34,300 --> 00:04:39,833
78, it's going to return all of this, including this little white bit and this blue bit.

74
00:04:39,833 --> 00:04:40,733


75
00:04:40,733 --> 00:04:47,066
And if I were to return below 54, it would just return this white bit. So we want to subtract below

76
00:04:47,066 --> 00:04:47,466


77
00:04:47,466 --> 00:04:52,532
54 from that. So let's go ahead and see what that looks like. So we're going

78
00:04:52,533 --> 00:04:53,699


79
00:04:53,700 --> 00:04:59,700
to do the same norm.dist. We're taking our larger number first.

80
00:04:59,700 --> 00:04:59,733


81
00:04:59,733 --> 00:05:05,733
So 78, our mean is 70. Our standard deviation is 8. And cumulative percent is

82
00:05:05,733 --> 00:05:11,666
always true. From that we are subtracting norm.dist 54,

83
00:05:11,666 --> 00:05:14,666


84
00:05:14,666 --> 00:05:18,232
70, 8, true.

85
00:05:18,233 --> 00:05:23,366


86
00:05:23,366 --> 00:05:29,299
Okay, and we've got our probability of landing between those two scores is 0.818595.

87
00:05:29,300 --> 00:05:29,733


88
00:05:29,733 --> 00:05:35,999
So that's about 80 percent, or 82 percent, of our distribution is landing

89
00:05:36,000 --> 00:05:42,000
between these two scores. And that makes sense when we just visually look at it. So what if we

90
00:05:42,000 --> 00:05:47,800
wanted to find the probability below 54? What we do at the same way we did before,

91
00:05:47,800 --> 00:05:49,100


92
00:05:49,100 --> 00:05:53,533
by using

93
00:05:53,533 --> 00:05:56,666


94
00:05:56,666 --> 00:06:03,032
our norm.dist, our x-value, our mean, our standard deviation,

95
00:06:03,033 --> 00:06:03,399


96
00:06:03,400 --> 00:06:08,500
setting cumulative to true.

97
00:06:08,500 --> 00:06:10,100


98
00:06:10,100 --> 00:06:16,300
And our probability of above, and we can see this is a very small number, which makes sense. It's a very small part of our distribution.

99
00:06:16,300 --> 00:06:16,866


100
00:06:16,866 --> 00:06:23,466
And the probability of above, remember,

101
00:06:23,466 --> 00:06:29,532
is equal to 1 minus norm.dist. And this time we want

102
00:06:29,533 --> 00:06:29,566


103
00:06:29,566 --> 00:06:33,966
above 78, because we're looking for that other white part of the distribution. Okay,

104
00:06:33,966 --> 00:06:38,199


105
00:06:38,200 --> 00:06:44,366
and now the same is before.

106
00:06:44,366 --> 00:06:44,399


107
00:06:44,400 --> 00:06:50,500
When we totaled up, remember when we totaled to these two up, they equaled 1. If we were

108
00:06:50,500 --> 00:06:50,533


109
00:06:50,533 --> 00:06:53,133
to add at these three values, they

110
00:06:53,133 --> 00:06:58,466


111
00:06:58,466 --> 00:07:04,532
will also equal

112
00:07:04,533 --> 00:07:09,999
1. So this whole distribution will always be equal to 1.

113
00:07:10,000 --> 00:07:13,200


114
00:07:13,200 --> 00:07:19,200
Okay, we have another type of problems that you will be encountering on your homework. Let's say

115
00:07:19,200 --> 00:07:25,266
we have a clinic that wants to identify patients who score low on a test, so that the patients can be offered

116
00:07:25,266 --> 00:07:25,299


117
00:07:25,300 --> 00:07:31,600
a new therapy. The scores are normally distributed with a mean of 80 and a standard deviation of 12. The

118
00:07:31,600 --> 00:07:32,400


119
00:07:32,400 --> 00:07:39,200
clinic decides to find the lowest 40% of scores. What is the score that marks the 40th percentile?

120
00:07:39,200 --> 00:07:39,533


121
00:07:39,533 --> 00:07:45,899
So here, we're not looking for a probability. We're actually looking for an x-value

122
00:07:45,900 --> 00:07:46,466


123
00:07:46,466 --> 00:07:52,666
using a probability score. So to do that,

124
00:07:52,666 --> 00:07:52,932


125
00:07:52,933 --> 00:07:58,299
we're going to use norm.inv, which is the inverse. And for that,

126
00:07:58,300 --> 00:08:02,800


127
00:08:02,800 --> 00:08:09,266
we're going to need to give it probability. So the first argument here will be probability.

128
00:08:09,266 --> 00:08:09,299


129
00:08:09,300 --> 00:08:13,000
We know that it's 40%, so that is 0.00.

130
00:08:13,000 --> 00:08:15,333


131
00:08:15,333 --> 00:08:21,499
And then the mean is 80. And the standard deviation is 12. And here we don't have to worry about telling

132
00:08:21,500 --> 00:08:28,633
it that the cumulative percentage is true. Okay, so we know our x value is 76.95983.

133
00:08:28,633 --> 00:08:32,733


134
00:08:32,733 --> 00:08:38,999
Okay, that's all you need to know about probability and finding an x value given

135
00:08:39,000 --> 00:08:39,033


136
00:08:39,033 --> 00:08:44,899
a percentile. And let us know if you need any help with your homework and we're happy

137
00:08:44,900 --> 00:08:52,400


138
00:08:52,400 --> 00:08:52,966
to help.