Time for a Job Change


Quote of the Day

Be kind whenever possible. It is always possible.

— Dalai Lama

Figure 1: I have decided that it is time for a new job. (Source)

Figure 1: I have decided that it is
time for a new job.

My company is changing its approach to hardware development, and after much soul-searching, I have decided to volunteer for layoff. I do not have any immediate plans – it is just time for a change. I will continue to write on technical topics because math, electronics, and software are in my blood.

I fell in love with electronics when I was five-years-old after my neighbor showed me a schematic. I thought it was some weird language that I needed to figure out. My first projects involved building kits from Radio Shack, Heathkit, and Lafayette Radio that I paid for using my income as a paperboy. I also started doing circuit-based science fair projects. It all culminated with me studying electrical engineering and working in this field for my entire career. Even after these many years, my passion for technology of all sorts continues unabated.

If you know of any jobs that would be appropriate for someone like me, please leave me a note here or on my linkedin page. My wife's job is covering my benefits, so either a contract or employee position would work.

Figure 2: Interociter schematic from the movie This Island Earth. This schematic also interested me as aboy. I loved that movie. (Source)

Figure 2: Interociter schematic from the movie This
Island Earth
. This schematic also interested me as a
boy. I loved that movie. (Source)

Posted in Personal | 6 Comments

Smallest Rocket to Put Payload Into Earth Orbit


Quote of the Day

Our educational system is like an automobile which has strong rear lights, brightly illuminating the past. But looking forward, things are barely discernible.

Hermann Oberth, German rocket theoretician, describing the German education system in the 1920s. He was bitter because his doctoral thesis on rocket propulsion was deemed utopian. His work became the basis of all spaceflight today. In my opinion, his criticism of the German educational system could be applied to the US education system today.

Figure 1: Japan's SS-520 rocket. It is reported to be the smallest rocket capable of putting a payload into orbit. (Source)

Figure 1: Japan's SS-520 rocket. It is reported to
be the smallest rocket capable of putting a
payload into orbit. (Source)

I just read a news article about Japan launching a 3 kg satellite into orbit using a 9.7-meter-long, two-stage rocket called the SS-520 (Figure 1). The 9.7 meter length was interesting to me because I recalled an Air & Space magazine article from 1999 that stated that the smallest rocket capable of achieving Earth orbit would be "about 30 feet long." Since 9.7 meters is 31.8 feet long, it appears that Japan's SS-520 is very near the lower size limit for rocket that can put an object into Earth orbit.

The size limit for an orbital rocket is driven by the amount of momentum lost because of atmospheric drag. As with artillery projectiles, larger rockets are more efficient in retaining momentum against drag. For a given shape, larger rockets are more aerodynamically efficient because frontal area increases by the square of the linear dimensions and volume (and mass) scales by the cube of the linear dimensions (see this detailed discussion). Drag is a function of the frontal area of the rocket, thus larger rockets have more mass (and momentum) relative to their drag. Another challenge with implementing a small launch vehicle is the difficulty of efficiently implementing a high specific impulse, liquid-fuel system because of the overhead of all the pumps, plumbing, and cooling.

Because I am still tied up with my cabin project, I have not gone through the minimum-sized orbital rocket calculations myself. Air & Space magazine states that:

A terrestrial rocket has to push through a plug of air equivalent to a 30-foot column of water, and physics dictates that the smallest vehicle capable of moving all that atmospheric mass without paying a penalty in momentum is about 30 feet long.

Historically, the orbital launch market has been dominate by customers who want to put large payloads into space. The advent of CubeSats has created a market for these small rockets. For example, a company called  Rocket Lab uses their Electron rocket to launch small groups of CubeSats.

NASA has been researching the smallest rocket that can return a sample from Mars to Earth. According the Air & Space magazine article, the smallest orbital rocket is "about the size of a pencil" for essentially zero payload. NASA's Mars return mission is targeting a 1 pound payload and the mass is about 170 kg. Having lower gravity and a much thinner atmosphere make the job of getting into Mars orbit much easier than getting into Earth orbit.

