An Interstellar Visitor?


Quote of the Day

In the days of my youth, the trickle-down theory was called the horse and sparrow idea of economics: If you feed a horse enough oats, some of it will go through the horse and fall on the road for the sparrow.

John Kenneth Galbraith (Post-Keynesian economist). I guess the common people are the sparrows? We get to pick through the manure?

Figure 1

Figure 1: Asteroid A/2017 U1. (Source)

Astronomers are now working on determining if a recently discovered asteroid is an interstellar visitor. The first observations of this asteroid were made by a team of researchers at the University of Hawaii's Pan-STARRS 1 telescope on Haleakala in Hawaii (Figure 1).

The astronomers were excited when they determined the orbital parameters of the asteroid and saw that its orbit has an eccentricity of 1.2, which means that it is on a hyperbolic trajectory through the solar system. Its estimated interstellar speed is 26.3 km/s, which is also known as the hyperbolic excess velocity. Figure 2 shows how to compute this value. The links (brown color) in Figure 2 are live and lead you to explanatory web pages.

Figure 2: Calculation of U1's Interstellar Velocity.

Figure 2: Calculation of U1's Interstellar Velocity.

Good Stack Exchange Discussion Good general discussion by principal Chat Group Discussion Mass of Sun Universal Gravitational Constant Standard Grav. Parameter Wikipedia Page on U1 Interstellar Velocity

The observations showed that it was likely a reddish, low albedo object probably similar to  D-type or P-type asteroids. The Wikipedia lists the absolute magnitude of asteroid U1 as 22.08. If we assume the albedo is low (~10%) like a D or P-type asteroid, we can estimate U1's equivalent diameter using the formula from this post to be 160 meters (Figure 3). The calculation in Figure 2 agrees with the value given in the Wikipedia.

Figure 2: Estimating the Size of Asteroid U1.

Figure 3: Estimating the Size of Asteroid U1.

The computed trajectories of asteroid A/2017 U1 show that it will pass in and out of our solar system (Figure 4).

Figure 3: Trajectory of Asteroid A/2017/ U1 Through Our Solar System.

Figure 4: Trajectory of Asteroid A/2017/ U1 Through Our Solar System. (Source)

Scientists have long speculated on the number of rogue bodies circulating between the stars – here is a list of candidate rogue planets.

One of my favorite science fiction books, When Worlds Collide, is a story written in the 1930s about a rogue planet being captured by our Sun. While the language used in the book is a bit dated, it is still popular enough that it remains in print. Hollywood made a B-movie that is loosely based on the book (Figure 5).

Figure 3: When Worlds Collide Movie Poster.

Figure 5: When Worlds Collide Movie Poster.

As an aside, my favorite science fiction book is After Worlds Collide, which is the sequel to When Worlds Collide.

Posted in Astronomy | Leave a comment

Estimating Asteroid Sizes Using Excel 2D Data Table


Quote of the Day

I do not give  performance guarantees because I provide the most flawed product known – people.

— Dave Neville, corporate recruiter


Figure 1: Asteroid 2011MD.

Figure 1: Asteroid 2011MD. (Source)

I recently gave a seminar on how to design products using Thermoelectric Coolers (TECs). During this presentation, I showed the audience how to generate various tables and graphs using Excel data tables. I was surprised to learn that no one in the audience had ever seen an Excel data table in action. Since the middle of a seminar on TECs is probably not the best time to divert to some Excel training, I decided to prepare a simpler example that would be easier to understand on first exposure.

My amateur astronomy activities soon provided me an excellent example. We often hear of Near-Earth Objects (NEOs) or asteroids  in the press (Figure 1). The sizes of these objects are estimated using a simple formula that is based on the objects brightness (i.e. absolute magnitude) and the percentage of incident light it reflects (i.e. albedo) – a perfect application for a data table.

Let's dig in …


You can skip the background material if you do not wish to know the details behind the formula used to create the data table.


