Saltar navegación

Activa JavaScript para disfrutar de los vídeos de la Mediateca.

Use Excel to make the Earth's past equivalent to 2020 - Contenido educativo

Ajuste de pantalla

El ajuste de pantalla se aprecia al ver el vídeo en pantalla completa. Elige la presentación que más te guste:

Subido el 16 de diciembre de 2020 por Andrés G.

56 visualizaciones

Instrucciones en inglés de cómo hacer cálculos con Excel para saber equivalencia en días de eones y eras, y colorear así el calendario de 2020.

Más información Transcripción

Descargar la transcripción

Greetings! This is a video to teach you how to calculate and create a calendar 00:00:00
that represents the Earth's past as if it had lasted only one year. Then, as you 00:00:09
know, the Earth formed 4600 million years ago and it is divided into eons and we 00:00:16
studied how the last aeon, the Phanerozoic aeon, is divided into three eras. The first aeon, the 00:00:25
Hadean aeon, had a lot of meteoritic impacts. It started 4,600 million years ago. It finished 00:00:33
approximately 4,000 million years ago. It was followed by the Archean aeon, in which life 00:00:41
appeared and the tectonic plates were already moving. Then we have the Proterozoic aeon at this 00:00:47
beginning, with descent, and then in the Proterozoic Eon, well, we know that there were no animals 00:00:54
like now, but life was evolving, then finally we have the Phanerozoic Eon, which begins 00:01:03
with the Cambrian Explosion, okay? 00:01:11
The Cambrian Explosion is the apparently sudden appearance of the main types of animals that 00:01:13
exist now. 00:01:19
So the Cambrian is the beginning of the Phanerozoic Eon, and it begins with the Paleozoic Era. 00:01:20
The era in which there were trilobites, there were different kinds of animals, first in the oceans, then in the continents. 00:01:28
Fish evolved from fish amphibians and then reptiles. 00:01:36
Then 250 million years ago the Paleozoic era finished with the Permian to Triassic mass extinction 00:01:41
and that was the beginning of the Mesozoic era, which was the era of the dinosaurs 00:01:53
and the era also in which mammals and birds evolved. 00:01:58
It started 250 million years ago and it finished 66 million years ago with the extinction of the last non-avian dinosaurs, 00:02:03
which was the beginning of the Cenozoic era, which is the era in which we live now, and was the era of the expansion of mammals. 00:02:13
Since the Cambrian is the beginning of the Phanerozoic eon, approximately 545 million years ago, 00:02:21
Then all the time before that, which is most of the existence of the Earth, is called the Precambrian. 00:02:30
Well, then all this history of the Earth took 4600 million years. 00:02:43
But now we want to calculate what if it had lasted only one year? 00:02:51
If it had lasted one year, then how much of that year would have been the Hadean Aeon? 00:02:57
how much would have been the Archaean Aeon, etc. 00:03:02
Ok, so what we want to do is something like this image here. 00:03:06
This is an image in which we have one year, so January, 00:03:10
February, March, the different days in one year. So 00:03:14
January has 31 days, then February usually has 28, March 00:03:18
again 31, April 30. So we want, with colors, 00:03:22
we want to indicate how much of this 00:03:26
how many days will have been the Hadean eon, then the Archean eon, the Proterozoic eon, etc. 00:03:30
And we want to calculate that with Excel. 00:03:38
So that's why you have in my website and in Teams this Excel file in which we are going to perform the calculations 00:03:41
and also you have another sheet in Excel in which you have this calendar, 00:03:53
this 2020 calendar, in which we will color the different days 00:03:59
depending on the eons they represent. 00:04:05
So the days that represent the Hadean eon will be in red, 00:04:10
then the ones of the Archean eon will be light green, 00:04:16
the Proterozoic eon will be yellow, etc. 00:04:19
So how do we perform these calculations? 00:04:22
Well, the first thing we have to do is we have to tell Excel 00:04:24
cell to calculate the length of each of these eons. 00:04:27
For instance, for the Hadean eon what we have to do is we click 00:04:31
on the cell, then we type the equal sign and 00:04:35
we indicate that we want to subtract from this cell 00:04:39
we subtract this other cell. 00:04:43
So this cell minus this cell 00:04:47
we press enter is 600 million years. So now we see that 00:04:51
the Hadean eon was 600 million years, which of course is very easy to calculate with this. 00:04:55
For the Archean eon we will do the same, we will subtract 4000 million years ago, which 00:05:00
is the beginning, minus 2500 million years. 00:05:05
Also the easiest way to do that is we just copy what we wrote for the Hadean eon and 00:05:10
then we paste that formula, only the formula, here. 00:05:18
So this way what Excel has done is it has copied the formula on this cell, on this other 00:05:23
cell, but it has changed the numbers. 00:05:32
So instead of using the numbers of the red row it has used the ones of the light row. 00:05:35
And I can do the same here and remember we are going to paste only the formula because 00:05:40
if we paste all of it we will also paste the format color which we don't want to do. 00:05:45
Then I paste it also here, and I have made a little mistake here. 00:05:52
Let me correct it. 00:06:00
I don't know why there was a little mistake here, but I am going again to copy the calculation 00:06:04
I told it to perform, then I paste only the formula, and then here it is. 00:06:14
And I am going to do the same in the other cells. 00:06:20
So I paste only the formulas and here I paste only the formulas. 00:06:26
So then this way we have the length of every eon and every era. 00:06:33
Now we want to know what percentage it is inside the Earth's existence for the Hadean. 00:06:39
Then I click here on this cell and then I type the equal sign. 00:06:45
Then what we want to do is, we want a percentage. 00:06:50
So I will start by multiplying 100 times this length here, this cell. 00:06:54
And the result has to be divided by the total length of the Earth's existence, which was 00:07:01
4600 million years. 00:07:07
I may type that number, but instead I will choose this cell. 00:07:09
When we press enter, then we see that approximately 13% of the Earth's existence was the Hadean 00:07:13
Eon. 00:07:22
Now, we want to do the same here, but there is a little problem. 00:07:23
The little problem is that here we also want to multiply times 100 the length of the Eon, 00:07:27
but we don't want to divide it by the number here, the 4000 million, but we want to divide 00:07:35
by the total existence of the Earth which is 4600. 00:07:43
So then, to do that, what I have to do is first I copy this formula, 00:07:47
but in this formula, as you can see, I have indicated the C4 cell 00:07:54
and it is not locked. It means when I paste the formula for the Archeaneum 00:08:01
The computer has no idea what we are doing. 00:08:08
It will simply use the equivalent cell. 00:08:10
I don't want to do that. 00:08:13
I want to use the 4600 for all of them. 00:08:14
So what I do is I type the dollar symbol in front of the C 00:08:18
and I type the dollar symbol in front of the 4. 00:08:23
If I do that, then the result here doesn't change. 00:08:28
But what I'm telling Excel is that for every cell I type here, 00:08:32
The division I want it to be by the 4600, 00:08:36
by the content of this cell here. 00:08:42
So now that I have indicated that, 00:08:45
then now I can copy and I can paste only the formula 00:08:47
to keep the format. 00:08:51
Then I will also do it here, 00:08:53
and let us see, I will also do it here, 00:08:55
and then I will also do it here, 00:08:59
and I will also do it here. 00:09:02
So this way we see the percentage inside of all of Earth's history for each of these 00:09:05
eons and eons. Okay, and then now once we have the percentage now we can calculate the equivalence in days 00:09:10
for each of these percentages and what I do is I click here then I 00:09:17
type equal then I select this cell and I multiply it 00:09:22
times 00:09:28
And since what I have in the percentage is a percentage, and it has been multiplied times 00:09:30
100, then I am going to divide it by 100 so that I get the right number. 00:09:38
Which means the Hedian Eon will be the equivalent of approximately 48 days in a year. 00:09:44
Now that I have typed that formula, then I am going to paste the formula in the other 00:09:51
cells and this way I am calculating how many days each of the eons and eras is. 00:09:58
Okay, and then once I have done this, once I have indicated that, now in the 2020 00:10:08
calendar I can start coloring. Okay, so as you see approximately 48 days in a year 00:10:16
will be the Hadean Aeon. The Hadean Aeon has this color, okay? So I am going to 00:10:25
copy the format and then in the 2020 calendar these days will have that red 00:10:32
color. I also paste, well copy the format, I select also these days in January, then 00:10:42
Then again I copy the format. 00:10:49
This way the 31 days of January are labelled red as the Hadean Eon. 00:10:52
But the Hadean Eon we calculated was 48 days. 00:11:00
So this means the 31 days of January are not enough. 00:11:05
We need 17 more days which will be from January. 00:11:09
So I have to copy the format. 00:11:13
Then I select this number of days, I select also this number of days, and, well, no, I 00:11:17
did it wrong here, yes, because I have to select only one cell, only one cell here, 00:11:24
and then I select the format, here it is. 00:11:30
Ok, so now we have the 48 days that represent the Hadean in the same color as the Hadean. 00:11:34
Ok, and now I will have to do the same for the Archea Neon, 119 days will be the light color, I copy the format and then I start selecting these days in February to do the same. 00:11:41
And I will have to select more days, like this, in March, and so on, until I reach the 119 days for the Archean Eon. 00:11:59
Then I will select the yellow color for the Proterozoic Eon, which will be 155 days, and so on. 00:12:12
So this is how you have to proceed on this Excel file and this calendar, 00:12:19
so that I see you know how to use Excel and how you have used it for the calendar 00:12:26
to see how many days each of the eras and eons will be. 00:12:32
Okay, so that is enough. Goodbye. 00:12:37
Idioma/s:
en
Idioma/s subtítulos:
en
Autor/es:
Andrés Gaytán de Ayala Alonso
Subido por:
Andrés G.
Licencia:
Dominio público
Visualizaciones:
56
Fecha:
16 de diciembre de 2020 - 18:35
Visibilidad:
Público
Centro:
IES AGORA
Descripción ampliada:
Aprendizaje básico de Excel
Duración:
12′ 40″
Relación de aspecto:
1.79:1
Resolución:
1212x678 píxeles
Tamaño:
31.24 MBytes

Del mismo autor…

Ver más del mismo autor

EducaMadrid, Plataforma Educativa de la Comunidad de Madrid

Plataforma Educativa EducaMadrid