People have been discussing these small rockets for many years. In fact, people have tried to motivate innovation in this area with the N-Prize, which is focused on putting a small payload (10 - 20 grams) into Earth orbit for less than 1000 £ . For an excellent discussion on micro-rocketry, see this forum thread. The following Google talk on microlaunchers is also useful.

Figure 2: Microlaunchers – The Case for a New Generation of Very Small Spacecraft.
Posted in Astronomy, Space | 5 Comments

Electrical Conduit Math


Quote of the Day

A leader is a dealer in hope.

— Napoleon Bonaparte

Figure 2: My Garage at Night.

Figure 1: My Garage at Night.

As I have mentioned in other posts, I am building a large garage in northern Minnesota (Figure 1). I would show you some pictures of the interior, but I have promised my son that I will not post anything that could ruin his surprise when he sees it in April. As part of this construction effort, I am using quite a bit of electrical conduit. Conduit consists of metal pipes (often called EMT) through which the wires pass and it must be bent to go around any barriers it encounters. Conduit is a very efficient way to wire a working area because it directly attaches to the wall and does not require opening holes in drywall and repairing the damage. Conduit can also be updated and modified easily by running new/additional wires through it.

Figure 1: 4 Point Saddle Bend Around An Obstacle.

Figure 2: 4-Point Saddle Bend Around An
Obstacle. (Source)

I am going to review the process for running conduit around an obstacle using a 4-point saddle bend, which entails bending the conduit into a trapezoidal shape for passing around the obstacle (Figure 1). Electrical handbooks contain tables that tell electricians how to measure along the conduit so that the bend will go around an object of a given depth. In this post, I will provide simple formulas for this bend and will use these formulas to regenerate a commonly seen table for conduit bending.

For those who like to follow along, my worksheet is here.


Reference Article

In this post, I will duplicate a conduit bending table that I saw in this excellent reference article. The table is shown in Figure 3, which has units of degrees for angles and inches for length.

Figure 4: Bend Table That I will Duplicate in Excel.

Figure 3: Bend Table That I will Duplicate in Excel.

Conduit Bending Video

Figure 4 shows a conduit bending video by a local trade school (Dunwoody) that I think is first-rate. The instructor covers both 4-point (trapezoid) and 3-point (triangular) bends. My focus in this post is the 4-point saddle bend because that is what I am dealing with in my garage construction right now.

Figure 4: Good Briefing on 3-Point and 4-Point Saddle Bends.

Conduit Bending Formulas

Conduit Bending Formulas Ignoring Bend Radius

There are two formulas that I need to generate: (1) shrinkage, which is the reduction in horizontal length caused by the bend; (2) bend distance, which is the horizontal length of the bend region. Figure 5 illustrates the geometry of the situation.

 Figure 5: Key Conduit Bending Formulas.

Figure 5: Key Conduit Bending Formulas Ignoring Bend Radius.

Applying basic trigonometry to Figure 5, we can derive Equations 1 and 2.

Eq. 1 \displaystyle \text{BD }=\text{BO}\cdot \text{cot}\left( \theta \right)
Eq. 2 \displaystyle \text{S}=\text{BO}\cdot \text{tan}\left( {\frac{\theta }{2}} \right)


    • BD, Bend Distance is the horizontal distance between bends.
    • BO, Bend Offset is the depth of the obstacle to be passed over.
    • Θ is the angle of the bend.
    • S, Shrinkage is the effective reduction in horizontal conduit length because of the bend. Essentially, it is the difference in length between the hypotenuse and the base of a triangle.

I will use these equations to generate the table shown in Figure 3.

Conduit Bending Formulas Compensating for Bend Radius

