Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,004 --> 00:00:01,007
- [Curtis] Whenever you collect data
2
00:00:01,007 --> 00:00:03,009
in Microsoft Excel or another program,
3
00:00:03,009 --> 00:00:06,004
you should always examine it for trends.
4
00:00:06,004 --> 00:00:07,002
In this movie,
5
00:00:07,002 --> 00:00:09,009
I will show you how to examine your data visually
6
00:00:09,009 --> 00:00:12,006
by adding a trend line to a chart.
7
00:00:12,006 --> 00:00:16,001
My sample file is 02_04_trendline.
8
00:00:16,001 --> 00:00:18,003
And you can find it in the chapter two folder
9
00:00:18,003 --> 00:00:20,007
of the Exercise files collection.
10
00:00:20,007 --> 00:00:25,006
In this workbook, I have a worksheet with a table of data.
11
00:00:25,006 --> 00:00:27,006
And I have the distance
12
00:00:27,006 --> 00:00:30,003
that a customer traveled to get to your store
13
00:00:30,003 --> 00:00:34,002
and then the amount that they spent once they were there.
14
00:00:34,002 --> 00:00:35,005
And if I scroll down,
15
00:00:35,005 --> 00:00:38,000
you can see that the customer who traveled
16
00:00:38,000 --> 00:00:45,008
the farthest came from 152 miles away and spent $195.
17
00:00:45,008 --> 00:00:47,009
I've already created an XY scatter chart
18
00:00:47,009 --> 00:00:51,006
to show the relationship between distance and amount spent.
19
00:00:51,006 --> 00:00:55,006
And you can see that there is a fairly clear relationship.
20
00:00:55,006 --> 00:00:59,003
If you want to find a line that best fits the data,
21
00:00:59,003 --> 00:01:02,001
you can add a trendline to your chart.
22
00:01:02,001 --> 00:01:05,002
To do that, I will click the body of the chart.
23
00:01:05,002 --> 00:01:10,003
And then on the Chart Design contextual tab on the ribbon,
24
00:01:10,003 --> 00:01:15,006
I will go to the far left and click Add Chart Element,
25
00:01:15,006 --> 00:01:19,009
and then point to trendline, and then click Linear.
26
00:01:19,009 --> 00:01:22,002
That's because I know that the data
27
00:01:22,002 --> 00:01:24,009
in this particular dataset is linear.
28
00:01:24,009 --> 00:01:26,009
So I'll go ahead and click that.
29
00:01:26,009 --> 00:01:28,006
And I can see the trendline.
30
00:01:28,006 --> 00:01:30,003
And in this case,
31
00:01:30,003 --> 00:01:33,002
it turns out that the trendline intersects
32
00:01:33,002 --> 00:01:38,001
almost exactly with the last data point in the set.
33
00:01:38,001 --> 00:01:39,006
That doesn't usually happen.
34
00:01:39,006 --> 00:01:43,001
So if it doesn't for your data, don't worry about it.
35
00:01:43,001 --> 00:01:47,001
So what this trendline tells you is that an individual
36
00:01:47,001 --> 00:01:52,004
who travels approximately 120 miles is likely to spend
37
00:01:52,004 --> 00:01:57,008
a little bit over $150, probably very close to 160.
38
00:01:57,008 --> 00:02:00,003
Where if they traveled 60 miles,
39
00:02:00,003 --> 00:02:07,004
they will most likely spend about 85 to $90.
40
00:02:07,004 --> 00:02:08,003
And again,
41
00:02:08,003 --> 00:02:12,003
this is a line that is designed to show you the trend,
42
00:02:12,003 --> 00:02:15,003
even though some values are going to be far from the line.
43
00:02:15,003 --> 00:02:16,005
And you can see for this point
44
00:02:16,005 --> 00:02:20,001
where someone traveled 100 miles and spent $200,
45
00:02:20,001 --> 00:02:21,003
that's far from the average.
46
00:02:21,003 --> 00:02:22,008
So they spent more than expected,
47
00:02:22,008 --> 00:02:24,000
but another customer who traveled
48
00:02:24,000 --> 00:02:27,003
about the same distance spent less than expected.
49
00:02:27,003 --> 00:02:30,003
If you want to change the format
50
00:02:30,003 --> 00:02:32,007
or appearance of your trendline,
51
00:02:32,007 --> 00:02:37,002
you can go to the Chart Elements control,
52
00:02:37,002 --> 00:02:38,005
point to Trendline,
53
00:02:38,005 --> 00:02:41,001
and then click the arrow that appears to the side,
54
00:02:41,001 --> 00:02:43,003
and then click More Options.
55
00:02:43,003 --> 00:02:46,005
That opens the Format Trendline task pain.
56
00:02:46,005 --> 00:02:48,005
And you can change elements
57
00:02:48,005 --> 00:02:51,003
of it such as, for example, forecasting.
58
00:02:51,003 --> 00:02:53,009
In this case because we don't have time series data,
59
00:02:53,009 --> 00:02:56,008
it doesn't actually make sense to use a forecast
60
00:02:56,008 --> 00:02:59,006
for values outside of your data range.
61
00:02:59,006 --> 00:03:02,005
We have collected data on individuals who traveled
62
00:03:02,005 --> 00:03:05,006
up to 150 miles to get to your store.
63
00:03:05,006 --> 00:03:08,000
And the question is, if this trend holds,
64
00:03:08,000 --> 00:03:11,004
if someone travels say 300 miles,
65
00:03:11,004 --> 00:03:15,002
would they actually spend $400 typically?
66
00:03:15,002 --> 00:03:16,007
That doesn't seem likely.
67
00:03:16,007 --> 00:03:18,002
And that's a sort of judgment
68
00:03:18,002 --> 00:03:20,001
you have to make about your business.
69
00:03:20,001 --> 00:03:20,009
So even so,
70
00:03:20,009 --> 00:03:24,000
you should limit your predictions to the dataset
71
00:03:24,000 --> 00:03:26,004
and data range that you have.
72
00:03:26,004 --> 00:03:28,004
If someone travels 160 miles,
73
00:03:28,004 --> 00:03:30,003
even though it's more than 152,
74
00:03:30,003 --> 00:03:32,008
you can probably make reasonable assumptions
75
00:03:32,008 --> 00:03:35,000
about how much they're going to spend.
5867
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.