While crawling around the Jet Propulsion Laboratory web site on solar system objects, I found a table of asteroid sizes versus absolute visual magnitude and albedo. In this post, I will duplicate that JPL table using an Excel's data table, rounding to two significant digits,  and custom format features. This example provides a useful vehicle for illustrating how to use an Excel for a typical scientific application – the evaluation of a two-variable function.


The following definitions are repeated from this earlier blog post that was focused on using Mathcad.

Apparent Magnitude (V)
The visual brightness of an asteroid when observed and measured visually or with a CCD camera. (Source)
Absolute Magnitude (H)
The visual magnitude of an asteroid if it were 1 AU from the Earth and 1 AU from the Sun and fully illuminated, i.e. at zero phase angle – a geometrically impossible situation. (Source)
Geometric Albedo (pV)
The ratio of the brightness of a planetary body, as viewed from the Sun, to that of a white, diffusely reflecting sphere of the same size and at the same distance. Zero for a perfect absorber and 1 for a perfect reflector. (Source)
I smiled when I read this description of geometric albedo – it is VERY similar to the description of target strength as used by the sonar community. I am always amazed at the similarity between the various engineering and scientific disciplines.
Bond Albedo (A)
The Bond albedo is the fraction of power in the total electromagnetic radiation incident on an astronomical body that is scattered back out into space. The Bond albedo is related to the geometric albedo by the expression A={{\rho }_{V}}\cdot q, where q is termed the phase integral. We will not be worrying about the Bond albedo for this post. (Source)
Equivalent Spherical Diameter
The equivalent spherical diameter of an irregularly shaped object is the diameter of a sphere of equivalent volume. (Source)

Asteroid Diameter Formula

JPL uses Equation 1 to estimate an asteroid's equivalent spherical diameter.

Eq. 1 \displaystyle D\left( {{{p}_{V}},H} \right)={{10}^{{0.5\cdot \left( {6.259-\text{log}\left( {{{p}_{V}}} \right)-0.4\cdot H} \right)}}}


  • D is the equivalent spherical diameter of the asteroid [km].
  • pV is geometric albedo [dimensionless].
  • H is absolute magnitude of the asteroid [dimensionless].

I have never seen Equation 1 before, however I have seen Equation 2 in a number of papers and websites (example), including the Minor Planet Center.

Eq. 2 \displaystyle D({{p}_{V}},H)=\frac{{1329}}{{\sqrt{{{{p}_{V}}}}}}\cdot {{10}^{{-0.2\cdot H}}}

The detailed derivation of Equation 2 is a bit involved, but quite interesting. See this document for details (section 4.2).

Equation 1 appears to be different from Equation 2, but simple algebraic manipulation shows it to be almost identical – only a minor difference in the leading coefficient.

Figure 2: Equivalence of Equations 1 and 2.

Figure 2: Equivalence of Equations 1 and 2.

For my work here, I will use the JPL formula (Equation 1).


The actual workbook is included here for those who wish to see the details. If you want a step-by-step guide to making a data table, see this video by Danny Rocks – a bit old, but I have always thought Danny gave the best tutorials.

Figure 2: Good Video Briefing on Spreadsheet Work.

The key points for my specific example (Figure 3) are:

  • The data table function will substitute all the albedo values in into the row input cell.
  • The data table function will substitute all the magnitude data (H) into the column input cell.
  • The function output goes into cell C16. I placed a reference to cell C16 in cell B18, which the data table function uses to obtain the table values.
  • I covered B18 with a couple of text boxes so I could label the row and column data.
  • As JPL did, I rounded the data to preserve 2 significant figures.
  • I used a custom number form ("[<1]0.####_0;[<10]0.0_0;0_0") to ensure the data table results were in the same format as used by JPL.
Figure 2: Excel Data Table Example.

Figure 3: My Excel Data Table Example.


I was able to duplicate JPL's table exactly using a data table, a custom format, and rounding to two significant digits.

Appendix A: JPL Asteroid Diameter vs Mag. and Albedo

Here is the raw JPL table that I duplicated in Figure 3.