Again, there are two formulas that I need to generate: shrinkage (Equation 3) and bend distance (Equation 5). An additional formula for the straight pipe length is also provided (Equation 4). Figure 6 illustrates the geometry of the situation and the associated formulas. The radius of the conduit bender, called R, will vary for each conduit bender. It normally is stamped on the bender, or the information is available in the vendor's literature.

Figure 6: Key Conduit Bending Formulas (Compensating for Bend Radius).

Figure 6: Key Conduit Bending Formulas (Compensating for Bend Radius).

Applying basic trigonometry to Figure 5, we can derive Equations 3, 4, and 5. Note that BD is defined slightly differently in that it represents the center-to-center distance between the bends.

Eq. 3 \displaystyle S\text{ = }CL-HL=\left( {BO-4\cdot R} \right)\cdot tan\left( {\frac{\theta }{2}} \right)+2\cdot R\cdot \theta
Eq. 4 \displaystyle SL=\left[ {BO-2\cdot R\cdot \left( {1-cos\left( \theta \right)} \right)} \right]\cdot \csc \left( \theta \right)
Eq. 5 \displaystyle BD=R\cdot \theta +\left[ {BO-2\cdot R\cdot \left( {1-cos\left( \theta \right)} \right)} \right]\cdot \csc \left( \theta \right)

Equations 3 - 5 are functions of the bend radius of the conduit bender. Because conduit benders can have different bend radii (see Figure 7), this means that using a single table for all conduit benders may result in some error – particularly for large bend offsets. Ideally, we would build a table for the conduit bender being used. I include this table with bend radius as a parameter on a worksheet in the Excel workbook associated with this post.

Figure 7(a): Klein™ Conduit Bender with a 4" Bend Radius. Figure 7(b): Ideal™ Conduit Bender with a 5.25" Bend Radius.



My focus here is on generating the traditional conduit bend table. In my workbook, I also include a tool using a more exact model.

There are a number of ways I could generate this table using Excel. The approach I chose was to:

  • Generate a table of values for bend offsets of 1 inch. I call this my "reference table" because it is used for all subsequent calculations.
  • Generate separate tables of shrinkage and bend distances.
  • Collate shrinkage and bend distance columns by bend angle (Θ).

I chose this approach because I wanted to experiment with arranging columns by using a helper row containing the ordinal number of each column and doing a horizontal sort.

For demonstration purposes, I also included a tab where I used formulas to fill down the columns. A third tab was includes the conduit bender radius as a parameter.

Reference Table

Figure 8 shows the shrinkage and bend distance formulas evaluated for a 1-inch bend offset (i.e. obstacle height), rounded to the nearest 1/16th of an inch. These values can be used as scale factors for other obstacle heights, which is exactly how the table in Figure 3 was generated.

Figure 6: Reference Bend Table.

Figure 8: Reference Bend Table.

Full Table Generation

The table shown in Figure 3 is generated by multiplying the bend offsets by the scale factors in Figure 9. I used Excel tables to perform this action.

Figure 7: My Excel Version of the Conduit Bend Table.

Figure 9: My Excel Version of the Conduit Bend Table.


I was able to duplicate the original table. I will be using this table for some conduit bending this weekend.

Posted in Construction | 2 Comments

Minor Planet Eccentricity versus Perihelion Chart


Quote of the Day

Einstein repeatedly argued that there must be simplified explanations of nature, because God is not capricious or arbitrary. No such faith comforts the software engineer…

— Frederick P. Brooks Jr., The Mythical Man-Month– Essays on Software Engineering

Figure 1: Interesting Graph Showing Minor Planet Eccentricities Versus Perihelion Distance. (Source)

Figure 1: Interesting Graph Showing
Minor Planet Eccentricities Versus
Perihelion Distance. (Source)

The amount of information being gathered in recent years on objects in the outer solar system is amazing. Think about what has happened in recent years:

  • The New Horizons probe visited Pluto.
  • Many new bodies have been discovered that both bigger and further out than Pluto (example).
  • Strong evidence has been found for a large body in the outer solar system.
  • The New Horizons probe has been directed to a recently discovered body (2014 MU69) that may consist of two bodies in very close proximity.

