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:02,006
- Most business data describes dollar amounts
2
00:00:02,006 --> 00:00:05,009
in the hundreds, thousands, or hundreds of thousands.
3
00:00:05,009 --> 00:00:07,002
A lot of scientific data,
4
00:00:07,002 --> 00:00:10,004
particularly in the fields of biology and astronomy,
5
00:00:10,004 --> 00:00:12,005
describe much larger quantities.
6
00:00:12,005 --> 00:00:15,007
For example, you might have celestial bodies
7
00:00:15,007 --> 00:00:19,008
that are millions or billions of miles away,
8
00:00:19,008 --> 00:00:21,004
and you can see those distances
9
00:00:21,004 --> 00:00:25,005
in light years expressed here using exponents.
10
00:00:25,005 --> 00:00:27,009
If you do need to work with data
11
00:00:27,009 --> 00:00:30,003
that contains extremely large values,
12
00:00:30,003 --> 00:00:32,008
then it makes sense to use logarithmic scales.
13
00:00:32,008 --> 00:00:34,008
And I will show you how to do that
14
00:00:34,008 --> 00:00:38,005
in a chart in Excel in this movie.
15
00:00:38,005 --> 00:00:42,002
My sample file is 02_03_LogarithmicScale,
16
00:00:42,002 --> 00:00:44,004
and you can find that in the Chapter02 folder
17
00:00:44,004 --> 00:00:47,002
of the exercise files collection.
18
00:00:47,002 --> 00:00:51,001
This workbook contains distance data for stars and galaxies,
19
00:00:51,001 --> 00:00:54,000
so the values are extremely large.
20
00:00:54,000 --> 00:00:56,004
But before I get into how to change the chart
21
00:00:56,004 --> 00:00:57,009
and what it's actually showing,
22
00:00:57,009 --> 00:01:01,007
I want to review exponents and logarithms.
23
00:01:01,007 --> 00:01:05,008
An exponent is used to multiply a number by itself.
24
00:01:05,008 --> 00:01:09,000
For example, in cell A13,
25
00:01:09,000 --> 00:01:13,002
you can see that I have text for the formula 2^3,
26
00:01:13,002 --> 00:01:16,000
and the ^ is used to indicate an exponent.
27
00:01:16,000 --> 00:01:19,007
And if I go into cell B13, type an equal sign,
28
00:01:19,007 --> 00:01:26,001
and then 2^3 and Enter, I get the value of 8.
29
00:01:26,001 --> 00:01:31,001
So that means that 2 multiplied by itself 3 times,
30
00:01:31,001 --> 00:01:34,005
2 times 2 times 2 gives you 8.
31
00:01:34,005 --> 00:01:37,006
The inverse of that operation is to find the logarithm.
32
00:01:37,006 --> 00:01:41,006
So if I want to find the logarithm of 8 base 2,
33
00:01:41,006 --> 00:01:44,000
Excel will calculate the number of times
34
00:01:44,000 --> 00:01:45,006
you need to multiply 2 by itself
35
00:01:45,006 --> 00:01:47,006
to get the number 8.
36
00:01:47,006 --> 00:01:51,002
So in cell B14, I'll type = log
37
00:01:51,002 --> 00:01:54,006
and then 8,2 and Enter.
38
00:01:54,006 --> 00:01:56,004
And you see I get 3,
39
00:01:56,004 --> 00:02:01,005
and that's because 2 raised to the 3rd power is 8.
40
00:02:01,005 --> 00:02:03,003
Now let's take a look at the data
41
00:02:03,003 --> 00:02:07,000
that we have in column B above the calculations I just did.
42
00:02:07,000 --> 00:02:09,008
And I'll click in cell B6.
43
00:02:09,008 --> 00:02:12,003
That value is displayed in scientific notation.
44
00:02:12,003 --> 00:02:16,004
And you can see it's 3.5E to the +01
45
00:02:16,004 --> 00:02:21,009
So, in other words, it is 3.5 times 10 to the 1st power,
46
00:02:21,009 --> 00:02:23,002
and that's in light years.
47
00:02:23,002 --> 00:02:24,006
And if you look on the formula bar,
48
00:02:24,006 --> 00:02:27,004
you can see the value of 35.
49
00:02:27,004 --> 00:02:32,004
So that means that you have 3.5 times 10, which equals 35,
50
00:02:32,004 --> 00:02:36,004
so it's a relatively close star or galaxy.
51
00:02:36,004 --> 00:02:40,009
The next value in cell B7 is 9,760,
52
00:02:40,009 --> 00:02:44,003
and that's 9.76 times 10 to the 3rd.
53
00:02:44,003 --> 00:02:48,008
And that continues down to the last value in cell B11,
54
00:02:48,008 --> 00:02:53,004
which is 90 million, 210 thousand light years away.
55
00:02:53,004 --> 00:02:57,005
So that is a relatively distant object.
56
00:02:57,005 --> 00:03:00,000
If you look at how that data is displayed in the chart,
57
00:03:00,000 --> 00:03:03,000
you can see that the vertical axis starts at 0
58
00:03:03,000 --> 00:03:06,007
and it goes all the way up to 1.00 times 10 to the 8th,
59
00:03:06,007 --> 00:03:09,008
which is large enough to contain the largest value
60
00:03:09,008 --> 00:03:11,005
in our data set.
61
00:03:11,005 --> 00:03:14,002
And you can see what that does to the scale.
62
00:03:14,002 --> 00:03:18,000
And so our first, second, third and fourth values
63
00:03:18,000 --> 00:03:21,009
are barely off of the baseline at 0
64
00:03:21,009 --> 00:03:23,009
compared to our largest value.
65
00:03:23,009 --> 00:03:28,000
You can see 4, maybe, peeking up just a little bit
66
00:03:28,000 --> 00:03:31,006
and 5, you can see that the center of the dot is off of 0,
67
00:03:31,006 --> 00:03:36,009
but they are incredibly small compared to our largest value.
68
00:03:36,009 --> 00:03:38,005
So that means you might want
69
00:03:38,005 --> 00:03:42,002
to change the scale to logarithms.
70
00:03:42,002 --> 00:03:45,009
To do that, I will right-click the vertical axis
71
00:03:45,009 --> 00:03:49,003
and then click Format Axis.
72
00:03:49,003 --> 00:03:52,006
The Format Axis task pane appears.
73
00:03:52,006 --> 00:03:54,000
And on the right,
74
00:03:54,000 --> 00:03:56,006
you can see, under display units,
75
00:03:56,006 --> 00:03:59,001
that you can change the scale.
76
00:03:59,001 --> 00:04:01,008
So I can go from a linear scale,
77
00:04:01,008 --> 00:04:04,007
which is just counting 1, 2, 3, 4, 5
78
00:04:04,007 --> 00:04:06,009
and change it to a logarithmic scale.
79
00:04:06,009 --> 00:04:12,003
So I will check that box, and I'll leave the base as 10,
80
00:04:12,003 --> 00:04:14,007
'cause that's the way that the data was created.
81
00:04:14,007 --> 00:04:18,006
And you can see that now the chart is much more readable.
82
00:04:18,006 --> 00:04:20,007
You have to know beforehand
83
00:04:20,007 --> 00:04:24,006
that you are reading chart that has data shown
84
00:04:24,006 --> 00:04:26,004
based on a log algorithmic scale.
85
00:04:26,004 --> 00:04:29,006
But for someone who works with scientific data,
86
00:04:29,006 --> 00:04:31,005
that's not a hard thing to do.
87
00:04:31,005 --> 00:04:34,002
It's something that you will do all the time.
88
00:04:34,002 --> 00:04:35,007
So if you need to represent data
89
00:04:35,007 --> 00:04:37,000
with extremely large values,
90
00:04:37,000 --> 00:04:40,005
such as for astronomy, biology, or chemistry,
91
00:04:40,005 --> 00:04:43,000
then a logarithmic scale for your vertical axis
92
00:04:43,000 --> 00:04:45,000
is the best way to go.
7249
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.