Figure M: JPL Asteroid Diameter vs Absolute Magnitude and Albedo.

Figure 4: JPL Asteroid Diameter vs Absolute Magnitude and Geometric Albedo.

Posted in Astronomy, Excel | 1 Comment

Great Lakes Shipwreck Statistics


Quote of the Day

Math is an under-appreciated virtue in politics.

Steve Schmidt, Republican political consultant

Figure 1: Great Lakes Freighter Edmund Fitzgerald, which sank 10-Nov-1975. (Source)

Figure 1: Edmund Fitzgerald, which sank on 10-Nov-1975. (Source)

I was driving home from my cabin construction project yesterday when I heard on the radio Gordon Lightfoot singing about the sinking of the Great Lakes freighter Edmund Fitzgerald (Figure 1). The sinking occurred when I was in high-school, and it was the first time I had ever heard of a sinking on Lake Superior.

Hearing the song got me thinking about shipwrecks on the Great Lakes. I decided to download the shipwreck data and create some pivot tables – my Excel workbook is here.

The number of shipwrecks recorded for all the Great Lakes is shown in Figure 2. Notice how Lake Erie has the greatest number of wrecks by far.

Figure 2: Wrecks By Great Lake.

Figure 2: Wrecks By Great Lake.

Figure 3 shows the 11 most recent shipwrecks, which go back to 1953.

Figure 2: 11 Most Recent Great Lakes Shipwrecks.

Figure 2: 11 Most Recent Great Lakes Shipwrecks.

In recent years, shipwrecks have become much less common (e.g. zero in the 1980s). While the rate of shipwrecks peaked from 1909 to 1909 (yellow highlight), you can see that the rate reduced greatly starting in the 1930s.

Figure 3: Histogram of Shipwreck Dates by Decade.

Figure 3: Histogram of Shipwreck Dates by Decade.

Lake Superior is the only one of the Great Lakes that I have boated on. It is a beautiful lake surrounded by wonderful land forms (e.g. Figure 4). I have friends who chose to build their retirement homes on its shores because it is so beautiful there.

Figure 4: Split Rock Lighthouse on Lake Superior. (Source)

Figure 4: Split Rock Lighthouse on Lake Superior. (Source)

Posted in History Through Spreadsheets | Leave a comment

A Math Error I Wish Were True


Quote of the Day

Because Bushwacker, PBR Bucking Bull just isn't his spirit animal. His spirit animal is more of that of a millennial who works at his dad's law office after graduating with a bachelor of business in 6 short years... unmotivated, lazy and slightly entitled...

— Advertisement for the sale of a bucking bull named Rudy that won't buck.

I checked my company's stock price today on Google. Something doesn't seem correct – I think there is a math error here 🙂



Posted in Humor | Leave a comment

Santa Rosa Fires And My Coworkers


Quote of the Day

Egocentrics are attracted to the inept. It gives them one more excuse for patting themselves on the back.

Helen Hayes, actress

Figure 1: Barn on fire in Santa Rose, California. (Kent Porter/The Press Democrat)

I work for a company that is headquartered in Petaluma, California, which is just south of the Santa Rosa fire region. Six employees lost their homes in this fire. One employee, Danny Pomplun, narrowly escaped the fire by sheltering in their pool. Their dramatic story is told very well in this Washington Post article. If you cannot gain access to the original news article, try this PDF of story.

Danny and his wife were fortunate -- they barely got out with their lives.





Posted in Personal | Leave a comment

Thermoelectric Cooler Calculation Example


Quote of the Day

You give it power.

Marilyn Vos Savant, her answer to the question 'How do determine if something is good or evil?' Unfortunately, this is a very dangerous test to perform in practice. Germany inadvertently applied this test with the Reichstag Fire Decree on February 4, 1933. This decree suspended civil rights so that Hitler could deal with a perceived terrorism problem. Hitler only relinquished power on his death at the end of WW2 in 1945.

Figure 1: Block Diagram of a Thermoelectric (TE) Cooler. (Source)