While searching the web for information on the outer solar system, I encountered the graph shown in Figure 1. This graph is made using eccentricity and perihelion data for ~1000 outer solar system objects. As I looked at it, I though I could generate a similar chart using data from the JPL Small Body Database Search Engine – a wonderful tool for solar system data exploration efforts.

I used the JPL search engine to download a list of all outer solar system asteroids and trans-Neptunian objects, which provided me 25K data points to plot (search setup). I then used Power Query and Excel to plot the data in Figure 2. Clearly, Sedna and 2012 VP113 are outliers in the data set. For reference purposes, I also included the same points for Pluto, Neptune, and Uranus.

Figure 2: My Version of Figure 1.

Figure 2: My Version of Figure 1.

For those who are interested in duplicating this work, my workbook and data file are included here.

Posted in Astronomy, Excel | 1 Comment

A Funny Stock Photo Error


Quote of the Day

He respectfully requests six Cleveland Browns pall bearers so the Browns can let him down one last time

— Obituary of a Cleveland Browns fan

Figure 1: No Way to Hold a Hot Soldering Iron.

Figure 1: No Way to Hold a Hot Soldering Iron.

We have been laughing at some stock photos of people soldering. An engineer was looking for a stock photo of a person soldering, so she went out to Shutterstock to find something. The first photo she found was Figure 1, which she immediately passed around to the group. It turns out that she found a number of photos that were equally bad. Apparently, a lot of people have never soldered. I was raised with a soldering iron in my hand, so I was a bit stunned to see this.

She also found a T-shirt that showed how you should hold a soldering iron (Figure 2).  If you want to buy this T-shirt, you can find it on Amazon.

Figure 2: Soldering on a T-Shirt.

Figure 2: Soldering on a T-Shirt.


Posted in Humor | 1 Comment

Using Excel to View US Pre-School Attendance Rate


Quote of the Day

We see a lot of feature-driven product design in which the cost of features is not properly accounted. Features can have a negative value to customers because they make the products more difficult to understand and use. We are finding that people like products that just work. It turns out that designs that just work are much harder to produce than designs that assemble long lists of features.

Douglas Crockford, author of JavaScript: The Good Parts. I encounter many marketing people who view product definition as the mere listing of features. In reality, there is a balance that must be achieved.

Figure 1: Graph Being Discussed by Jeffery Sachs. (Source)

Figure 1: Graph Being Discussed by Jeffery Sachs. (Source)

Jeffrey Sachs was on CSPAN this weekend giving a talk on the competitive challenges the US faces with other nations. During his presentation, he showed a chart (Figure 1) that ranks the US as 30th among reporting OECD countries with respect to preschool participation rates for 4-year-old children. The discussion was interesting, but I found myself focusing on the technical aspects of the graphs he was using. I am always looking for good Excel examples for use in training my staff, and the y-axis in Figure 1 contains formatted text, which is something I have not shown my staff how to do.

Figure 2: My Excel Version of Figure 1.

Figure 2: My Excel Version of Figure 1.

In Figure 2, I show how my duplication of Figure 1 using Excel. For those who like to follow along, my workbook is here. To highlight the formatting of the y-axis, I used green and red colors instead of bold font.

My process was straightforward:

  • Use Power Query to grab the data from this web site.
  • Generate a bar chart.
  • Use the method of Jon Peltier to format the y-axis.

While this is not a sophisticated chart, it does provide an end-to-end example of web scraping and charting.

Posted in Excel | Leave a comment

Dealing with Furnace Condensate At Low Temperatures


Quote of the Day

If all printers were determined not to print anything till they were sure it would offend nobody, there would be very little printed.

— Benjamin Franklin


Figure 1: A Typical Condensate Pump. (Source)

Figure 1: A Typical Condensate Pump. (Source)

