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,003
- [Instructor] In the previous movie,
3
00:00:06,003 --> 00:00:08,005
I described a set of challenges
4
00:00:08,005 --> 00:00:12,001
working with the skills that you learned in Chapter 1.
5
00:00:12,001 --> 00:00:15,003
Once again, we're working with 30 days of sales data,
6
00:00:15,003 --> 00:00:17,004
and we can see that over here on the left
7
00:00:17,004 --> 00:00:21,000
in columns A and B, I have the sales broken down by day
8
00:00:21,000 --> 00:00:22,004
and you see that we do in fact
9
00:00:22,004 --> 00:00:25,002
have 30 days of data.
10
00:00:25,002 --> 00:00:27,008
In column D, I have a list of summaries
11
00:00:27,008 --> 00:00:32,001
that I would like to perform and we can do that in column E.
12
00:00:32,001 --> 00:00:34,009
So in cell E5, the first thing I want to do
13
00:00:34,009 --> 00:00:36,003
is calculate the mean
14
00:00:36,003 --> 00:00:39,003
or average of the values in column B.
15
00:00:39,003 --> 00:00:42,002
So in E5, which I've already clicked,
16
00:00:42,002 --> 00:00:43,008
I will type an equal sign.
17
00:00:43,008 --> 00:00:46,009
And the average function is average.
18
00:00:46,009 --> 00:00:50,000
And, again, that's also called the mean.
19
00:00:50,000 --> 00:00:54,005
The range is B4 through B33,
20
00:00:54,005 --> 00:00:56,001
and that won't be copying this formula
21
00:00:56,001 --> 00:00:58,001
so I don't need to worry about absolute
22
00:00:58,001 --> 00:00:59,003
or relative references.
23
00:00:59,003 --> 00:01:01,008
Press Enter, and I see the average
24
00:01:01,008 --> 00:01:07,004
is about $1,319.47 of sales per day.
25
00:01:07,004 --> 00:01:10,006
If I were to sort those values into either ascending
26
00:01:10,006 --> 00:01:14,007
or descending order, then I could calculate the median.
27
00:01:14,007 --> 00:01:17,005
Because we have an even number of values, 30,
28
00:01:17,005 --> 00:01:20,003
the median will be the average
29
00:01:20,003 --> 00:01:24,006
of the two values in the middle, in positions 15 and 16.
30
00:01:24,006 --> 00:01:27,001
So in E6, I'll type an equals sign.
31
00:01:27,001 --> 00:01:32,007
And median is the function. B4 through B33.
32
00:01:32,007 --> 00:01:35,000
Right parenthesis and enter.
33
00:01:35,000 --> 00:01:40,007
And we see that the median is $1,285.50.
34
00:01:40,007 --> 00:01:42,006
And that's very close to the mean
35
00:01:42,006 --> 00:01:46,009
so that tells me that we don't have any outlying values.
36
00:01:46,009 --> 00:01:48,008
There are no extremely large
37
00:01:48,008 --> 00:01:51,009
or extremely small numbers in the data set.
38
00:01:51,009 --> 00:01:54,005
If you want to find the maximum and minimum values,
39
00:01:54,005 --> 00:01:57,006
then we can in cell E8 find the maximum.
40
00:01:57,006 --> 00:02:01,005
So that's equal, max, left parenthesis
41
00:02:01,005 --> 00:02:05,008
and then B4 to B33, Enter.
42
00:02:05,008 --> 00:02:11,000
So the largest sales day had sales of 25.99.
43
00:02:11,000 --> 00:02:13,008
And our minimum will be the smallest.
44
00:02:13,008 --> 00:02:17,004
So equal min, which is the minimum function
45
00:02:17,004 --> 00:02:22,009
and B4 through B33 and right parenthesis and Enter.
46
00:02:22,009 --> 00:02:25,006
And the smallest was only 189.
47
00:02:25,006 --> 00:02:29,009
So it's possible something untoward happened that day.
48
00:02:29,009 --> 00:02:31,008
And it might be worth looking into
49
00:02:31,008 --> 00:02:35,002
in terms of why sales were that low.
50
00:02:35,002 --> 00:02:38,002
Next, we can calculate our quartiles.
51
00:02:38,002 --> 00:02:41,000
The first quartile shows the cutoff
52
00:02:41,000 --> 00:02:45,000
for where the lowest 25% of values will stop.
53
00:02:45,000 --> 00:02:47,008
So in cell E10, I'll type an equals sign,
54
00:02:47,008 --> 00:02:49,005
and then quartile.
55
00:02:49,005 --> 00:02:52,007
And I will use exclusive, which does not include
56
00:02:52,007 --> 00:02:55,003
the minimum and the maximum.
57
00:02:55,003 --> 00:03:01,006
So I will make sure the quartile.exc is highlighted.
58
00:03:01,006 --> 00:03:06,005
And then the array is B4 through B33, comma,
59
00:03:06,005 --> 00:03:09,008
and then the first quartile.
60
00:03:09,008 --> 00:03:16,006
So number one, right parenthesis and $1,072.25.
61
00:03:16,006 --> 00:03:18,008
Now we can look at the third quartile
62
00:03:18,008 --> 00:03:21,009
and that's where 25% of values are above it
63
00:03:21,009 --> 00:03:23,009
and 75% below.
64
00:03:23,009 --> 00:03:28,000
So I'll type an equals sign, quartile.exc,
65
00:03:28,000 --> 00:03:29,005
same one we used before.
66
00:03:29,005 --> 00:03:32,009
Then B4 through B33 and a comma.
67
00:03:32,009 --> 00:03:36,006
And we'll look at the third quartile, 75%.
68
00:03:36,006 --> 00:03:39,000
So type a three, right parenthesis and Enter.
69
00:03:39,000 --> 00:03:44,009
And we get $1,569.75.
70
00:03:44,009 --> 00:03:49,006
If we look at the median of 1,285.50
71
00:03:49,006 --> 00:03:52,008
it fits nicely in the middle of the first quartile
72
00:03:52,008 --> 00:03:56,004
and second quartile values.
73
00:03:56,004 --> 00:03:59,001
Another way to analyze the spread of your data is
74
00:03:59,001 --> 00:04:02,008
by calculating variance and standard deviation.
75
00:04:02,008 --> 00:04:06,004
Variance, again, is the sum of the squared error
76
00:04:06,004 --> 00:04:10,001
divided by the number of items in the list.
77
00:04:10,001 --> 00:04:14,001
So I'll type equal and then var.
78
00:04:14,001 --> 00:04:18,008
And I will use VAR.S, which is based on a sample.
79
00:04:18,008 --> 00:04:23,004
And then the range, again, B4 through B33,
80
00:04:23,004 --> 00:04:25,000
right parenthesis and Enter.
81
00:04:25,000 --> 00:04:30,005
And we get 249,270.6.
82
00:04:30,005 --> 00:04:34,005
The standard deviation is the square root of that value.
83
00:04:34,005 --> 00:04:39,007
So I'll type an equals sign and STDEV.S.
84
00:04:39,007 --> 00:04:40,008
Again, working with samples
85
00:04:40,008 --> 00:04:43,005
which is a more conservative estimate.
86
00:04:43,005 --> 00:04:48,009
And then B4 through B33, right parenthesis and enter.
87
00:04:48,009 --> 00:04:53,006
And we get 499.27, which is the square root
88
00:04:53,006 --> 00:04:56,003
of the variance that we had earlier.
89
00:04:56,003 --> 00:04:59,001
And looking at our values, we're looking at the average
90
00:04:59,001 --> 00:05:03,006
of 1,319.47, and with a standard deviation
91
00:05:03,006 --> 00:05:07,001
of about 500, then one standard deviation above
92
00:05:07,001 --> 00:05:09,001
would be about 1,800.
93
00:05:09,001 --> 00:05:12,002
And one below would be about 800.
94
00:05:12,002 --> 00:05:14,009
And given the range of values that we have,
95
00:05:14,009 --> 00:05:20,007
with our maximum of almost 2,600 and our minimum of 189,
96
00:05:20,007 --> 00:05:23,002
it looks like our standard deviation represents
97
00:05:23,002 --> 00:05:26,000
the data in our collection pretty well.
7546
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.