Figure 1: Block Diagram of a Thermoelectric Cooler. (Source)

My team is working hard on developing  Dense Wavelength Division Multiplexing (DWDM) optical systems for communications applications. These systems put multiple wavelengths on a single fiber,  and they provide our customers the opportunity to deliver significantly more bandwidth without needing to additional fiber optic cables. To ensure reliable communication, each laser is is assigned a specific wavelength range. It must not leave that range, otherwise it will interfere with another communication channel.

The assigned wavelength ranges are ~0.8 nm wide. Because the wavelength of a semiconductor laser varies by 0.1 nanometer (nm) per °C,  this means we must control the laser's temperature to within ±4 °C of an assigned value.  We normally control the laser temperature using a device called a Thermoelectric Cooler (TEC), which depends on the Peltier effect to provide refrigeration.

Most electrical engineers do not have much experience designing with TECs, and I need to put together some training material for our newbie design engineers. Fortunately, I found the following two articles were provided excellent background on designing with these devices.

The only problem with the design example is that there was a typo in one of the formulas and one of the matrices, and no worksheet implementation was provided. I marked up the design example and I provide a working spreadsheet implementation (Excel)  of the matrix-based solution here. The spreadsheet implementation is interesting because it demonstrates the use of matrix mathematics and data tables in Excel.

For those who prefer to see an algebraic approach, I include that here implemented in Mathcad 15 and PDF. As you can see in the algebra, there is an solution to TEC equations, but it is rather long (Figure 3).

Figure 3: Algebraic Solution to Simon's Model.

Figure 3: Algebraic Solution to Simon's Model.

I will be training my staff using this material in the coming weeks. As an example of the modeling output, Figures 3 and 4 show the design charts that the model generates.

Figure 2: Air Temperature into Electronics versus TEC Current.

Figure 3: Air Temperature into Electronics versus TEC Current.

Figure 3: Air Temperature into Electronics versus Electronics Power Dissipation.

Figure 4: Air Temperature into Electronics versus Electronics Power Dissipation.


Posted in Electronics, Excel, optics | 1 Comment

Torpedo Engine Technology for a Venus Space Probe?


Quote of the Day

Nothing great in the world has been accomplished without passion.

Georg Wilhelm Friedrich Hegel, philosopher. I know people who have gone their entire lives without finding a passion. I am fortunate that I found mine early on.

Figure 1: Current US Navy Torpedoes.

Figure 1: Current US Navy Torpedoes. (Source)

I just finished reading an interesting article on a NASA proposal for a Venus space probe that uses power generation technology developed for a US Navy torpedo program back in the 1980s. Like many spacecraft, torpedoes need power generation systems that are small, generate massive power for a short period, and must be storable for years with the ability to turn on almost instantly with high reliability.

Back in the 1980s, the US Navy faced the threat of deep diving, high-speed Alfa-class submarines operated by the Soviet Navy. Standard torpedo power systems at that time either used seawater batteries (eg. Royal Navy Sting Ray) or Otto-fuel driven internal combustion engines (e.g Torpedo MK 48). Both of these technologies have their advantages and disadvantages.

  • Seawater Battery
    • Advantages: electrical propulsion is quiet, very reliable, no exhaust to remove, storable long-term
    • Disadvantages: low energy density
  • Otto-Fuel
    • Advantages: high energy density, storable long-term
    • Disadvantages: noisy, exhaust must be remove

The need to remove engine exhaust is a problem for a deep-diving torpedo. As the torpedo goes deeper, the engine must work against the back pressure of the ocean in order to remove the exhaust. This means that an Otto-fueled torpedo goes slower as it goes deeper. This creates an intrinsic limit on the operating depth of these torpedoes. The Alfa-class submarine could dive so deep that it was possible for it to go below the operating depth of many torpedoes.

The US Navy's solution was developed by the University of Pennsylvania in the form of the Stored Chemical Energy Propulsion System (SCEPS). The SCEPS engine uses the chemical reaction between lithium (fuel) and sulfur hexafluoride (oxidizer) to generate heat that can drive a boiler and turbine (Equation 1).