While working on my retirement home and workshop in northern Minnesota, I have noticed that my furnace is generating between five and seven gallons of condensate per day. The furnace is on quite often this time of year because the outside temperature is running about -30°F (-35°C). I currently pipe the condensate over to a floor drain, which is connected to my septic system.

I mentioned the amount of condensate to my General Contractor (GC), and he said that this condensate can be an issue with a septic system in a cold climate because septic systems work best when they receive significant amounts of water flow. He said that he trickle of water can create a blockage if the flow is so low that it can freeze. Frozen pipes mean broken/blocked pipes and condensate water backing up into the house. If you are a homeowner that is gone for long periods of time during the winter – like vacationing in a warmer climate – you could return to a house with water damage.

My GC  said that condensate pumps (Figure 1) resolve this issue by collecting the condensate water and releasing it in surges, which ensures that a significant amount of water is sent down the drain. These surges are very similar in size to that produced by a toilet and are very unlikely to freeze. I went online and confirmed that others use this solution to resolve their issues with a condensate pump (example).

This discussion generated a few questions that a bit of math can help me answer.

  • How much water is generated per BTU of furnace heat?
  • How much propane is consumed per BTU of furnace heat?


Propane Characteristics

I obtained my information on propane from the Wikipedia:

Where is the Water Coming From?

Burning hydrocarbons generates water. We don't think about this water much because it often floats away in the form of steam. In the case of a furnace like mine, this steam is condensed so that its heat of vaporization can be captured and used to heat the building. In the case of a propane system like mine, the amount of water produced by the propane combustion can be computed by looking at the chemical formula for propane combustion (Equation 1).

Eq. 1 \displaystyle {{\text{C}}_{\text{3}}}{{\text{H}}_{\text{8}}}\text{+5}{{\text{O}}_{\text{2}}}\to \text{3C}{{\text{O}}_{\text{2}}}\text{+4}{{\text{H}}_{\text{2}}}\text{O}

where the chemical symbols are:

    • Propane, C3H8
    • Oxygen, O2
    • Carbon Dioxide, CO2
    • Water, H2O

Equation 1 tells us that for every mole of propane burned, we generate four moles of water.


My Furnace Characteristics

To answer my questions, we need to discuss my furnace and how it is running during this cold snap. Here are the critical parameters:

  • Furnace heat output: H = 100,000 BTU/hour (hr)
  • Furnace efficiency: ϵ = 96.3%
  • Furnace duty cycle: dc = 30%


Figure 2 shows my calculations. The key results are:

  • A propane-powered furnace will generate about 1 gallon of condensate per every 100,000 BTU of heat generated.
  • My furnace under these cold conditions is generating between 6 and 7 gallons of water condensate every day.
  • During the cold spell, I am burning about 34 pounds of propane each day.
Figure 2: Analysis of Condensate Generation and Propane Consumption.

Figure 2: Analysis of Condensate Generation and Propane Consumption.

Propane Heat of Combustion Propane Molar Mass


I now understand why I am seeing so much condensate water now. I will be installing a condensate pump this weekend.

Posted in Construction, General Science | 11 Comments

Audie Murphy's Rifle and the Power of Databases


Quote of the Day

The measure of leadership is not the quality of the head, but the tone of the body. The signs of outstanding leadership appear primarily among the followers.

Max De Pree, businessman and writer

Figure 1: Audie Murphy, the most decorated US Soldier of WW2. (Source)

Figure 1: Audie Murphy, the most
decorated US Soldier of WW2.

When I was a boy, I read the memoir To Hell and Back by Audie Murphy and was very impressed with his accomplishments as an infantry soldier during WW2 (Figure 1). It is a very American tale – a dirt poor teenager from family with a dead mother and missing father accomplishes amazing feats through sheer determination and force of will. He later starred in a movie version of his book that is well worth watching. I should mention that the book tells a better tale than the movie.

