Lab 4

As with past labs, call me over when you're done so that I can mark down that you were here. If time runs out and you have to go somewhere (e.g., another class) then please email me your lab/progress.

Feel free to ask questions (ask a neighbor or a friend or me) if you're stuck or having a hard time.

A Near-Earth Object (NEO) is an object in our solar system, whose orbit brings it close to Earth's orbit. In other words, it could maybe, conceivably crash into us.

Naturally, lots of governments spend lots of money trying to track these things, in order to determine the risk of impact and how to best handle a likely impact. Hollywood also spends lots of money convincing us that this threat is real and that we would worry about it (for example, in the films Armageddon and Deep Impact).

But how real is this risk and what are the general properties of these Near Earth Objects? Should we be worried? Let's open up Excel and find out.

NASA has a site that lists "Current Impact Risks [of NEOs]." Go there now and look at their table labeled "Recently Observed Objects (within past 60 days)." Also look at the table labeled "Objects Not Recently Observed." There are a few columns there that are pretty scientific and complicated, we'll be ignoring those. Instead, take a glance at the columns labeled "Year Range", "Impact Probability", and "Est. (Estimated) Diameter."

The year range represents the years in which an impact might occur. An impact can only occur in certain years because the object's orbit may take it very far away from the Earth at some points in time, and very close at other points. The impact probability represents the probability that it'll hit earth (and/or burn up in Earth's atmosphere). The estimated diameter is how large the object is thought to be.

A quick lesson on scientific notation: the probabilities are given in a form like 1.1e-05. The "e" just tells you that you should shift the decimal place around. In this case, it should be shifted left 5 places (because of the -5). We might read this probability as .000011 or that there is an 11 in 1,000,000 chance of the object hitting Earth.

When I was preparing this lab, I encountered problems with simply cutting and pasting the data into Excel. I've therefore made a table with the data. You should download the table from this place here, save it to your desktop, and open it up in Excel.

Now, we want to delete the columns that we don't care about. You can click on a column heading (e.g., "A") to select a whole column, then right click and choose the proper option. Specifically, you should be left with only the year range, impact probability, and diameter columns.

We can now use Excel to better understand these objects. Depending on how Excel decides to format the numbers, we may wish to format them in another way. For example, we could choose to show several decimal places.

Now, at the top of the work sheet (you will have to insert a blank row) find the average diameter of the NEOs. Your answer will be in kilometers, since that is what the data give us.

We might prefer to instead understand the diameter of each NEO in terms of miles. Add a new column, and in the column insert a formula to take the diameter (km) and convert it to miles. 1 km = 0.621371192 mi. So you should do something like:

 miCell=kmCell*0.621371192

Then, click and drag to copy this formula all the way down, so that we can view all of the objects' diameters in miles. Add an average diameter in miles cell to the top of the worksheet, just like how we can see the average diameter in kilometers.

Now, some probability theory: If we want to know whether at least 1 event out of many will happen then we can do this by first finding out the probability that NONE of the events happen.

As an example, if we have an event E, which has a 0.25 probability of occurring, then the probability of E not occuring is 1 - 0.25 = 0.75. Knowing how likely it is that each event will NOT happen, we can then find out how likely it is that NONE of the many events happen. So in other words, we want to know the probability that E doesn't happen AND that E2 doesn't happen AND that E3 doesn't happen, etc. This is simply given by:

 Prob(None happen) = Prob(E1 doesn't happen)*Prob(E2 doesn't happen)*Prob(E3 doesn't happen) ...

If we want to find out the probability that at least one of them happens, we then can find 1 - Prob(None happen).

You should now add a formula at the top of the worksheet that shows how likely it is that AT LEAST one of the NEOs hits Earth. If you can do this in only 1 formula, go ahead and do it. If you want to break this process up into several steps (perhaps by adding some new columns to the worksheet) then go ahead and do that. If you're confused, here are the steps you could take:

1. Add a new column, called "Probability of Not Hitting Earth"
2. Insert a new formula in the first row of data that is equal to 1-[the cell that contains the probability of the object for that row hitting Earth]
3. Click and drag to copy this down to the other objects. Make sure their formulas/values seem correct.
4. You've now found the probability that the object in that row will not hit Earth.
5. At the top of the data and maybe off to the side, make a cell whose value is the result of multiplying ALL of the pieces of "Probability of Not Hitting Earth" data for each NEO together. Hint: use the PRODUCT function to multiply a range of cells. E.g., =PRODUDCT(A1:A999)
6. At the top, directly above the column containing the original probability of the NEO objects hitting Earth, on the same row as the average cells you previously calculated, make a cell's value =1-[cell that had probability of not hitting Earth in it]
7. There, you have now calculated the probability that at least 1 of the objects hits Earth (assuming that my understanding of probability is okay, and that I didn't make a mistake).

Now, go ahead and apply formatting to the probability that at least one of the objects hits Earth cell. It should display at least 5 decimal places. Ask yourself, is this a large probability? Should we be concerned that one of the objects will hit us?

Hollywood might tell us though, that a HUGE object could appear and hit us. Maybe this is true. Looking at the average diameter of all the known objects though, we see that the average is 0.08km. But, that's merely the average. The average could be .08 from, for example, many objects whose size is exactly 0.08 or maybe it's from many objects which are either VERY small or VERY large, and whose average therefore is 0.08. That's where standard deviation can come in to help us understand the distribution of the diameters.

We'll assume that the sizes of NEOs follow a normal distribution. If you don't know what a normal distribution is, that's fine. Just click this link here and look at the picture and just notice how there's a peak and then each side kind of gently decreases.

At the top of your worksheet, near your averages and your probability of at least one object hitting Earth, add a cell which is equal to the standard deviation of all the objects' diameters. Remember that the standard deviation function is =STDEV.

With normal distributions, because things are kind of clumped into the middle, we can make predictions about the sizes of the objects, even ones we haven't seen before. Again, find some blank space near the top (or make some if you need to) and add the following cells:

You should create the formulas to automatically calculate those numbers (e.g., use the average diameter cell and the standard deviation cell). The number that are hand typed in (0.002, 0.159, 0.023) are exact numbers and are simply properties of all normal distributions, so, you won't need a formula to generate them.

We see that there is a 0.002 (0.2%) chance of an object being bigger than [whatever the average diameter + 3 * standard deviations is].

In another column, convert these sizes into miles. Should we be worried? Is this large?

Now, imagine that you work for NASA and you need to update this list often. President Obama has asked that alert him if the probability of at least one of the objects hitting Earth is greater than 0.01. In other words, he wants to know if there is a bigger than 1% chance of some object hitting Earth. If so, he should be told. Go ahead and add a cell to the top of your spreadsheet. The cell should look at the probability of at least one of the objects hitting Earth. If the probability is >= 0.01 the cell should say, "Call the President!" otherwise it should say nothing. Give the cell bold red text so that it'll be noticed (hint: use Excel's IF capabilities). Probably it will say nothing, but you never know… (we're assuming that regularly you paste new data into the worksheet, as Nasa tracks new NEOs/updates properties about known NEOs).

Once you've got all these numbers, format them to make them slightly more pleasing to the eye, then call me over and you're done! Regardless of the probabilities and sizes of these things hitting Earth, you should know that almost ALL of the objects have a Torino Scale of 0 (we erased the Torino Scale column), which in plain English means that it has "no likely consequences."

Have a good Valentine's Day weekend!