Eq. 1 \displaystyle 8\ \text{Li}\ \text{+}\ \text{S}{{\text{F}}_{6}}\to \text{6}\ \text{LiF}\ \text{+}\ \text{L}{{\text{i}}_{\text{2}}}\text{S}\ \text{+}\ \text{heat}

A key advantage of this chemical reaction is that its reaction products occupy less volume than the input fuel and oxidizer.

Historically, NASA would have used plutonium-238 thermelectric batteries for its space applications. However, the US has lost its plutonium-238 production capacity (article, article) and is now going through multi-year process to rebuild that capacity. While this rebuilding is going on, the SCEPS engine may provide a useful alternative power source.

This is not the first time torpedo technology has been used in spacecraft. For example, a friend of mine managed a thermal battery design group that built the batteries for the Galileo Probe that descended into Jupiter's atmosphere. Very similar batteries have been used in torpedoes for decades because they can be stored for years, generate tremendous power for a short period of time, and are very reliable.

I should mention that the SCEPS engine ended up not being deployed on any active US Navy torpedo because of issues that are not relevant to a space deployment.

For those of you interested in the history of US torpedo development, Figure 2 is excellent (large photo, ~6 MB).

Figure 2: Pictorial History of US Torpedo Development.



Posted in Naval History, Space | Leave a comment

One Drink Per Hour Can Get You Drunk


Quote of the Day

Your time is limited, so don't waste it living someone else's life.

— Steve Jobs

Figure 1: Example BAC Chart. (Source)

Figure 1: Example BAC Chart. (Source)

Back in 2011, I wrote a blog post that goes into the details on how the Blood Alcohol Content (BAC) versus drinks/time/gender are computed (Figure 1). These charts tell people how much they can drink and still stay under the BAC limits for driving under the influence. These charts are for a typical drinker and the actual BAC value will vary by person.

A reader, Megan Sweeny, wrote a very interesting comment to my original post that included a link to a  Google sheet she created that applied the Widmark formula to a person that drinks at a fixed rate to investigate the validity of the following rule of thumb:

Your BAC level will remain within safe limits if you consume only one standard drink per hour.

The basis of most BAC charts is the Widmark formula. Megan used the Widmark formula to test the rule of the thumb mathematically. Her approach was to:

  • Assume the person takes a drink at the top to the hour (1:00, 2:00, 3:00, etc).
  • Compute the BAC one hour after consumption.
  • Draw charts to illustrate how the drinker's BAC varies with time.

Megan's model show that a 150 lb male following the rule of thumb eventually will exceed the legal limit. Since I use this blog as a repository for my Excel work, I have made an Excel version of Megan's work that you can access here.

From my standpoint, no one should ever drive after drinking. However, this was an excellent use of spreadsheet to model a common rule-of-thumb.

Nice work Megan.




Posted in General Mathematics, Health | 3 Comments

Countries That Are Graying Fastest


Quote of the Day

To see what is in front of one’s nose needs a constant struggle.

— George Orwell

Figure 1: UN Projections on Median Country Ages By 2050.

Figure 1: UN Projections on Median Country Ages By 2050. (Source)

Aging is something that many US firms are needing to address because of the tidal wave of baby boomer retirements (blog post). As a director in a high-tech firm, I have been working hard to address the need for passing knowledge from workers near retirement age to younger workers (blog post). However, aging is not just a problem for companies as the median age of the world is increasing. I have been reading quite a few articles lately on the effect of aging populations on quite a few developed nations: South Korea, China, Germany, and Italy. These nations are facing the problems associated with aging populations: high medical expenses, providing elder care, paying pensions, etc.

Figure 1 shows data from the UN on how they project how the median ages will change by 2050 for some selected countries. This chart made me curious as to how the median age is changing per year in some countries form which I buy components. I grabbed median age data from the CIA World Factbook for the years from 2004 to 2016 (biannual data), did a least‑squared fit on the data to determine the rate of aging, and filtered the data for the countries in which I was interested.