I recently read that the US Army had recovered his favorite rifle, which was an M1 carbine. The M1 carbine was shorter and much lighter than the infantry's standard issue M1 Garand. The carbine was usually carried by troops who had limited space available (e.g. tankers) or who had to carry other things (e.g. radiomen, paratroopers). For example, my father was a radioman and he carried an M1 carbine. In Murphy's case, he carried many different weapons, but appeared to prefer the M1 carbine. The story of its recovery is a testament to the power of modern database technology. The key to recovering the rifle was an interview with Murphy that provided a key piece of information – the serial number of the rifle.

Figure 2: Serial Number on Audie Murphy's M1 Carbine.

Figure 2: Murphy's M1
Carbine Serial Number.

When Murphy had the rifle, it certainly had certainly seen better days. The explosion of a nearby mortar round had damaged it, and Murphy did a field‑expedient repair on it using a wire. He continued to use the rifle, which he referred to as his "wounded carbine". I have read that at various times Murphy had used a Thompson sub-machine gun, an M1 Garand, and the M1 carbine. He must of have really like this rifle because during a 1967 interview, Murphy mentioned its serial number, 110878 (Figure 2). Over six million of these rifles were produced during WW2, but that serial number provided a means for uniquely identifying that rifle.

Figure 1: Warehouse in Movie Raiders of the Lost Ark. (Source)

Figure 3: Warehouse in the
movie Raiders of the Lost Ark.

The exact story of how the rifle left Murphy's possession is unclear. It appears that Murphy was wounded by a sniper on 25-Oct-44. Thinking that the wound may send him home, Murphy gave his rifle to a sergeant who hoped that the carbine would bring that him luck. Unfortunately, most of that sergeant's platoon was wiped out the following day. It is believed the rifle was recovered from the battlefield by the US Army, properly repaired, and put into storage. When you think of US government storage, think of a warehouse like what was shown at the end of the movie Raiders of the Lost Ark (Figure 3). It seems like a miracle that this specific rifle could be pulled out of a warehouse like this, but it really happened. A person at the Center of Military History Clearinghouse at the Anniston Army Depot did a database search for that serial number, got a hit, and the rifle was found (Source).

Figure 4 shows the rifle in its museum display today. I should mention that another movie,  Carbine Williams, was made that involved the M1 carbine. It is the story of a convict, Marsh Williams , who created the basic operating mechanism of the gun while serving time in a North Carolina prison. If you are curious about the four rifles he designed while in prison, see this Wikipedia paragraph.

Figure 4: Museum Display of Audie Murphy's Rifle, Gear, and Medals from WW2.

Figure 4: Audie Murphy's M1 Carbine in Museum Display. (Source)

I do have my own tale of trying to recover something from government storage, but it is much less interesting. Back in the early 1990s, I worked on the development of a very small sonar system that used low-voltage ceramic transducers. The US Navy paid $30 million for the development of this technology, which worked but the Cold War was ending and they decided not to pursue the technology any further. We sent the sonar system to the US Navy for storage. A few years ago, I got a call from a contractor who was wondering if I knew how to find the sonar system because the US Navy wanted to resurrect the project. I told him the name of the government employee that was sent the unit – I was concerned that he may have retired. The contractor called me back two weeks later and said that the government employee was still working, and he had the sonar system in his office! It never went into storage because it looked so cool that he had decided to use it as a doorstop. The $30 million doorstop was returned to the contractor, who found that it still worked, and he used it to pursue further development of the technology. I chuckle just writing that – $30 million doorstop.

Posted in Military History, software | 4 Comments

Earth's Curvature and Battleship Gunnery


Quote of the Day

Eggshells smashing each other with hammers.

— Winston Churchill, describing his feelings on battleship combat.


Figure 1: Factors Affecting Range Ballistics. (Source)

Figure 1: Factors Affecting Range Ballistics. (Source)

I must admit that I am a bit of a battleship junkie. I have been reading some old US Navy manuals on battleship fire control, which discuss the various effects that must be corrected for to ensure accurate fire (Figure 1). In this post, I want to examine how the curvature of the Earth affected the gunnery direction. Curvature corrections are only needed for very long-range artillery.

