Using Excel to View US Pre-School Attendance Rate

Posted on 4-January-2018 by mathscinotes

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 | Comments Off on Using Excel to View US Pre-School Attendance Rate

Dealing with Furnace Condensate At Low Temperatures

Posted on 2-January-2018 by mathscinotes

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

Introduction

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). So, we make sure to check for any issues each month and have the number of a plumbing company Airdrie that will be able to repair it ASAP if it ever breaks. But I want to do something about the amount of condensate it produces. 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. Of course, there are companies out there, like 1-800 WATER DAMAGE of Nassau County and many more, that can come to repair and restore any damage caused by water - but no homeowner wants to return to a flood.

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?

Background

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.

Analysis

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%

Calculations

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

Conclusion

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 | 13 Comments

Audie Murphy's Rifle and the Power of Databases

Posted on 26-December-2017 by mathscinotes

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.
(Source)

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 a 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 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.
(Source)

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 have really liked this rifle because, during a 1967 interview, Murphy mentioned its serial number, 1108783 (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.
(Source)

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 similar to that 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 decades after the war, 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 | 10 Comments

Earth's Curvature and Battleship Gunnery

Posted on 20-December-2017 by mathscinotes

Quote of the Day

Eggshells smashing each other with hammers.

— Winston Churchill, describing his feelings on battleship combat.

Introduction

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.

Background

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

where

  • δ 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.

Analysis

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 Δ.

Example

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.

Conclusion

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 | 102 Comments

Super Bowl Winners and Losers Using Power Query

Posted on 13-December-2017 by mathscinotes

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. So those who want to place bets using Sports Betting America after looking at the data can do so if they want, but for me, I just love the statistical analysis of it.

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

RankTeamGamesWinsLosses
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 | Comments Off on Super Bowl Winners and Losers Using Power Query

My Star Trek TOS Database

Posted on 8-December-2017 by mathscinotes

Quote of the Day

Well, if there is a bright center to the universe, you're on the planet farthest from.

—Luke Skywalker, expressing the lament of a young person growing up in a small town. As a person who grew up in a small, rural Minnesota town, I understand his feelings perfectly.

Figure 1: Spock with Whiz Wheel.

Figure 1: Spock with Whiz Wheel. I love the
anachronism here. (Source)

The people on my team frequently chide me for using management analogies drawn from the original Star Trek television series, which fans refer to as Star Trek TOS. Because of these analogies, I need to provide exact references for the younger folks on our team who have not memorized every episode. I decided that it was time for me to put together an online list that I could search and sort as the need arises. I grabbed the data from the Internet Movie Database (IMBD), including their review score for each episode.

The IMDB ratings did provide me with one surprise. While I expected City on the Edge of Forever to be the most popular episode, I was a bit surprised to see Mirror, Mirror as the second most popular.

Table 1: Star Trek TOS Episodes and Ranking.

EpisodeTitleRatingPlot Summary
1-01The Man Trap7.3Dr. McCoy discovers his old flame is not what she seems after crew members begin dying from a sudden lack of salt in their bodies.
1-02Charlie X7.2Captain Kirk must learn the limits to the power of a 17-year-old boy with the psychic ability to create anything and destroy anyone.
1-03Where No Man Has Gone Before7.8The flight recorder of the 200-year-old U.S.S. Valiant relays a tale of terror--a magnetic storm at the edge of the galaxy!
1-04The Naked Time8.0The crew is infected with a mysterious disease that removes people's emotional inhibitions to a dangerous degree.
1-05The Enemy Within7.8A transporter malfunction splits Captain Kirk into two halves: one meek and indecisive, the other violent and ill tempered. The remaining crew members stranded on the planet cannot be beamed up to the ship until a problem is fixed.
1-06Mudd's Women6.9The Enterprise picks up untrustworthy entrepreneur Harry Mudd accompanied by three beautiful women who immediately put a spell on all the male crew members.
1-07What Are Little Girls Made Of?7.6Nurse Chapel is reunited with her fiance; but his new obsession leads him to make an android duplicate of Captain Kirk.
1-08Miri7.1The Enterprise discovers a planet exactly like Earth, but the only inhabitants are children who contract a fatal disease upon entering puberty.
1-09Dagger of the Mind7.5Kirk and psychiatrist Helen Noel are trapped on a maximum security penal colony that experiments with mind control and Spock must use the Vulcan mind-meld to find a way to save them.
1-10The Corbomite Maneuver8.2After the Enterprise is forced to destroy a dangerous marker buoy, a gigantic alien ship arrives to capture and condemn the crew as trespassers.
1-11The Menagerie: Part I8.4Spock kidnaps the crippled Capt. Pike, hijacks the Enterprise and then surrenders for court martial.
1-12The Menagerie: Part II8.3At Spock's court martial, he explains himself with mysterious footage about when Capt. Pike was kidnapped by powerful illusion casting aliens.
1-13The Conscience of the King7.3While Captain Kirk investigates whether an actor is actually a presumed dead mass murderer, a mysterious assailant is killing the people who could identify the fugitive.
1-14Balance of Terror9.0The Enterprise must decide on its response when a Romulan ship makes a destructively hostile armed probe of Federation territory.
1-15Shore Leave7.7The past three months has left the crew of the Enterprise exhausted and in desperate need of a break...
1-16The Galileo Seven7.8The Galileo, under Spock's command, crash-lands on a hostile planet. As the Enterprise races against time to find the shuttlecraft, Spock's strictly logical leadership clashes with the fear and resentment of his crew.
1-17The Squire of Gothos7.5A being that controls matter and creates planets wants to play with the Enterprise crew.
1-18Arena8.1For bringing hostility into their solar system, a superior alien race brings Captain Kirk in mortal combat against the reptilian captain of an alien ship he was pursuing.
1-19Tomorrow Is Yesterday8.0The Enterprise is thrown back in time to 1960s Earth.
1-20Court Martial7.5Kirk draws a court martial in the negligent death of a crewman.
1-21The Return of the Archons7.3Seeking the answer to a century-old mystery, Kirk and crew encounter a vacantly peaceful society under a 6000-year autocratic rule that kills all those it can't absorb.
1-22Space Seed8.9While on patrol in deep space, Captain Kirk and his crew find and revive a genetically engineered world conqueror and his compatriots from Earth's 20th century.
1-23A Taste of Armageddon8.1Kirk and Spock must save their ship's crew when they are declared all killed in action in a bizarre computer simulated war where the actual deaths must occur to continue.
1-24This Side of Paradise8.0The Enterprise investigates a planet whose colonists should be dead, but are not.
1-25The Devil in the Dark8.5The Enterprise is sent to a mining colony that is being terrorized by a mysterious monster.
1-26Errand of Mercy8.3With a war with Klingons raging, Kirk and Spock attempt to resist an occupation of a planet with incomprehensibly placid natives.
1-27The Alternative Factor5.8Existence itself comes under threat from a man's power-struggle with his alternate self, with the Enterprise's strained dilithium crystals presenting his key to a final solution.
1-28The City on the Edge of Forever9.3When a temporarily insane Dr. McCoy accidentally changes history and destroys his time, Kirk and Spock follow him to prevent the disaster, but the price to do so is high.
1-29Operation: Annihilate!7.6The Enterprise crew attempts to stop a plague of amoeba-like creatures from possessing human hosts and spreading throughout the galaxy.
2-30Amok Time8.8In the throes of his Pon Farr mating period, Spock must return to Vulcan to meet his intended future wife, betrothed from childhood.
2-31Who Mourns for Adonais?7.1A powerful being claiming to be the Greek god Apollo appears and demands that the crew of the Enterprise disembark onto his planet to worship him.
2-32The Changeling7.8A powerful artificially intelligent Earth probe, with a murderously twisted imperative, comes aboard the Enterprise and confuses Capt. Kirk as his creator.
2-33Mirror, Mirror9.2A transporter accident places Capt. Kirk's landing party in an alternate universe, where the Federation is a barbarically brutal empire.
2-34The Apple6.4Primitive inhabitants of Gamma Trianguli VI worship a God who orders them to kill visitors, from the Enterprise.
2-35The Doomsday Machine8.8The USS Enterprise encounters the wrecked USS Constellation and its distraught captain who's determined to stop the giant planet-destroying robot ship that killed his crew.
2-36Catspaw6.2Very alien visitors to our galaxy attempt to connect with human consciousness but miss, winding up tapping into the regions of human nightmares instead.
2-37I, Mudd7.5Harry Mudd returns with a plot to take over the Enterprise by stranding the crew on a planet populated by androids under his command.
2-38Metamorphosis7.4While returning to the Enterprise aboard the shuttlecraft, Kirk, Spock, McCoy and a seriously ill Federation diplomat find themselves kidnapped by an energized cloud.
2-39Journey to Babel8.6The Enterprise hosts a number of quarrelling diplomats, including Spock's father, but someone on board has murder in mind.
2-40Friday's Child6.9The Federation clashes with the Klingon Empire over mining rights to Capella IV. A sudden coup between its warrior-minded inhabitants forces Kirk's party to flee with the now dead leader's pregnant wife.
2-41The Deadly Years7.4A landing party from the Enterprise is exposed to strange form of radiation which rapidly ages them.
2-42Obsession7.4Capt. Kirk obsessively hunts for a mysterious cloud creature he encountered in his youth.
2-43Wolf in the Fold7.4Kirk and the Enterprise Computer become detectives after Scotty is accused of murdering women on a pleasure planet.
2-44The Trouble With Tribbles8.9To protect a space station with a vital grain shipment, Kirk must deal with Federation bureaucrats, a Klingon battle cruiser and a peddler who sells furry, purring, hungry little creatures as pets.
2-45The Gamesters of Triskelion7.0Kirk, Uhura and Chekov are trapped on a planet where abducted aliens are enslaved and trained to perform as gladiators for the amusement of bored, faceless aliens.
2-46A Piece of the Action7.9The crew of Enterprise struggles to cope with a planet of imitative people who have modeled their society on 1920's gangsters.
2-47The Immunity Syndrome7.6The Enterprise encounters a gigantic energy draining space organism that threatens the galaxy.
2-48A Private Little War7.0Peaceful, primitive peoples get caught up in the struggle between superpowers, with Kirk unhappily trying to restore the balance of power disrupted by the Klingons.
2-49Return to Tomorrow7.6The Enterprise is guided to a distant, long-dead world where survivors of an extremely ancient race - existing only as disembodied energy - desiring the bodies of Kirk, Spock and astro-biologist Ann Mulhall so that they may live again.
2-50Patterns of Force7.6Looking for a missing Federation cultural observer, Kirk and Spock find themselves on a planet whose culture has been completely patterned after Earth's 1944 Nazi society.
2-51By Any Other Name7.7Galactic alien scouts capture the Enterprise for a return voyage and a prelude to invasion. Kirk's one advantage - they're not used to their adopted human form.
2-52The Omega Glory6.3Responding to a distress signal, Kirk finds Captain Tracey of the U.S.S. Exeter violating the prime directive and interfering with a war between the Yangs and the Kohms to find the secret of their longevity.
2-53The Ultimate Computer8.1Kirk and a sub-skeleton crew are ordered to test out an advanced artificially intelligent control system - the M-5 Multitronic system, which could potentially render them all redundant.
2-54Bread and Circuses7.3The Enterprise crew investigates the disappearance of a ship's crew on a planet that is a modern version of the Roman Empire.
2-55Assignment: Earth7.7While back in time observing Earth in 1968, the Enterprise crew encounters the mysterious Gary Seven who has his own agenda on the planet.
3-56Spock's Brain5.6The crew of the Enterprise pursues a mysterious woman who has abducted Spock's brain.
3-57The Enterprise Incident8.6An apparently insane Capt. Kirk has the Enterprise deliberately enter the Romulan Neutral Zone where the ship is immediately captured by the enemy.
3-58The Paradise Syndrome6.8Trapped on a planet whose inhabitants are descended from Northwestern American Indians, Kirk loses his memory and is proclaimed a God while the crippled Enterprise races back to the planet before it is destroyed by an asteroid.
3-59And the Children Shall Lead5.2The Enterprise reaches a Federation colony where the adults have all killed themselves but the children play without care.
3-60Is There in Truth No Beauty?7.0Lovely telepath Miranda is aide to Ambassador Kollos, in a box to stop insanity when humans see Medusans. She rejects Larry, a designer of Enterprise, and senses murderous intent nearby.
3-61Spectre of the Gun7.4As punishment for ignoring their warning and trespassing on their planet, the Melkot condemn Capt. Kirk and his landing party to the losing side of a surreal recreation of the 1881 historic gunfight at the OK Corral.
3-62Day of the Dove8.0Both humans and Klingons have been lured to a planet by a formless entity that feeds on hatred and has set about to fashion them into a permanent food supply for itself.
3-63For the World Is Hollow and I Have Touched the Sky7.2The Enterprise discovers an apparent asteroid that is on a collision course with a planet is actually an ancient populated generation ship.
3-64The Tholian Web8.1With Capt. Kirk and the derelict USS Defiant apparently lost, the Enterprise grapples with an insanity causing plague and an attack by the Tholians.
3-65Plato's Stepchildren6.6After Dr. McCoy helps the leader of a planet populated by people with powerful psionic abilities, they decide to force him to stay by torturing his comrades until he submits.
3-66Wink of an Eye7.4A group of aliens who exist in a state of incredible acceleration invade the Enterprise and abduct Capt. Kirk.
3-67The Empath6.6Trapped in an alien laboratory Kirk, Spock and McCoy meet an empath and are involved in a series of experiments.
3-68Elaan of Troyius7.2While transporting an arrogant, demanding princess for a political marriage, Captain Kirk must cope both with her biochemical ability to force him to love her and sabotage on his ship.
3-69Whom Gods Destroy7.0Kirk and Spock are taken prisoners by a former starship captain named Garth, who now resides at, and has taken over, a high security asylum for the criminally insane.
3-70Let That Be Your Last Battlefield7.2The Enterprise encounters two duo-chromatic and mutually belligerent aliens who put the ship in the middle of their old conflict.
3-71The Mark of Gideon6.5Kirk beams down to the planet Gideon and appears to find himself trapped on a deserted Enterprise. Spock on the real Enterprise must use his diplomatic skills to deal with the uncooperative inhabitants of Gideon and find the Captain.
3-72That Which Survives6.5After the Enterprise landing party beams down to investigate a geologically interesting planet, their ship is hurled across the galaxy. Kirk and company find a deserted outpost guarded by the deadly image of a beautiful woman.
3-73The Lights of Zetar6.2A mysterious, twinkling mass of sapient energy ravages an important archive and Scotty's new girlfriend may be linked to it.
3-74Requiem for Methuselah7.6On a planet, looking for an urgent medicinal cure, Kirk, Spock and McCoy come across a dignified recluse living privately but in splendor with his sheltered ward and a very protective robot servant.
3-75The Way to Eden5.5A group of idealistic hippies, led by an irrational leader, come aboard the U.S.S. Enterprise.
3-76The Cloud Minders7.0Kirk and Spock are caught up in a revolution on a planet where intellectuals and artists live on a utopian city in the sky while the rest of the population toils in mines on the barren surface below.
3-77The Savage Curtain6.8Kirk, Spock, Abraham Lincoln and Vulcan legend Surak are pitted in battle against notorious villains from history for the purpose of helping a conscious rock creature's understanding of a concept he does not understand, good vs. evil.
3-78All Our Yesterdays8.3When Kirk, Spock and McCoy investigate the disappearance of a doomed planet's population, they find themselves trapped in different periods of that world's past.
3-79Turnabout Intruder6.9Captain Kirk's insane ex-lover Dr. Janice Lester forcibly switches bodies with him in order to take command of the Enterprise.
Posted in Science Fiction | 5 Comments

Cannon Bore Measured in Pounds

Posted on 7-December-2017 by mathscinotes

Quote of the Day

Gentlemen, we will chase perfection, and we will chase it relentlessly, knowing all the while we can never attain it. But along the way, we shall catch excellence.

— Vince Lombardi Jr., the son of legendary Green Bay Packer Coach Vince Lombardi.

Figure 1: 17 Pdr Anti-Tank Gun on Sherman Firefly .

Figure 1: 17-Pounder Anti-Tank
Gun on Sherman Firefly . (Source)

I have been pulling together some WW2 data for an article that I want to publish in the next year or two. During my research, I have noticed that the British specified the caliber of their artillery by the nominal mass of the projectile (lbm or pound mass) and not by the bore diameter (Figure 1). I was curious as to how the British came to this particular system and decided to investigate further. As with many military standards, it traces its history back hundreds of years.

Back in the mid-17th century, cannons fired solid iron, spherical shot. This means that the application of a bit of geometry allows one to relate the diameter of the shot to the mass of the shot. The use of mass to determine the size of something was very common in the old days when weight measurements were far easier than dimensional measurements. We still see thread diameter measured using pounds – as long as the thread is made consistently (standard length, consistent thickness and material), weight can be used to specify thread diameter.

Equation 1 shows the formula that I will use to relate the diameter of spherical shot to the its mass. I will use Equation 1 to calculate a table that I found in a document from 1768 (Muller, pg. 6) that lists both the diameter of the shot and the gun to the mass of the projectile. Note that breach-loading artillery must have a bore diameter (referred to as the caliber) that is slightly larger than the diameter of the shot so that the shot can be forced down the barrel when covered in wadding. In 1768, the standard was that the caliber was 5% larger than the shot diameter.

Eq. 1 \displaystyle M=\rho \cdot \frac{4}{3}\cdot \pi \cdot {{\left( {\frac{D}{2}} \right)}^{3}}\Rightarrow D=\sqrt[3]{{\frac{{6\cdot M}}{{\rho \cdot \pi }}}}

where

  • M is the mass the round shot.
  • ρ is the density of the material making up the solid shot.
  • D is the diameter of the round shot.

The calculation of the cannon caliber (i.e. bore diameter) is simply 1.05·D, where D is the output of  Equation 1. I used the Excel spreadsheet here to generate my version of the original table (Figure 2).

Figure M: My Version of the Muller Table.

Figure 2: My Version of the Muller Table.

The values I compute are close to those shown in the original table (Figure 4).  I assume the errors are due to routine calculation errors on the part of the human calculators,  which can easily occur when using logarithm tables. I shudder just thinking about performing these calculations by hand using logarithms. I spent weeks in Osseo Junior High School  mastering interpolating log tables for just such hand calculations. Very little had changed between 1768 and 1971.

I should mention that Muller computes the density of the shot material based on a 9 pound projectile with 4 inch diameter – this projectile constituted both a diameter and weight calibration reference similar in spirit to the international prototype kilogram and meter.  I compute the density of the shot reference as  7.43 gm/cm3, which is very close to the density of pure iron (7.874 gm/cm3).

Using the term pounder to express the diameter of an artillery projectile today is not particularly useful because the mass of a projectile today does not uniquely determine its diameter. In fact, the vast majority of modern projectiles are composed of multiple types of materials (e.g., steel, copper, tungsten, explosive, tantalum, etc) in multiple types of geometries (e.g., shells, arrows, winged, rocket-boosted, etc)  based on their function. Thus, diameter and mass are not as strongly correlated today as 300 hundred years ago. Figure 3 shows how the pounder unit relates to the projectile diameter for some modern UK artillery. Notice how projectile mass does not uniquely determine the diameter of a modern projectile. This approach has become unworkable and the UK now specifies projectile diameter.

Figure 3: List of Modern Artillery With Their Bore Diameters and Pounder Designation.

Figure 3: List of Modern Artillery With Their Bore Diameters and Pounder Designation. (Source)

Appendix A: Original Table from Muller Document

Figure 4 shows the original table from the Muller document. Note that I actually corrected one obvious error in this table – duplicate values of 3.668 in the top caliber row (marked in red). Errors like this are very common. I have spent hours trying to find errors in old WW2 records. I do have sympathy for the poor yeoman who had to type in all this data.

Figure 3: Table of Shot Masses, Diameters, and Calibers.


Figure 4: Table of Shot Masses, Diameters, and Calibers.

Posted in Military History | Comments Off on Cannon Bore Measured in Pounds

The Pacific War and Manufacturing Capacity

Posted on 4-December-2017 by mathscinotes

Quote of the Day

Our job is to teach the students we have. Not the ones we would like to have. Not the ones we used to have. Those we have right now. All of them.

— Dr. Kevin Maxwell, teacher. I saw this quote on Dr. Nic's blog, which is one of my favorites.

Introduction

Figure 1: US Political Cartoon from 1942.

Figure 1: US Political Cartoon from 1942.

I was watched a particularly interesting lecture by Victor Davis Hanson on his new book The Second World Wars. While Hanson is generally thought of as an ancient Greek scholar, he does an excellent job of analyzing WW2 from a novel set of viewpoints: ideas, air, water, earth, fire, and people. One particular emphasis of Hanson is the role of the dominating manufacturing capacity of the Allied powers versus the Axis powers. I have listened to a number of WW2 history lectures recently, and all of them emphasized that  the US WW2 strategy from the beginning was to build massive numbers of medium quality weapons that would overwhelm Japan and Germany by sheer numbers. This approach was based on the belief that quantity can cover up all sorts of shortcomings with quality, personnel, and training.

My focus here will be on the Pacific War because a number of the lectures also focused there. I have never before looked at the relative production levels of the Allies versus Japan. I decided to grab some military production data for the UK, US, and Japan from this web site using Power Query and Excel. I did not look at military production for the Soviet Union because their contributions to the Pacific War were minimal. For those interested in my analysis, the spreadsheet is here. This was a good exercise in basic web scraping and table generation using Excel. I looked at three areas associated with wartime manufacturing: merchant shipping production (tonnage), aircraft production, and warship production.

Analysis

Merchant Shipping

Merchant shipping is the lifeblood of island nations like the UK and Japan. Figures 2 and 3 show merchant ship production data for the US, UK, and Japan during WW2. Figure 2 shows that the US and UK produced nearly ten times the merchant ship tonnage as did Japan. An order of magnitude advantage in shipping capacity when you are fighting an island war is overwhelming.

Figure 2: Merchant Ship Production Tonnage By Country.

Figure 2: Merchant Ship Production Tonnage By Country.

Figure 3 shows the data of Figure 2 in graphical form.

Figure 2: Merchant Ship Tonnage Versus Time For the US, UK, and Japan.

Figure 3: Merchant Ship Tonnage Versus Time For the US, UK, and Japan.

Aircraft Production

Because the US strategy was focused on island hopping, the production of aircraft was also critical because a single island airbase could secure a region of many tens of thousands of square miles – these island airbases were sometimes referred to as unsinkable aircraft carriers. As is shown in Figure 4, the US and UK dominated in aircraft production as well, though not by as much as seen with merchant ship production.

Figure 4: WW2 Aircraft Production By Type and Country.

Figure 4: WW2 Aircraft Production By Type and Country.

Warship Production

Ultimately, warships were needed because there was a war to win. I looked at the production of six classes of warships: aircraft carriers, battleships, cruisers, destroyers, escorts, and submarines. I started my counting in 1942, which is when the US really began warship production in earnest. Figure 5 shows a table of the data and Figure 6 shows a graphical view. The differences in the warship counts again reflect an order of magnitude difference – for example, the US and UK produced 151 aircraft carriers (all types) from 1942 to 1945 while Japan produced 15.

One stark difference between Japan and the UK and US alliance was in the area of escort vessels, which were warships designed to protect merchant convoys from air and submarine attacks. The data source I used for this post claimed that the Japanese had no escort vessels. Some people may argue that one class of Japanese destroyer (Matsu) could be viewed as an escort, but only seventeen were built and these had minimal impact because they were deployed at the very end of the war. In any event, the UK and US built hundreds of escort vessels to protect their supply lines. Japan did not give priority to protecting their supply lines, which meant starvation was a real threat once they were isolated.

Figure M: Warship Production By Nation and Year.

Figure 5: Warship Production By Nation and Year.

Figure 6 provides a visual representation of the warships produced between 1942 and 1945. I used the color green for Japan. You see very little green in Figure 6.

Figure M: Graphical View of Figure M.

Figure 6: Graphical View of Figure 5.

Conclusion

The US gave the European Theater higher priority for resources than the Pacific Theater; historians usually talk in terms of 30% to the Pacific and 70% to Europe. This approach was referred to as Europe First. While Japan was only given second priority, the data in this post shows that the US and UK still had more than enough resources to dominate over Japan. Once Germany was defeated, Japan was facing opponents with truly awesome capacity for destruction. I am amazed that they even considered starting a war with the US, let alone the Allies (US, UK, Australia, New Zealand, and the Soviet Union). I hope modern leaders learn just how easy it is to miscalculate when it comes to military action.

Posted in History Through Spreadsheets, Military History | Comments Off on The Pacific War and Manufacturing Capacity

Statistics of US Senate Disciplining Its Members

Posted on 21-November-2017 by mathscinotes

Quote of the Day

The Oversimplification, The Cherry Pick, The Butter-up Undercut, The Demonizer, The Blame the Blogger, The Ridicule and Dismiss, The Literal Nitpick, The Credit Snatch, The Certain Uncertainty, The Blind Eye to Follow-Up, The Lost in Translation, The Straight-Up Fabrication.

— David Levitan (author of the book Not a Scientist), his categorization of the methods politicians use to obfuscate science. I have seen our politicians use every method he described. Politicians generally begin their attack on science with the phrase "I am not a scientist but …"

Figure 1: Drawing of Senators Fist Fighting in the 1800s.

Figure 1: Senators Fist Fighting. (Source)

The controversial senate candidacy of Roy Moore has resulted in some discussion of how the US Senate would respond if he won the election. Senate Majority Leader Mitch McConnell has mentioned the possibility of expulsion. I could not recall hearing of anyone being expelled from the Senate recently, so I decided to grab some data from the Senate web site and summarize it here.

I used Power Query to download and process the data from the Senate website. Figure 2 shows my summary of all expelled Senators. Only 15 senators have been expelled, with 14 being expelled for their participation in the Confederacy. The last senator to be expelled was in 1862.

Figure 2: List of All Expelled US Senators.


Figure 2: List of All Expelled US Senators.

There are other ways the US Senate can discipline its members – actions like censure, condemnation, and denunciation. Near as I can tell, people distinguish condemnation and denunciation from censure, but there really is no difference (e.g. discussion of McCarthy). Figure 3 show the list of senators subjected to forms of discipline other than expulsion. I also included those senators who resigned or whose terms ended before their disciplinary reviews were completed. The last senator to resign while undergoing disciplinary review was Robert Packwood back in 1995.

Figure 3: Senators Accused of Bad Behavior But Not Expelled.

Figure 3: Senators Going Through Disciplinary Actions Other Than Expulsion.

For those who are interested, my workbook is here.

Posted in Civics Through Spreadsheets | 5 Comments

An Interstellar Visitor?

Posted on 20-November-2017 by mathscinotes

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.

Postscript

Here is an artist's conception of the asteroid's shape given recent data (Figure 6).

Figure M: Artist's Conception of Asteroid's Shape Based on European Space Agency Data. (Source)

Figure 6: Artist's Conception of Asteroid's Shape Based on European Space Agency Data. (Source)

Posted in Astronomy | Comments Off on An Interstellar Visitor?