Will Bennu Impact Earth? Excel Hash - Podcast 2141

Will Asteroid Bennu strike the Earth in the 2182-2198 time frame? Using Microsoft Excel to model Bennu's path 10 million times. Even after OSIRIS-REx, we might learn that we are at greater risk than we think now.
This is my entry in Oz do Soleil's #ExcelHash competition.
Vote for your favorite here: Microsoft Forms
The entire playlist is here: Excel Hash 2018

Table of Contents of Video
(0:00) Introduction and Welcome
(0:08) About Excel Hash game
(1:00) Excel Technique: 3D Models in Excel
(1:25) Excel Technique: Rotating 3D Models
(1:35) Science: Bennu Spinning Wildly
(1:51) Science: What does Bennu Eventually hit?
(2:15) Joke: JPL Travel Poster
(2:25) Science: Bennu Close-Pass Dates
(2:50) Science: How small is 1 in 2700?
(3:07) Science: What happens if Bennu hits Earth
(3:25) Science: Impact: 3 Miles Out
(3:40) Science: Impact: 30 Miles Out
(3:54) Joke: Standing on a Corner Winslow Arizona
(4:02) Science: Impact: 300 Miles Out
(4:12) Science: Impact: Brooklyn
(4:40) Science: Who is Ivan Yarkovsky
(4:50) Science: What is Yarkovsky Effect
(5:21) Science: Yarkovsky Effect on Bennu
(5:30) Science: Can Yarkovsky matter?
(5:45) Math: What is "Accumulates Quadratically"
(6:05) Excel: VLOOKUP to named range
(6:19) Joke: Freaking large amount
(6:26) Shout out: Davide Farnocchia at JPL
(6:38) Science: Yarkovsky compounds
(7:06) Science: Uncertainty of Bennu 2135
(7:10) Excel: Bubble Chart
(7:58) Shout out: Dante Lauretta
(8:04) Science: OSIRIS-REx spacecraft
(8:08) Science: OSIRIS-REx OTES camera
(8:10) Shout Out: Lockheed Martin
(8:16) Joke: Otis Redding vs OTES
(8:20) Space: Launch Photos
(8:28) Science: First Photo of Bennu from OSIRIS-REx
(8:42) Science: Current Yarkovsky Estimates based on photo
from 2.2 million miles away
(8:50) Science: view from 3900 Feet above Bennu
(8:52) Shout-out: Michael Moreau from NASA Goddard
(9:17) Science: Chesley theorizes 25 times improvement in estimate after OSIRIS-REx
(9:26) Science: When will estimates improve (Lauretta quote)
(9:28) Cool: Thermal images of Danta Lauretta
(10:02) Disclaimer: Don't Trust My Science!
(10:22) Science: Chesley's table of Bennu 2135 Keyholes
(10:43) Excel: Scatter Chart
(11:00) Math: Standard Normal Curve
(11:22) Excel: Converting Chesley table to Lookup Table
(12:02) Excel: Where Bennu Hits using NORM dot INV and RAND
(12:24) Excel: Recalc with F9
(12:29) Excel: Grouping Objects
(12:32) Excel: VLOOKUP - Range Version
(12:50) Excel: F5 to Select 100K rows with Shift
(13:01) Excel: Ctrl+Backspace to bring active cell into view
(13:04) Excel: Ctrl+D to copy down
(13:15) Math: Result of 100K trials
(13:22) Math: 1 in 2778 chance of impact
(13:26) Excel: Custom Formatting for 1 in 2778
(13:40) Science: The model is coincidentally close to Chesley
(13:58) Excel: Data Table to Run Model 100 times
(14:30) Excel: Calculate Automatic Except for Data Tables
(14:42) Excel: MAX function
(15:03) Excel: Cutting so you can Copy
(15:14) Excel: FREQUENCY Function
(15:32) Excel: Function Arguments dialog
(15:48) Excel: Ctrl+Shift+Enter from Function Arguments
(16:08) Excel: IFERROR for DIV/0 error
(16:18) Science: Does OSIRIS-REx make us safer?
(16:26) Excel: Adding Scrollbar Form Controls
(16:34) Excel: Hold down Alt key to fit to cells
(17:13) science: what if 25 times more accurate?
(17:50) Science: Holy Fireball! 1 in 813 Chance of Impact
(18:00) Psychotherapy: Coming to grips with results
(18:08) Science: Adjusting the Mean
(18:28) Science: Zero Chance of Bennu Impact
(18:34) Joke: Hollywood Oil Rig Drillers Not Needed
(18:41) Wrap-up: Wait until 2123 for answer
(19:02) Shout-out: Thanks to JPL & OSIRIS-REx team
(19:12) Shout-Out: Thanks to Oz do Soleil
(19:20) Wrap Up: Watch the other videos
(19:30) Wrap Up: Vote
(19:45) Survey QR Code