Figure 2: Range Table for US Navy 16-inch/50 caliber. (Source)

Figure 2: Range Table Excerpt for US Navy 16-inch/50
caliber. (Source)

Gunnery direction calculations usually begin with a range table (Figure 2), which tells the gunner the angle that projectile must be fired at to hit a target at a given range on the same horizontal plane as the gun (i.e. no difference in height between the gun and target). The target height relative to the gun can be either positive or negative, which affects the range that is used to index into the range table . For example, battleships in WW2 doing shore bombardment sometimes needed to attack fortifications on mountains (e.g. Mount Suribachi on Iwo Jima). For sea-level sea battles, the targets are below the horizontal plane of the ship firing the projectile.

Figure 3: Example Where Target is Lower Than the Gun. (Source)

Figure 3: Example Where Target is Lower Than
the Gun. (Source)

Figure 3 shows that firing at a target that is at sea level also involves a difference in heights. The rangefinders on a battleship determined a Line-Of-Sight (LOS) distance, but that distance is not the same as the horizontal distance listed in the table of Figure 2. The LOS distance must be corrected to an effective horizontal distance that can be looked up in the range table. My goal in this post is to show how we can correct the LOS distance to provide the required horizontal distance, which can then be used to read the gun elevation from the  table in Figure 2.

All calculations are performed in Excel – my workbook is here.


Earth Curvature Calculation

I have written about how to compute the curvature of the Earth over a given distance in another post using Equation 1, which relates the deviation from horizontal to the distance from the measurement origin.

Eq. 1 \displaystyle \delta =\sqrt{{{{R}^{2}}+{{R_{LOS}}^{2}}}}-R


  • δ deviation from horizontal, which is called curvature in gunnery.
  • R is the radius of the Earth (3963.2 miles)
  • RLOS is the LOS distance.

These parameters are illustrated in Figure 5.

We can use Equation 1 to compute a curvature versus range table (Figure 4). This table duplicates the results shown in this reference.

To illustrate how to read this table, consider the range of 19,800 yards. We go to the row that corresponds to 19,000 yards and find the column that corresponds to 800 yards. At the intersection of the row and column, we find a curvature of 84 ft.

Figure 4: Table of Curvatures for Different Horizontal Ranges.

Figure 4: Table of Curvatures for Different Horizontal Ranges. This figure shows how to find the curvature for a range of 19,800 yards, which is 84 feet.

Rate of Height Change

The US Navy manuals refer to "Column 19" and the "Change in height of impact for variation of 100 yards in sight bar."  While this sounds like a complex parameter, it is simply the tangent of the projectiles impact angle with respect to horizontal, which is called the angle of fall and is listed in the range table shown in Figure 2. The tangent of the angle of fall tells you how many feet the projectile loses in height for every foot of horizontal distance. We will use this parameter to relate the height difference to the range correction.


Earth Curvature Correction Calculation

Figure 5 defines some variables using the illustration of Figure 3. You can see in Figure 5 hitting target on a requires reducing the range setting of the gun (RH) from the distance measured along the line of the sight (RLOS) by Δ, i.e. {{R}_{H}}={{R}_{{LOS}}}-\Delta.

Figure 5: Illustration of the Range Correction.

Figure 5: Illustration of the Range Correction.

For modeling purposes in Figure 6, we can treat the trajectory of the shell near the target as a straight line. This allows us to use a simple trigonometric function to compute Δ, i.e. \text{tan}\left( {{{\theta }_{{Fall}}}} \right)=\frac{\delta}{\Delta }\Rightarrow \Delta =\frac{\delta}{{\text{tan}\left( {{{\theta }_{{Fall}}}} \right)}}.

 Figure 6: Details on the Correction Term Δ.

Figure 6: Details on the Correction Term Δ.


