Microsoft Excel Tutorial:
To play along, read the trivia questions and download the data set here: Space Trivia
In case you have problems solving any of the trivia questions, I've created a video showing the answers. Jump to the section for any of the five questions:
Table of Contents
(0:00) Welcome
(0:22) How many astronauts have orbited the Moon?
(4:35) Who had the most hours of spacewalk activity?
(5:25) Who has launched the most number of times?
(6:27) Woman with the most days in space?
(7:41) How many launched on Shuttle and returned on Soyuz?
(9:26) Wrap-up
To play along, read the trivia questions and download the data set here: Space Trivia
In case you have problems solving any of the trivia questions, I've created a video showing the answers. Jump to the section for any of the five questions:
Table of Contents
(0:00) Welcome
(0:22) How many astronauts have orbited the Moon?
(4:35) Who had the most hours of spacewalk activity?
(5:25) Who has launched the most number of times?
(6:27) Woman with the most days in space?
(7:41) How many launched on Shuttle and returned on Soyuz?
(9:26) Wrap-up
Transcript of the video:
Hey, thanks for playing the Microsoft Day of Data Treasure Hunt.
I am Bill Jelen from MrExcel.
In case you had any problems with any of these, I made a short video here showing you how I came up with each each answer.
Now, the great thing about Excel is there's five ways to do everything.
There is a good chance that you might have found a different way to solve the problem. Let's get started. All right.
Our first question: How many space travelers have been on a mission that orbited the moon.
With a hint there to be careful because Jim Lovell went to the moon on two missions but we're only counting him as one.
Now, if you don't go to Excel.
You are just might think that a well-known fact is 12 people have walked on the Moon. So you might say that it's 12. But let's take a look at the data.
So we come here to our workbook. We have five worksheets. I go to the Crewed Missions worksheet.
There is a column here for Lunar Orbit. Let's turn on the filters. This is on the Data tab.
Turn on the filters for Lunar Orbit. We want the Lunar Orbit equal to True so I uncheck False.
And when we scroll up we see that these nine missions: Apollo 8, 10, 11, 12, 13, 14, 15, 16 and 17 have all gone around the moon. If you know the Apollo each had three people per mission. So nine times three is 27.
Oh, no, that's where you might go wrong.
All right so let's come back here to Mission by Crew. Again we'll turn on the filters here and under Destination and we'll choose just the Moon.
All right and there are a list of the people.
When I select those people down here in the lower right-hand corner we see that there's 27. But there are people that are in here twice.
You could just look at this. Like there's Jim Lovell and Jim Lovell.
But on the Home tab we go to Conditional Formatting. Highlight Cells. And ask for Duplicate Values.
You'll see that Jim Lovell is there twice. Gene Cernan is there twice. John Young is there twice.
All right. Now, there's actually two different ways that you could go here.
One is a brand new function in excel called the FILTER function.
And because this is a cool function let's talk about that. I am going to come out here and I am going to say =FILTER(.
I want to see from this data set.
I want to see all those names in column A where the destination that's in column H. So I choose the destination is equal to, Moon in quotes.
Close parentheses.
And that one formula will give me a list of all of the people who have gone to the moon. There's 27 people there. But I need to throw out the duplicates.
I only want to keep one of Jim Lovell and John Young.
So we're going to edit that formula and wrap the FILTER function in another new function called UNIQUE.
All right so the UNIQUE function and that gives me the list of 24. How do I know there's 24? Ask for equal COUNTA of that list.
The hash there says give me all the results of that new dynamic array formula.
And there's the 24.
Probably the easier way to go though is to come here to the space traveler sheet where Microsoft has very nicely created a summary for us and we'll turn the filters on again.
Here now if we would look for a Walk on the Moon greater than zero that's where you'll see the 12 people who have walked on the moon but that's not what we're looking for.
We're looking for the people who left Low Earth Orbit.
I just want to call out here this is really interesting to me there are 570 rows so that's 569 people minus the heading who have left Earth on a mission.
Out of those people almost all of them have gone to what's known as Low Earth Orbit for a space station. That might be anywhere from 60 to 250 miles above the Earth.
The Moon, though, that's 240,000 miles above the Earth.
So out of these 500 people the ones we're really interested in are the people who have left Low Earth Orbit. And right there that's the list of the 24 people. Only 24!
Have gone more than 250 miles above the earth.
And right now the only place where they've gone is the Moon. Someday that may be Mars or an asteroid.
But right now these 24 people are the 24 people who have gotten to within 60 to 70 miles of the moon.
A very special special list of people there who have gone further than anyone else. Alright so the answer to question number one is 24.
All right question number two: Who had the most hours of spacewalk activity.
So this is you're in your spacecraft. You put on the EVA suit. EVA is extra vehicular activity.
You go into the airlock and they open the door and now you're outside. All right this is fascinating.
On our workbook here there's a list of all the spacewalks. 829 of those.
Come out here to space travelers where there is an awesome thing called EVA duration in hours.
Man it seems simple just sort this descending and Anatoly Solovyev with 78.8 hours.
Answer to question two is B. All right. Question three: which space traveler has launched the most number of times?
For me, launch is the most exciting part there's all the fire and rumble.
We live here in Florida about 12 miles. 12 miles from where the rockets launch. It shakes the house! Right?
12 miles away. So we're trying to figure out who has launched the most number of times.
The easiest way to get this is to come back here to the space travelers worksheet and we have a mission count column. The mission count column. I want to know who had the most number of missions.
So very easy just choose one cell in column F and on the Data tab click Z to A like that.
All right now we've had two two people to tie with seven missions each Jerry L Ross and Franklin Chang Diaz have seven.
Beyond that there's a big tie for second place with a lot of people with six. All right.
So easy one here. Jerry L Ross and Franklin Chang Diaz answer d for question three.
Question four: Who is the woman with the most days in space?
And you know I've added this question because I think it's important to understand a lot of people know about these guys - The Mercury Seven.
The first seven astronauts.
But people don't realize that at the same time of the Mercury Seven, there were 13 women who were trained for the Mercury program. But the U.S.
Congress said "No, there's no way they're flying".
These ladies are not as well known as the original Mercury Seven but still frequent visitors back here to the Space Coast.
Now the first woman in space was Valentina Tereshkova. That was in 1963 in the Russian space program.
But let's figure out who has spent the most time in space. So we have our space travelers here.
I will add the filter in and go to the gender column and choose only Female.
Alright so there's a list of all the female astronauts and cosmonauts who have flown. And then here mission time in column G.
I'll choose one cell and click Z to A. There we are and there we are Peggy Whitson with 665.94 days in space.
So question four the answer is B Peggy Whitson.
All right question number five: How many people launched on the Space Shuttle but then returned on a Soyuz spacecraft?
That means you left from Florida and you land in Russia. All right.
So to do this let's come back to Mission by Crew and there's a nice column here. Column D is Changed Craft.
We'll turn on the filters and filter to Changed Craft equals True. And there are a lot. Actually I was surprised.
A total of 133 times that someone flew up on one spacecraft and flew back on another.
Although you'll see here they flew up in Discovery and came back on Discovery.
But it was a different flight of Discovery. So we want to look for something a little bit more interesting than that.
I want to know who left Earth on the space shuttle.
So here in column B we're going to search for "STS". That stands for Space Transport System.
That's the abbreviation for the space shuttle. Click ok and we get all of the STS flights. All the space shuttle flights.
And then for the return we'll search for Soyuz. And click ok.
Scroll up. And there we are. There's five. Five is the correct answer.
Of course it would be just as interesting to find the opposite so we'll clear that.
We'll look for someone who returned on STS and launched on Soyuz. There it is six.
So five that launched on the Shuttle and came back on Soyuz. Six who launched on Soyuz and came back on the Shuttle.
But the way the question is worded how many launched on the Shuttle and return on Soyuz. So it is five. Answer C.
All right there you have it. I hope you had fun playing. Check out that data set.
All kinds of fun things you can discover about all the astronauts and spacewalks and rockets that we're using to go to space. I want to thank you for playing. This is Bill Jelen from MrExcel.com
I am Bill Jelen from MrExcel.
In case you had any problems with any of these, I made a short video here showing you how I came up with each each answer.
Now, the great thing about Excel is there's five ways to do everything.
There is a good chance that you might have found a different way to solve the problem. Let's get started. All right.
Our first question: How many space travelers have been on a mission that orbited the moon.
With a hint there to be careful because Jim Lovell went to the moon on two missions but we're only counting him as one.
Now, if you don't go to Excel.
You are just might think that a well-known fact is 12 people have walked on the Moon. So you might say that it's 12. But let's take a look at the data.
So we come here to our workbook. We have five worksheets. I go to the Crewed Missions worksheet.
There is a column here for Lunar Orbit. Let's turn on the filters. This is on the Data tab.
Turn on the filters for Lunar Orbit. We want the Lunar Orbit equal to True so I uncheck False.
And when we scroll up we see that these nine missions: Apollo 8, 10, 11, 12, 13, 14, 15, 16 and 17 have all gone around the moon. If you know the Apollo each had three people per mission. So nine times three is 27.
Oh, no, that's where you might go wrong.
All right so let's come back here to Mission by Crew. Again we'll turn on the filters here and under Destination and we'll choose just the Moon.
All right and there are a list of the people.
When I select those people down here in the lower right-hand corner we see that there's 27. But there are people that are in here twice.
You could just look at this. Like there's Jim Lovell and Jim Lovell.
But on the Home tab we go to Conditional Formatting. Highlight Cells. And ask for Duplicate Values.
You'll see that Jim Lovell is there twice. Gene Cernan is there twice. John Young is there twice.
All right. Now, there's actually two different ways that you could go here.
One is a brand new function in excel called the FILTER function.
And because this is a cool function let's talk about that. I am going to come out here and I am going to say =FILTER(.
I want to see from this data set.
I want to see all those names in column A where the destination that's in column H. So I choose the destination is equal to, Moon in quotes.
Close parentheses.
And that one formula will give me a list of all of the people who have gone to the moon. There's 27 people there. But I need to throw out the duplicates.
I only want to keep one of Jim Lovell and John Young.
So we're going to edit that formula and wrap the FILTER function in another new function called UNIQUE.
All right so the UNIQUE function and that gives me the list of 24. How do I know there's 24? Ask for equal COUNTA of that list.
The hash there says give me all the results of that new dynamic array formula.
And there's the 24.
Probably the easier way to go though is to come here to the space traveler sheet where Microsoft has very nicely created a summary for us and we'll turn the filters on again.
Here now if we would look for a Walk on the Moon greater than zero that's where you'll see the 12 people who have walked on the moon but that's not what we're looking for.
We're looking for the people who left Low Earth Orbit.
I just want to call out here this is really interesting to me there are 570 rows so that's 569 people minus the heading who have left Earth on a mission.
Out of those people almost all of them have gone to what's known as Low Earth Orbit for a space station. That might be anywhere from 60 to 250 miles above the Earth.
The Moon, though, that's 240,000 miles above the Earth.
So out of these 500 people the ones we're really interested in are the people who have left Low Earth Orbit. And right there that's the list of the 24 people. Only 24!
Have gone more than 250 miles above the earth.
And right now the only place where they've gone is the Moon. Someday that may be Mars or an asteroid.
But right now these 24 people are the 24 people who have gotten to within 60 to 70 miles of the moon.
A very special special list of people there who have gone further than anyone else. Alright so the answer to question number one is 24.
All right question number two: Who had the most hours of spacewalk activity.
So this is you're in your spacecraft. You put on the EVA suit. EVA is extra vehicular activity.
You go into the airlock and they open the door and now you're outside. All right this is fascinating.
On our workbook here there's a list of all the spacewalks. 829 of those.
Come out here to space travelers where there is an awesome thing called EVA duration in hours.
Man it seems simple just sort this descending and Anatoly Solovyev with 78.8 hours.
Answer to question two is B. All right. Question three: which space traveler has launched the most number of times?
For me, launch is the most exciting part there's all the fire and rumble.
We live here in Florida about 12 miles. 12 miles from where the rockets launch. It shakes the house! Right?
12 miles away. So we're trying to figure out who has launched the most number of times.
The easiest way to get this is to come back here to the space travelers worksheet and we have a mission count column. The mission count column. I want to know who had the most number of missions.
So very easy just choose one cell in column F and on the Data tab click Z to A like that.
All right now we've had two two people to tie with seven missions each Jerry L Ross and Franklin Chang Diaz have seven.
Beyond that there's a big tie for second place with a lot of people with six. All right.
So easy one here. Jerry L Ross and Franklin Chang Diaz answer d for question three.
Question four: Who is the woman with the most days in space?
And you know I've added this question because I think it's important to understand a lot of people know about these guys - The Mercury Seven.
The first seven astronauts.
But people don't realize that at the same time of the Mercury Seven, there were 13 women who were trained for the Mercury program. But the U.S.
Congress said "No, there's no way they're flying".
These ladies are not as well known as the original Mercury Seven but still frequent visitors back here to the Space Coast.
Now the first woman in space was Valentina Tereshkova. That was in 1963 in the Russian space program.
But let's figure out who has spent the most time in space. So we have our space travelers here.
I will add the filter in and go to the gender column and choose only Female.
Alright so there's a list of all the female astronauts and cosmonauts who have flown. And then here mission time in column G.
I'll choose one cell and click Z to A. There we are and there we are Peggy Whitson with 665.94 days in space.
So question four the answer is B Peggy Whitson.
All right question number five: How many people launched on the Space Shuttle but then returned on a Soyuz spacecraft?
That means you left from Florida and you land in Russia. All right.
So to do this let's come back to Mission by Crew and there's a nice column here. Column D is Changed Craft.
We'll turn on the filters and filter to Changed Craft equals True. And there are a lot. Actually I was surprised.
A total of 133 times that someone flew up on one spacecraft and flew back on another.
Although you'll see here they flew up in Discovery and came back on Discovery.
But it was a different flight of Discovery. So we want to look for something a little bit more interesting than that.
I want to know who left Earth on the space shuttle.
So here in column B we're going to search for "STS". That stands for Space Transport System.
That's the abbreviation for the space shuttle. Click ok and we get all of the STS flights. All the space shuttle flights.
And then for the return we'll search for Soyuz. And click ok.
Scroll up. And there we are. There's five. Five is the correct answer.
Of course it would be just as interesting to find the opposite so we'll clear that.
We'll look for someone who returned on STS and launched on Soyuz. There it is six.
So five that launched on the Shuttle and came back on Soyuz. Six who launched on Soyuz and came back on the Shuttle.
But the way the question is worded how many launched on the Shuttle and return on Soyuz. So it is five. Answer C.
All right there you have it. I hope you had fun playing. Check out that data set.
All kinds of fun things you can discover about all the astronauts and spacewalks and rockets that we're using to go to space. I want to thank you for playing. This is Bill Jelen from MrExcel.com