Transcript of the video:

Learn Excel from MrExcel Podcast Episode 2241 - Excel Hash Will Bennu impact the Earth? Hey welcome back to the MrExcel netcast. I'm Bill Jelen Something completely different today. A tip of the hat to Oz do Soleil for this great idea.
He challenged six of your favorite Excel YouTube Creators to a game called "Excel Hash", right?
We signed up and collected ideas of ingredients that must be used in our solution.
Then used RANDBETWEEN. An so all six of us have to post a video, today Where we have to use: a 3D model, a Form control, the FREQUENCY function and MAX.
Six competitors. All in a playlist. You, the viewers could vote for your favorite. Sounds like fun!
The goal of Excel Hash is to take some random Excel elements and make something from them.
So, in the course of me using these 4 elements, here is what we are going to learn today.
Our first ingredient today is the 3D Model. I love this 3D model. Whenever I do my Excel seminars and I show Insert, 3D Models, the example that I do in the seminar is Bennu!
Alright. So Excel accepts any of these: FBX, OBJ, 3MF, PLY, STL, GLB. You have to find one of those files.
I had one of those here.
Insert. You get an object that we can resize.
but not just rotate like this. We can do amazing rotations using this symbol.
Now I can rotate Bennu in all dimensions. What is Bennu?
Bennu is an asteroid. It is 0.3 miles across (that is 484 meters) and it is trapped in orbit around the Sun. It goes around the Sun every 1.2 years.
It is spinning wildly.
A day on Bennu lasts 4.2 hours on Earth.
Astronomers discovered Bennu in 1999 when it passed 2.2 million miles away Earth.
Over the next three hundred million years, there is a 48% chance that Bennu falls into the Sun. 26% that it hits Venus. 10% that is escapes the Solar System (after close fly-by a Jupiter) 10% (uh-oh) that it hits us - the Earth. 3% Mercury. Less than 1% for Mars and Jupiter.
I love these free travel posters from Jet Propulsion Laboratory and NASA.
This one advertising Earth. But unfortunately - I wish they wouldn't have sent one of these to Bennu, because Bennu is planning on making several close approaches to Earth.
Astronomers have figured out these are the dates out when Bennu will be making a close pass.
That one in blue - and I love this one - They save it is going to be 300,000 km away, which will put it between the Earth and the moon. How wild will that be?
And then these ones in red … these ones in red are the dangerous ones.
Collectively, these dates in red - there is a 1 in 2700 chance that Bennu will impact the Earth.
That sounds small, but it's not comfortably small. If every commercial airline flight had a 1 in 2700 chance of crashing we would have 38 jetliners crashing every single day. It's kind of a big number.
Now what happens if Bennu hits the Earth? Does it kill every body? No!
We don't know where it is going to hit the Earth. This is Meteor Crater National Landmark in Arizona.
If - Bennu were to hit right in the middle of Meteor Crater (again). It would become 3 times as large, And three times as deep. This circle - people within three miles of the strike - the wind speed would be 3720 miles per hour.
All buildings will be knocked down. The stuff stuff ejected by the Bennu impact would be fifty feet deep.
Now let's go out to 30 miles. Here at 30 miles people will experience an air blast about 156 miles per hour.
Wood frame buildings would collapse and windows will be blown out.
and everything would be covered in 1/2 inch of dust.
If you happen to be standing on a corner in Winslow Arizona, It won't be such a fine place to be. Even the people on the outskirts of Flagstaff be impacted.
Now, out at a 300 mile radius, things won't be so bad.
You will actually hear the impact - it will sound like a truck passed by the window. And there will be a light coating of dust.
But what if Bennu doesn't want to visit the desert? It's tired of desolate areas and wants to go where the action is. So it heads to Brooklyn.
This is the 3 mile radius. Remember, all buildings in that circle are going to be toast.
30 miles: a majority of the Tri-State area - the windows will be blown out. This will be a massive natural disaster.
At the 300 mile range - people in Pittsburgh, Cleveland they are fine.
Who is Ivan Yarkovsky and why do we care? He was a Russian civil engineer 1844 - 1902. Two years before his death he wrote a pamphlet And he said, hey look, if there is a daily heating of a rotating object in space, it might experience a force that while tiny, could lead to significant long-term effects in the orbits of small bodies.
Now remember that Bennu is spinning fast. A day on Bennu lasts just 4.2 hours.
As each side of Bennu rapidly heats and then cools thermal radiation is causing a small but noticeable drift in the trajectory of Bennu.
Observations of Bennu from 2.2 million miles away estimate that the trajectory is moving about 282 to 286 meters per year due to this Yarkovsky effect. But hey how can this matter at all? It is 2018 now. In 2198, 180 years from now, times 282 or 286 meters… its 50 kilometers or 31 to 32 miles. It's not going to miss the Earth.
This term - "Accumulates quadratically" has been throwing me for a loop. All of the scientific papers, say that the Yarkovsky effect accumulates quadratically. And I've been trying to figure out what the heck "Accumulates quadratically" means.
Look, I am not a rocket scientist. I am not an astronomer.
I can do VLOOKUP with my eyes closed but I can't figure out the math behind these papers.
Well, here… let's do it with VLOOKUP! =VLOOKUP of "Accumulates Quadratically", into my science jargon table comma 2 comma False.
The uncertainty in the future is a freaking large amount. Well, why didn't you just say that!
A big shout out thanks to Davide Farnocchia at JPL for his e-mail that pointed me to the exact page in figure in Dr. Chesley's 2014 paper.
The issue is that this 282 to 286 meter per year compounds until the next near Earth pass. In 2054, that distance from here to there, will affect the future spin and trajectory of Bennu.
And then again six years later - 2060. Again, we have some uncertainty from 2054, add more Yarkovsky, and then even more variability and in 2080 and by the time we get to 2135, well the uncertainty band is a path 49,500 miles wide. And it is not just the distance uncertainty is it time uncertainty.
Think about it: the Earth is is whizzing by, the Moon is spinning around the Earth… that delta in time and the delta in width here can dramatically impact what happens in those future visits in 2186 through 2198.
The only reason they can say it is not going to hit us in 2135 is because the error is within these bounds and it can't hit the Moon or the Earth.
But after that, there's too many potential places where it can pass here and location of the Earth and location of the Moon, it's as scattershot for the other passes in 2186 through 2198.
Alright. We are progressing. The next question: who is Dante Lauretta?
This is Dante Lauretta, talking about his spacecraft, OSIRIS-REx Asteroid Sample Return Mission.
This is a picture of the spacecraft. (Thanks to Lockheed Martin Space.) There are a series of instruments on the side of the spacecraft including this one: the OSIRIS-REx Thermal Emission Spectrometer.
OSIRIS-REx launched on September 8, 2016 and has been catching up tp Bennu. It's going to arrive on December 3, 2018.
In late August 2018 this is the first picture that OSIRIS-REx took of Bennu.
it's about 2.2 million miles away - the same as ground-based observations were, and it's kind of shocking that the current Yarkovsky estimates are based on photos like this one.
Between now and December 3rd 2018, OSIRIS-REx is going to be able to improve from *this* photo to *this* photo.
Michael Moreau, Flight Dynamics Systems Manager at NASA's Goddard Space Flight Center, explains it like this: a commercial aircraft that is flying across the country's at 35,000 feet or about 10 kilometers.
OSIRIS-REx will be 1.2 kilometres above Bennu, taking pictures.
They are going to create a complete topographic map of this asteroid.
And again, back to Dr. Chesley and his 2014 paper.
He estimates the uncertainty, that 49,000 mile swath of uncertainty in 2135 might improve by a factor of 25.
I asked Dr. Lauretta when he expects the data to be back.
He says, while they are going to collect the data during 2019 (January through May and July through August) But they will actually probably have a final estimates until OSIRIS-REx is on its way back to Earth.
It's actually grabbing a bit of the surface so they can study it here on Earth.
and that's in 2021 through September 24, 2023 is when it arrives. So maybe out in the 2021 period, We might see some improved estimates. But here today for Excel Hash, let's see what we can generate in Excel.
Here's my disclaimer. Don't count on any of the science for the rest of this video. This is an Excel competition.
I am trying to blend the three remaining ingredients in Excel to come up with an entry in Excel game.
From here on out, don't believe any of my science. I will leave that in the capable hands of Dr. Chesley and Dr. Lauretta.
I'm not a rocket scientist. I'm just MrExcel.
So here's the model in Excel. We start with the paper from Dr. Chesley in 2014. He identified a series of 200 keyholes. If Bennu flies through this location in 2135, then there will be an impact with Earth in 2182-2196.
Some of these are really narrow. But some of these are really wide. This one is almost 20 kilometers wide.
Just to visualize, from the minimum location to the maximum location, I plotted all of these orange dots. I arbitrarily assigned them as wide or narrow.
So that we can see the variability there.
Bad science alert: in the interest of simplicity I have assumed that there is a standard normal distribution between the minimum location and the maximum location. That's completely wrong.
but I don't want this video to run 17 hours! So this blue curve is the probability. Here is Bennu. It is going to hit somewhere.
It's more likely hit in the middle than near the outside edges.
I am going to slow down a bit for this first real bit of Excel.
This is Dr. Chesley's table. This is the location and the width on either side of that. To build that into a proper VLOOKUP table, so I can use the Comma True version of VLOOKUP, I took each of these points and made it into two rows in Excel.
Start with the really small number: there is not impact there. The first impact occurs at 92981 minus half this width.
and so on all the way down. So we can predict depending on the location whether there's an impact or not.
So what were are going to do is to model where Bennu is in 2135, to determine whether there's an impact are not.
Now one important thing: it's not just RANDBETWEEN the min and the max.
It's the NORM.INV function. We send our RAND function in to that. And that determines the location where Bennu will hit.
371 in this particular case. We press calculate now and 561 - somewhere out here.
From there, we do a VLOOKUP into our table. Now this VLOOKUP is an approximate match, in other words, it does not need to find the exact number. It will tell us whether there's an impact.
Now - 1 in 2700, I would have to press calculate now [ up to ] 2700 times before I got the first hit.
So I am going to run this 100,000 times [ Press ] F5. I want to go to W100009. Hold down the shift key while you press OK.
That will select all the way down to that range. Control backspace to go to the top. Ctrl+D for [ copy ] down.
and we've now run the model 100,000 times, throwing Bennu in a different location on it's pass by Earth in 2135.
How many times does it impact? Well that's the sum of the 100,000 trials.
What's the chance of impact? it's 100,000 divided by the number of impacts.
and format that with Ctrl+1, Custom, "1 in "0 will get rid of all the decimals.
So look at that. Dr. Chesley said 1 in 2700, and this model - order of magnitude - is coming up with something close to that (which might just be dumb luck because remember the bad science alert).
Now, I would take this 100,000 trial model, re-run that 100 times. I am going to do that using the Data Table in What-If analysis.
So here's my number that takes the result of this model - the result of 100,000 calculations, and summarizes it down to one cell, I select 100 rows… Data, What-If Analysis, Data Table.
For the column input cell, because I have RAND, it can be any blank cell.
Click OK. And there are our results. This takes a long time to calculate.
Go to calculation options. Automatic except data tables. Which means that everything on the page will calculate except for this model here in gray.
What's the most number of times that it hits? There's our MAX function (one of the ingredients we had to use).
In this particular case, 65 was the highest.
I really like my statistic here with "a one in 2273" So I will cut with Ctrl+X to put it next to the result. Copy. Paste. And then Cut back. Perfect.
Time for the FREQUENCY function. Of the 100 runs of the 100,000-row model how many times did we get each of these bins?
Now, the FREQUENCY function is entered into one more cell than you have bins.
Use the function arguments. It wants to know the data array. The data array is these hundred results here, and the bins array are these bins.
In order to get this to work we have to press Ctrl+Shift+Enter.
It's really frustrating when I click OK they're going to press enter, so I click up here in the formula bar, Control+Shift+Enter. Boy, if there's a better way to do that I would love to hear it.
We have our frequency of how many times for each of this bins.
It looks like the most likely is somewhere between 46 and 50. Again, I like this statistic.
I am going to adjust it here, to deal with the division by zero error.
Now our question is: After OSIRIS-REx does this perfect topographical mapping and understands the Yarkovsky Effect, does that change these answers?
To model that, I'm going to add two form controls here. I'm going to use the scroll bars.
I love the scrollbars because they create these great little sliders.
I'll hold down the Alt key while I drag [ to draw ] the slider.
Right-click. Format Control. So for "Accuracy", I want to go from a minimum value of one, to 25 times better. Report that answer in this cell I10. Click OK.
Then copy this, paste. Right-click. Format control. This one is going to go from a minimum value of one to a maximum value of 100 and it can be tied to this cell. Click OK.
Dr. Chesley theorized that after OSIRIS-REx, the accuracy of the estimate - the uncertainty - would be reduced by a factor of 25. Let's go full on. There.
So now the possible location for the Bennu to hit is this very very tiny area right there.
It looks like the most likely occurrence before OSIRIS-REx was "1 in 2174".
We are going to run this model again, all 10 million iterations - Calculate Now and holy fireball - the odds of us getting hit are much much higher, provided it stays right here in the mean.
That's because are some really wide keyholes near the mean. Now… they improve the accuracy, which means how narrow it will be, But they're also going to a potentially learn the true new mean, which is where our second control comes in here.
This will shift us left or right. And so there are spots out here where there really just are not a lot of keyholes.
So if we'd learn that this is the location (and yes, I'm cherry picking,) and that's the location that Bennu's going to hit, Calculate now. And zero chance of hitting Earth at all. [ Phew! ] that is a great relief because now we won't have to go hire a bunch of Hollywood oil rig drillers to go blow up Bennu.
Maybe it doesn't hit there, maybe it's here. We will know all this by 2123 am really have a sense of whether we are in a lot of danger or hardly any danger at all. I love this model in Excel. we're doing - every calculate now - 10 million calculations.
It is an amazing tool for calculating the variability of this asteroid.
My sincere thanks to everyone on the OSIRIS-REx team who put up with my stupid non-science questions.
Again I want to thank Oz do Soleil for this great idea of making six Excel YouTube creators create an Excel model with these four crazy items.
Please watch others in the playlist this is going to be hilarious. I will put a link to the playlist in the YouTube description down there.
Then when you are done, vote for your favorites. Again a link down in the YouTube description.
We ask that you if you watched the video are rated from 1 to 5 points. If you did not watch a video, choose N/A. and then those averages will not be included.
to get to the survey, point your phone at this bar code and choose Open in browser.
Well, hey, I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.