I copied a section of the range table from the US Navy manual and used it to compute: (1) curvature; (2) change in height of impact for variation of 100 yards in sight bar (i.e. LOS range); (3) danger space (discussed in this blog post). I can verify that (1) and (2) agree with the manual. Item (3) is discussed but not listed in the manual tables.

Figure 7: My Duplication of Curvature Correction Table.

Figure 7: My Duplication of Curvature Correction Table.


I am interested in understanding the gunnery corrections for the Earth's curvature and the Coriolis effect. I believe this post thoroughly covers the curvature correction.  I will put out a post shortly on the correction for the Coriolis effect.

Posted in Ballistics, Naval History | 36 Comments

Super Bowl Winners and Losers Using Power Query


Quote of the Day

In Data Science, 80% of time spent prepare data, 20% of time spent complain about need for prepare data.

—Tweet from Big Data Borat. I must admit, I am amazed at the poor format of data on the web. I work with a lot of old WW2 data that was processed by human typists, so I understand the quality issues there. By there is no excuse for the poor formatting of machine-processed data today.

Figure 1: Teams with Most Super Bowl Wins.

Figure 1: Teams with Most Super Bowl Wins.

I was reading a post on Statista showing the NFL teams with the most Super Bowl wins. Since my staff includes a number of football fans — mainly Viking and Packer supporters — I decided it would be a good training exercise to show them how to gather the football statistics and present them in the same manner as shown on Statista.  I should mention that I do not follow football at all; this is purely a data analysis exercise for me.

I used web resources and Power Query to generate an Excel workbook that I could then use to generate the charts I wanted. I duplicated the Statista graph in Figure 1. In addition to gathering the data, I also used a "bulk" substitution routine put together by Miguel Angel Escobar, also known by his Youtube handle as The Power User.  My spreadsheet is available here.

Figure 1: Teams with Most Super Bowl Wins.

Figure 2: Teams with Most Super Bowl Losses.

In addition to duplicating the Statista chart, I also want to look at the number of Super Bowl losses. I live in Minnesota and our Vikings team has never won the Super Bowl, though it has made four appearances. Figure 2 shows a list of NFL teams ranked by their number of Super Bowl losses.

The Denver Broncos have the most losses with 5, but they also have had three wins. The Vikings, Patriots, and Bills have all have had four losses. While the Patriots have had five wins, neither the Vikings nor Bills have had even a single Super Bowl win.

Figure 3: Number of Super Bowl Appearances By Teams with No Super Bowl Wins.

Figure 3: Number of Super Bowl Appearances By
Teams with No Super Bowl Wins.

In Figure 3 shows the number of Super Bowl appearances by teams with no wins. The Vikings and Bills lead this list. Things could be worse. Notice how the Jaguars, Texans, Lions, and Browns have never been to the Super Bowl. I especially feel sorry for the Lions and Browns, who have long established franchises.

I did put the data into table form, which I show in Table 1.

Table 1: NFL Super Bowl Statistics

1Pittsburgh Steelers862
2San Francisco 49ers651
3Dallas Cowboys853
4New England Patriots954
5Green Bay Packers541
6New York Giants541
7Los Angeles/Oakland Raiders532
8Washington Redskins532
9Denver Broncos835
10Baltimore Ravens220
11Baltimore/Indianapolis Colts422
12Miami Dolphins523
13New Orleans Saints110
14New York Jets110
15Tampa Bay Buccaneers110
16Chicago Bears211
17Kansas City Chiefs211
18St. Louis/Los Angeles Rams312
19Seattle Seahawks312
20Buffalo Bills404
21Minnesota Vikings404
22Atlanta Falcons202
23Carolina Panthers202
24Cincinnati Bengals202
25Philadelphia Eagles202
26Arizona Cardinals101
27San Diego/Los Angeles Chargers101
28Tennessee Titans101
29Cleveland Browns000
30Houston Texans000
31Detroit Lions000
32Jacksonville Jaguars000
Posted in Excel, Statistics | Leave a comment