Figure 2 shows my selection of developed nations ordered by their rates of aging. One country that is is important to the optics business is South Korea, which is the fastest aging developed country in the world. The sparklines show the data for which the slopes were calculated.

Figure 2: Countries on My List Ranked by Rates of Aging.

Figure 2: Countries on My List Ranked by Rates of Aging.

Not all countries are aging. Figure 3 shows the top ten countries in terms of growing younger.

Figure 3: Countries with Declining Median Ages.

Figure 3: Countries with Declining Median Ages.

For those who are interested, my workbook and data files are here. No macros here – just Excel and Power Query.







Posted in History Through Spreadsheets | Leave a comment

Old Naval Mines Still Floating Around


Quote of the Day

There is greatness hidden in every talent, career, or skill be it photography, designing, art, music, and many more. But it can only be realized by people who decide on a certain day at a certain time to take their passions further.

Israelmore Ayivor

Figure 1: Old Training Mine Washes Up on North Carolina Beach.

Figure 1: Old Training Mine Washes Up on North Carolina Beach. (Source)

I just read a news article about an old moored training mine washing up on a North Carolina beach (Figure 1). I am amazed at the number of mines that still wash up on beaches around the world. Figure 1 shows the mine that washed up on a North Carolina beach recently. Stories like this seem to happen frequently after severe storms. Fortunately, this training unit (i.e. inert) did not pose a hazard to the people who came upon it, but some explosive-laden mines still wash up on beaches (example).

Figure 2: Moored Mines As Portrayed in WW2 Movies.

Figure 2: Moored Mines As Portrayed in the Movie Destination Tokyo.  (Source)

This particular mine is similar to the type often portrayed in WW2 submarine movies (Figure 2). It looks like a floating metal sphere covered in "horns" that is moored to the ocean bottom. The horns are called Hertz horns (sometimes spelled Herz) and actually are a form of electric battery. When the horns are bent, such as when a ship collides with the mine, a vial containing sulfuric acid breaks, releasing the acid into a dry lead-acid battery (Figure 3). With the battery now filled with electrolyte, it begins generating a voltage which can be used to fire a detonator.

Figure 3: Schematic of a Hertz Horn.

Figure 3: Schematic of a Hertz Horn. (Source)

This type of detonator can remain functional for decades. The Hertz horns were invented in 1868, which give you an idea of how old naval mine technology is. It is estimated that 235,000 sea mines were laid during WW2 and some missed being cleared. While I consider this type of mine obsolete, they are still commonly used. Modern mines are much more capable, in some cases even being able to fire torpedoes (e.g US Navy Captor). They have onboard sonar systems that can track ships as they approach.

The mines washing up on beaches have been floating free in the ocean for various reasons. During WW2, minesweepers used paravanes (i.e. towed underwater gliders) to cut the mooring cables of the mines. With their mooring lines cut, the mines would float to the surface and a sailor would shoot a rifle at the mine. Their plan was for the bullets to either hit the Hertz horn and detonate the mine, or to put so many bullet holes in the mine that it would sink. Some floating mines were missed during this process and simply floated off to become a hazard later on. Other mines were missed and never had their mooring lines cut – eventually the action of wind, waves, and corrosion would break the mine free. Also, these old mines have been laid as part of other conflicts, like the Korean War, and in the Persian Gulf to impede oil shipments. These mines have also ended up floating in the ocean.

Naval weapons are very powerful and even old ones pose a serious safety threat because explosives become unstable with age. One of these mines washed up on a beach and was mistaken for an old buoy– children were playing by it. It makes me shudder just thinking of it (Figure 4). Part of my concern for these kids is driven by a close call of my own involving an experimental torpedo. It is the only time in my life that I hoped my wife would be able to find my dental records.

Figure 4: Unidentified Sea Mine Near Children.

Figure 4: Unidentified Sea Mine Near Children. (Source)

Posted in Military History | Leave a comment