Lightning Damage at My Garage

Quote of the Day

When you counsel someone, you should appear to be reminding him of something he had forgotten, not of the light he was unable to see.

Baltasar Gracian, Spanish philosopher  on how to give advice.

Burn Residue
During a major storm two weeks ago with 80 mile per hour winds (129 kph) and heavy rain,  a lightning strike near my garage destroyed two TVs, a power adapter for my robot lawnmowers, a wireless router, two data switches, and my furnace's propane tank regulator and copper feedline. As an illustration of the damage, Figure 1 shows the residue left from my mower power adapter exploding. The light region inside the smudge is from my fingers wiping across the smudge to see how thick the film was. The film was removed using mineral spirits. A small burn mark is all that remains.

For those who wonder, I have all sorts of lightning protection on everything, including a dielectric separator between the propane tank and the furnace. This saved quite a bit my electrical equipment, but not everything.

I am in the process of fixing everything. Because the power adapter is so damaged and a number of my circuit breakers tripping, I am guessing the surge came in through the garage power system. My cabin also suffered some damage because its electrical system is connected to the garage over a 120 foot (37 meter) cable.

Figure 2 shows the blown-up power adapter. I found the cover 13 feet (4 meters) away in the woods. The copper feed line to my propane tank was turned black and it developed a major leak, which allowed all the propane to escape into the soil. The smell of the odorant in the propane (ethyl mercaptan) is noticeable from the soil. I included a 1-2-3 block in Figures 1 and 2 for a size reference.

Figure 2: Blown Power Adapter.

Figure 2: Blown Power Adapter.

Posted in Cabin | 2 Comments

Excel Spillable Ranges are Great!

Quote of the Day

I begin to speak only when I'm certain what I'll say isn't better left unsaid.

Cato the Younger. I try to keep this quote in mind during meetings.

Figure 1: Toy Data Set With Same Format as My Work Example.

Figure 1: Toy Data Set With Same Format as My Work Example.

I use Python or R for my large-scale data work, but I do find Excel a very powerful ad hoc data analysis tool, particularly with some of the new functions that use spillable ranges. Today, I was given a large table of Engineering Change Orders (ECOs) and a comma-separated list of the documents each ECO affected (very abbreviated form shown in Figure 1). I needed to generate a count of the number of times each document was modified by an ECO and I needed that count for a meeting coming up in a few minutes. My approach can be described as follows:

    • Use TEXTJOIN to combine all the comma-separated lists into one long comma-separated list.
    • Use FILTERXLM to break up the combined comma-separated list into a column with one document per row
    • Use UNIQUE to generate a list of the unique documents that were changed
    • Use COUNTIF to count the number of times each individual document was changed

Figure 2 shows a screenshot of my Excel workbook. For those who like to work along, I include my workbook here.

Figure 2: Functions Applied to Generate Document Change Counts.

Figure 2: Functions Applied to Generate Document Change Counts.

Posted in Excel | Comments Off on Excel Spillable Ranges are Great!

Effect of Lumber Price Rise on House Cost

Quote of the Day

Great things are not done by impulse, but by a series of small things brought together.

Vincent van Gogh


Lumber Prices

Figure 1: Lumber Prices Versus Time (NASDAQ data, My Graph)

I recently went into a home center and was shocked to see a 2‑in x 4‑in stud now costs $11 that used to cost $2.50. A 4‑foot x 8‑foot sheet of plywood now costs $100 that used to cost $25. These prices were so high that I decided to delay building a shed until the prices had dropped to more sane levels.

Unfortunately, a perfect storm of COVID panic and bizarre trade policies has caused a massive rise in US lumber prices (Figure 1). Lumber prices are typically around $300 per 1000 board-feet (units discussed below). These prices recently peaked at over $1700 per 1000 board-feet.

What impact did these high lumber prices have on the price of a home? Time to do a little math …

For this post, I will be performing my calculations using a new tool called Maple Flow. I want to give it a test drive.


Yet Another Apology for Units

While my raw US Forest Service Wood Product data is metric, the commodity price data is in US Customary units. Because this post is focused on the US housing market, my final results will in the form that US readers can use directly.


Wood volume in the US is usually expressed in board-feet, which has units of ft2·in. There are 423.8 board-feet in a cubic meter of wood.
House Area
House area in the US is usually expressed in square feet, which has units of ft2.

Wood Usage Per Home Data

All of my calculations will be based on 2019 data from this US Forest Service document, where Table 3 contains this information.

Figure 2: US Forest Service Wood for Housing Data.

Figure 2: US Forest Service Wood for Housing Data.

I am only interested in the single-family house data. Here is the information critical to my analysis (Figure 2):

  • Floor Area Per Unit: ASFH := 234.5 m2 (2524 ft2)
  • Wood Volume Per Unit: VWood := 44.0 m3 (18.65E3 board-feet/home)


Wood Cost Increase

Figure 1 provides the following information:

  • Maximum wood cost per board-foot: $1,733.50/1000 board-feet
  • Average wood cost per board-foot (pre-COVID); $392.81/1000 board-feet (data mean before 1-Jan-2021)

The price rise in wood products can be computed as shown in Equation 1.

Eq. 1 \displaystyle \Delta W=\$1733.50-\$392.81=\$1,340.69 \text{ per 1000 board-feet}

For those who like to follow along, my Excel workbook of historical lumber data is available here.

Mean Board-Feet Per Square Foot of Housed

Equation 1 shows how we can calculate the mean board feet (BfA) per square foot of the home. This number includes both lumber and sheet goods. I should mention that a quick Google search will give you a rough number of 6.3 board-feet per square foot of house area (example). I find this number to be a bit low.

Eq. 1 \displaystyle k_{BdFtPerSqFt}=\frac{{{{V}_{{Wood}}}}}{{{{A}_{{SFH}}}}}=\frac{{18.65E3}}{{2524}}=7.34\frac{{\text{bdft}}}{{\text{f}{{\text{t}}^{2}}}}

Estimate Home Cost Increase at the Peak of the Wood Cost Boom

Assuming that houses built in 2019 and 2021 are similar, we now can compute that impact of the recent wood cost increase on the price of a typical home using Equation 2.

Eq. 2 \displaystyle \Delta C=\Delta W\cdot k_{BdFtPerSqFt}\cdot {{A}_{{SFH}}}= {1,340.69}\cdot 7.34\cdot 2524=24,999\ \text{USD}

Here is a screenshot of all my calculations in Maple Flow.

Figure 3: Maple Flow Version of Calculations.


At the peak of the recent wood cost increase, the cost of a typical home increased $25K just for wood alone. I should point out that other materials, such as drywall, have also increased in price. Fortunately, we are seeing the wood prices decrease rapidly as more supply is coming online.

I feel bad for the young people trying to buy a house in recent years. Between the Great Recession and COVID, young people entering both the housing and job markets have been subjected to terrible uncertainty.

Posted in Construction | 4 Comments

Our House Design Process

Quote of the Day

Find out who you are and do it on purpose.

- Dolly Parton


Cabin Today

Figure 1: Cabin Today. (Source: Dawn Biegert)

A number of people have asked questions about the process we used to design and build our cabin in northern Minnesota (Figure 1), a build that I have discussed in other posts (link, link). The questions center on the tools we used, what the build process was like, and things to watch for. The following post examines our design and build process from the standpoint of what went well and not so well.

Overall, I was very happy with how things turned out. We have been in the house now for 3 years and have had no major issues. The minor issues were mainly simple screw pops in the drywall and some drywall seams that separated. We also had some outlets that were in the wrong places and I had to move them. Normally, we would have caught the outlet position issues during construction, but we were not on-site often enough.

While things went well, I don't think we would build another custom home. It was too much work – one was enough.


High-Level Design

We had some basic requirements on the structure:

  • Comply with Local Building Codes
    There were a large number of building codes related to the structure being close to the lake. One of the main constraints was that we could only grow the footprint of the old structure by 50%. To get enough area for our children and grandchildren, we needed to add a second floor. The footprint of the house was also limited by building restrictions on how close the house could be to the lake and a nearby road. The house placement was highly constrained.
  • Handicapped-Accessible First Floor
    We have handicapped friends that we want to be able to use our home comfortably and we wanted to consider our needs when we are old and want to continue living here. We decided on a home with:

    • no basement
    • laundry, kitchen, curbless shower on the first floor
    • stepless entrances/exits
    • made provisions for adding a home elevator for access to the second-floor if that becomes necessary
    • because of the house size restrictions, we decided to add a large garage on another lot that would provide a shop and an entertainment area. Little did we know that handicapped accessibility would be important right away as my wife would suffer a serious injury and would be laid up for the better part of a year (she is fine now).

Concept Development

My wife spent years collecting pictures of homes that she thought would fit our north woods lifestyle. She put together a collection of ~100 home designs that had features that she liked, for instance, lighting ideas like wall lights or pendant lights, carpet colors, what wood the furniture would be, how the kitchen would be set out, everything under the sun she had already gathered ideas for. Eventually, she found a plan for a boathouse that became her inspiration for the overall look of the house.

Detailed Design

Designing this house was a lifetime dream for my wife and she started by drawing some simple hand drawings (she is a skilled hand draftsman). However, she had a large number of alternatives she wanted to evaluate, and hand drawing proved to be just too slow.

Figure M: Chief Architect Home Designer.

Figure 2: Chief Architect Home Designer Package.

To speed her ability to try different options, we decided to move her from hand drawing to a 3D modeling program. This worked really well. For house design, we ended up choosing Home Designer by Chief Architect (Figure 2). It was easy to use, had excellent Youtube training videos, and had an excellent library of house components directly from the manufacturers (e.g. windows) that provided realistic renderings of her house concepts. I think we spent ~$300. I captured the initial draft of the house based on the original boathouse concept. My wife then did everything else.

One thing that we did miss during the design phase was allocating enough space for storing snow while snowplowing. While this issue is not directly about the house, it has become a big issue because the snow plower has to push snow up on the lawn. The plowed snow contains rock and other debris from our driveway, which is then deposited on our lawn every spring when the piles of snow melt. He also damages the lawn during the plowing operating. We are getting ready to buy a large snowblower that we can blow the snow over the whole yard and spread out the problem, but it would have been simpler to have clear areas for the snowplow to push the snow into.

Reality Phase

Builder Selection

Our location deep in the woods limited our builder options. We were fortunate to find Mark Utecht, a builder from the Twin Cities (Minneapolis/St. Paul) who had decided to move to the north woods. He had extensive experience building custom homes similar to ours and we liked his commitment to quality.

Figure M: Pettibone (Source: Pettibone).

Figure 3: Pettibone (Source: Pettibone).

I worked on construction sites as a drywall installer during high school and I saw quite a few carpenters. Utecht may be the best hammer and nail man I have ever seen. While he contracted out the electrical, HVAC/plumbing, concrete, and roofing tasks, he did everything else himself. He was interesting to watch because he was a master of working alone and was a magician with a Pettibone (Figure 3). Every motion was well-practiced and efficient. I love to watch a master in action. When it comes to work that needs doing like plumbing, it's usually best to go for experts in that particular field to do that job as there's too much to mess up and that could go wrong were you to do it yourself. This is why reaching out to the likes of Apollo Plumbing is a popular decision when working on construction and remodeling projects.

To simplify getting cost estimates, Utecht requested that we take our plans to a draftsman who would check what Chief Architect generated for code compliance, generate drawings in a format that Utecht was used to, and creating a part database for cost estimation. This cost $1500 and I think was worth it as we began to iterate on costs.

Like most custom home builds, the initial cost estimates were far over our budget. Utecht was experienced at getting the most value for the dollar and he worked with my wife and the draftsman to hit our cost targets. This activity took a couple of months.

Construction Phase

Building the structure took about six months. Overall, there was relatively little drama during the construction. However, there were some miscommunications that normally would be easy to deal with if my wife could have been on-site every day. However, we live 200 miles from the construction site and daily inspections were out of the question. Also, there is no cellular service nor Internet at the site.

Fortunately, I was able to get a DSL line to the site and I set up cameras for remote monitoring. This helped and we were able to correct some errors from long-distance, but remote monitoring was not adequate. Some painful rework ended up being needed.

Overall Assessment

Table 1 shows a score that represents my feeling about the build and how it went.

Table 1: Cabin Build Scorecard.
Category Quality Conformance to Plan Comments
General Contractor A A- Very easy to work with. My only regret is that he did not put in a sump basket after the HVAC guy advised him to. This has been annoying because the lift pump (for combustion condensate) has been failure-prone.
HVAC/Plumbing A A No issues of any sort.
Sheetrocking B A Lots of screw pops started right away. A number of edges have experienced paper separation. I am able to repair these.
Painting B A Some areas were not smooth, brush strokes visible.
Electrical A B Did not follow the instructions for placing a number of outlets. This has been annoying and I had to move a number of outlets.
Concrete A A The house is built on a heated slab. It has provided us very comfortable heating. We also have some outdoor stamped concrete work that we really like.
3D Design Software B+ N/A The software was critical to getting the home we wanted. The software made no calculation mistakes but did have some annoying bugs. Nothing earth-shattering, but annoying.
Posted in Cabin, Construction | Comments Off on Our House Design Process

Ben Franklin and the Gulf Stream

One trains one's imagination to go visiting.

Hannah Arendt

Figure 1: Ben Franklin. (Wikipedia)

One of my favorite Youtube channels is Wind Hippie Sailing. The Wind Hippie is a free spirit who does not worry about little details like currents and wind – this is the charm of her channel.  While sailing from North Carolina to Puerto Rico, she soon learned the power of the Gulf Stream when she discovered that she was making very slow (maybe negative) progress. Fortunately, she was able to get out of that situation. I was worried she might end up somewhere dangerous, like a reef.

Her predicament reminded me of a story that Sister Mary Agnes told our fifth-grade class about Ben Franklin (Figure 1). She told us that Ben was the first person to publish a chart of the Gulf Stream. Ben's Gulf Stream story began during a visit to England where he heard tales of ships taking much longer to cross from the UK to the US than from the US to the UK. He became curious and upon his return to the US contacted a US whaling captain, Timothy Folger, who was also his distant cousin. Whalers were famous for collecting all sorts of ocean data. Captain Folger confirmed what Ben had heard in England and told Ben what the whalers knew about the Atlantic currents. He showed Ben the path of the current and even how to avoid it. Ben and Captain Folger put together a chart that Ben sent to a contact in England, who then printed it.

I remember as a young boy being impressed by Ben's curiosity and approach – I still am. For those who want to know more about the Franklin-Folger chart of the Gulf stream, check out this document.

Posted in History of Science and Technology, Naval History, Navigation | 6 Comments

US Daylight Saving Time Date Calculation in Excel

Quote of the Day

There is a wonderful, almost mystical, law of nature that says three of the things we want most—happiness, freedom, and peace of mind—are always attained when we give them to others. Give it away to get it back.

— Basketball coach John Wooden


Figure 1: US Daylight Savings Time Rule. The clocks change at 2 AM Sunday morning. (Source: Me)

Figure 1: US Daylight Saving Time Rule: Spring Ahead, Fall Back. The clocks change at 2 AM Sunday morning. (Source: Me)

I recently had a situation where I needed to correct a number of date/time values because they did not take into account Daylight Saving Time (DST). To be specific, some transactions from China were recorded assuming a fixed time offset with respect to US Central Standard Time. Because of DST, this is not always the case. My customer only works in Excel, so the work was done in Excel.

To perform this correction, I need to know if the date/time values occurred during the DST date ranges for those years. As discussed below, the US adopted its current DST assignments in 2005.  I only need to calculate the DST correction for dates after 2005, so I will not worry about earlier years.


Date Definitions

The 2005 Energy Policy Act defined the dates of DST to be:

  • Start DST: Second Sunday of March.
  • End DST: First Sunday in November.

Excel Dates

Solving this problem requires two Excel date functions.

DATE(year, month, date)
This function computes an Excel date object, which is an integer equal to the number of days since January 1st, 1900.
WEEKDAY(date object)
This function takes a date object and computes an integer that corresponds to the day of the week, where the default numbering is Monday = 1, Tuesday = 2, … , Sunday = 7.


Date Calculation Approach

I will illustrate the calculation using the 2021 Spring DST date calculation.

  • Create a date object for the latest possible spring DST date (14-March-2021)
    • If March 1st is a Monday, the second Sunday in March is on the 14th
    • Excel Calculation: latest = DATE(2021, 3, 14)
  • Compute the weekday of 14-March-2021 from the previous Sunday
    • This is simply the weekday number of 14-March-2021  minus 1
    • offset = WEEKDAY(latest) - 1  (Sunday = 0, Monday = 1, …, Saturday = 6)
  • SpringDST = latest -offset = DATE(2021, 3, 14) - (WEEKDAY(DATE(2021,3,14))-1)

The same logic can be used to give us the Fall DST date by substituting in the lastest possible first Sunday in November: DATE(2021, 11, 7) - (WEEKDAY(DATE(2021,11,7)-1) = 7-Nov-2021.

Excel Workbook Example

My Excel workbook can be found here. I used it to create the following tables (Figures 2 and 3) of Spring and Fall DST dates, which agree with a similar online table.

Figure 2: Spring DST Dates.

Figure 2: Spring DST Dates.

Figure 3: Fall DST Dates.

Figure 3: Fall DST Dates.

Posted in Excel | 6 Comments

Taffy 3 Total Displacement vs Yamato Using R

Quote of the Day

Rome wasn’t built in a day, but they were laying bricks every hour. You don’t have to do it all today. Just lay a brick.

James Clear.

IJN Yamato

Figure 1: IJN Yamato, arguably the most powerful battleship of WW2 (along with its sister Musashi).

I was watching a documentary about the Battle Off Samar on my favorite Youtube naval channel called Drachinifel. During this show, Drachinifel stated that the battleship Yamato displaced more tonnage than the entire Task Group 77.4.3 (call sign Taffy 3) that it was attacking. I found this a remarkable statement and one that I could verify using a little bit of web scraping. Because one of the students I tutor use R, I thought this would be a good exercise to implement using R and Rmarkdown (a great report generation tool).


Battle Details

On 25-Oct-1944, a large Japanese surface fleet consisting of four battleships and six heavy cruisers, two light cruisers, and eleven destroyers attacked a greatly overmatched US force that was protecting a Philippine beachhead. Remarkably, the US force was able to turn back the attack with a defense no less remarkable than David versus Goliath. For example, you cannot read about the USS Johnston and fail to be in awe of the actions of that ship.

The best way to learn about the Battle off Samar is to watch the Drachinifel video shown below. His research coupled with a dry sense of humor makes the viewing enjoyable.

Figure 2: Drachinifel Video on the Battle of Samar.

Web Scraping

Here is the process I used to sum the displacements of the Taffy 3 ships:

  • Gather the list of Taffy 3 ships from US Naval History and Heritage Command
  • Create Wikipedia links from the ship names.
    Wikipedia has a standard format for converting a ship name to a hyperlink, so this involved just concatenating the correct prefix.
  • Using the rvest package with R, grab the ship class and displacement data from Wikipedia.
  • Correct some obvious Wikipedia errors.
    As a person who has authored a number of Wikipedia pages, I know how hard it is to get all the details correct, especially for WW2 ships because sources often differ. In this case, the problems had to do with different authors using different ship displacement definitions. For those interested, Wikipedia normally uses standard displacement. Unfortunately, the editors do not always enforce that convention. I manually corrected the error. The errors were obvious because different ships of the same class had radically different displacements. For the corrections, I used data from the US Navy's History and Heritage Command.
  • Using the kable package, create a table of Taffy 3 ships, their individual displacements, and their total displacement.

I am including my R file and a figure in this zip file for those who like to follow along. I include a PDF of my Rmarkdown file here.


Table 2 shows a screenshot of the key result from this web scraping exercise. The total displacement of Taffy 3 is just under 61K long tons, which is well under Yamato's 64K long tons displacement.

Figure M: Taffy 3 Ships, Classes, and Displacements.

Taffy 3 at Samar took very heavy casualties with five ships being sunk:

The Japanese Center Force also took heavy casualties with three heavy cruisers sunk.

Posted in History Through Spreadsheets, Naval History | 2 Comments

US Battleship Fuel Usage

Quote of the Day

Y2K was 21 years ago. Looking back, I think the only thing we learned is that if a bunch people work really hard to stop a problem from happening, lots of other people will assume it was never really a problem.

— Steve Lieber. I have seen many people work hard to ensure problems would never occur. This effort was never appreciated. I have also watched people who created problems win awards for fixing the problems they created. Very strange organizational behavior.


Figure 1: USS North Carolina, my choice for the most beautiful of the US battleshps. (Wikipedia)

Figure 1: USS North Carolina, my choice for the most beautiful of the US battleships. (Wikipedia)

In this post, I will examine the fuel consumption of the three most modern battleship classes that the US deployed during WW2:  North Carolina,  South Dakota, and Iowa. The data is scraped from the Hyperwar website, which is one of my favorite targets for data extraction. In this case, the Hyperwar page contains a set of tables from the US Navy document FTP 218: War Service Fuel Consumption of US Navy Surface Vessels.

The fuel consumption (gallons/hr ) for a ship is a highly non-linear function of speed (knots), as my graphs will show. I also estimate each ship's running time as a function of speed using their rated fuel capacity, which is called radius oil that is the sum of the fuel oil and diesel capacities.

For those who like to follow along, my Excel workbook is available here.


US Battleship Classes

I first look at the different battleship classes the US deployed during WW2. Fortunately, a table in Wikipedia makes this data easy to access. Table 1 shows the ten battleship classes that the US deployed during WW2, along with the main battery diameter and the number of main battery guns. The battleship classes are shown in the order they were deployed.

Table 1: US Battleship Classes Deployed During WW2.

US WW2 Battleships By Name

There were 18 battleships in US Navy service on the morning of 7-Dec-1941. All were WW1-era ships, except for the 2 North Carolina class ships that were just entering service. After Pearl Harbor, 8 of these ships were damaged with 6 returned to service after months of repair and refit. The battleships in service immediately after Pearl Harbor bore the brunt of the fighting, both in the Atlantic and Pacific, until newer ships were completed.

Table 2: US WW2 Battleships By Name.


Scraping the data from the Hyperwar website was straightforward using Power Query because all the data was contained in well-structured tables. Figure 2 shows the fuel consumption of the USS South Dakota, USS North Carolina, and USS Iowa versus speed in knots. Note how their fuel consumption was similar at low speeds but diverged as their speed increased. USS North Carolina, in particular, was comparatively inefficient. The USS Iowa was quite efficient, with its fuel consumption rate at 29 knots roughly the same as the USS North Carolina at 25 knots.

Figure 2: Battleship Fuel Consumption Versus Speed.

Figure 2: Battleship Fuel Consumption Versus Speed.

Knowing a ship's fuel consumption as a function of speed and its fuel capacity, we can compute its sailing time before running out of fuel (Table 3). Ships normally would refuel their tanks regularly to ensure that they always had enough fuel to handle foreseeable needs (link). When they didn't, problems occurred (see discussion of Bismarck's fuel issues).

Table 3: Battleship Running Time Versus Speed.


It is hard to imagine ships that consume 9000+ gallons of fuel per hour. It is also impressive how fuel-efficient the USS Iowa as compared to the earlier classes.

We are fortunate that the US was able to to keep 9 battleships as museums ships:

  1. Alabama
  2. Arizona
  3. Iowa
  4. Massachusetts
  5. Missouri
  6. New Jersey
  7. North Carolina
  8. Texas
  9. Wisconsin

I hope to see them all in the next few years.

Posted in History Through Spreadsheets, Military History, Naval History | 3 Comments

50 Destroyer Pre-War Base Deal

Quote of the Day

Vision is not necessarily enough; it must be combined with venture. It is not enough to stare up the steps; we must step up the steps.

Vaclav Havel


Figure 1: Wickes-Class Destroyer.

Figure 1: Wickes-Class Destroyer.

This post is going to look at the Destroyers for Bases deal between the US and UK. The bargain was an executive agreement announced on 2-Sep-1940 to trade 50 WW1-era US destroyers to the UK for US basing rights in the Caribbean, Bermuda, and Newfoundland. I have seen the destroyers described as obsolete, which seemed odd for ~20-year-old destroyers that nominally have 30 year lifetime (typical for most US Navy ships).

Alas, age is not the only factor in determining the performance of a ship. These destroyers were designed and built under wartime conditions by a navy that was still learning the ropes of modern naval warfare. As such, their design and outfitting were probably not appropriate for service as WW2 North Atlantic convoy escorts battling Type VII U-boats. Performance-wise, the ships were "wet" and lacked the maneuverability, weapon systems, and sensors required of a WW2 anti-submarine vessel. To a large extent, this is to be expected because they were designed for a different time with different threats.

Corrosion was also another major issue with these ships. Most of these destroyers had been put into the US Navy's Reserve Fleet,  which is often called the "Mothball Fleet." Ideally, ships in the Reserve Fleet are treated to prevent corrosion. However, there were signs that these ships were not properly mothballed and many suffered from severe corrosion issues. These issues had to be resolved before the ships could go back on active duty. For more details on these issues, see this article.

To be fair, many of these ships were refit and used effectively by the US Navy in WW2. However, the UK was facing an existential threat from U-boats and they needed convoy escorts in a hurry. The time required to repair and refit the ships would be an obstacle to their defense of the UK. The following quote from Winston Churchill in a cable to Roosevelt in late 1940 sums up his opinion of the ships and their issues.

We have so far only been able to bring a few of your fifty destroyers into action on account of the many defects which they naturally develop when exposed to Atlantic weather after having been laid up so long.

The deal was important because it provided President Roosevelt a way to help the UK without raising the ire of the isolationist elements in the US. Once it was clear that the destroyers had issues, President Roosevelt transferred 10 Lake Class Coast Guard Cutters to the Royal Navy as a gesture of good faith. The Lake Class ships were 10 years newer and their design was more suited to ASW operations.

The rest of this post will discuss the 50 destroyers in the deal along with the other ships of the same classes. This is a tale of two nations, US and UK, trying to make use of whatever assets were available to give themselves time to build more modern ships.

For those who like to follow along, my Excel workbook is available here. All of the data is from Wikipedia.


Figure M: Commissioning Dates of US Battleships at the Start of WW2.

Figure 2: Commissioning Dates of US Battleships at the Start of WW2.

The 50 destroyers were built by the US during a 273 destroyer building program that started during WW1 (1916) and finished in 1922. By the time of WW2, all but ~30 of these ships ended up in Reserve Fleet.

While these destroyers are frequently described as old, understand that much of the early WW2 naval fighting was done using WW1-era ships. For example, seven out of nine US battleships at the start of WW2 were built during or shortly after WW1 (Figure 2). Of these old battleships, two were permanently lost during the attack on Pearl Harbor (USS Oklahoma and USS Arizona).

The destroyers traded to the UK were from three classes: Caldwell, Clemson, and Wickes. The Royal Navy referred to these ships as the Town Class. Table 1 shows that most of the ships of these classes served in WW2.

Table 1: Total Number of Caldwell, Clemson, Wickes-class Destroyers built.

As is shown in Table 2, the Clemson, Caldwell, and Wickes-class ships were very similar in terms of their general characteristics.

Table 2: Key Characteristics of Deal Destroyers.

I looked at the range of Royal Navy ship displacements for classes launched after 1935 (Table 3). The Caldwell, Clemson, and Wickes-classes are displacement-wise most similar in displacement to the Black Swan sloop or G-class Destroyer-classes.

Table 3: Displacement Range of Royal Navy Classes Launched Post-1935.


Notable Ships of the Clemson and Wickes Classes

Some of these ships did give notable service:

  • USS Ward (Wickes-Class Destroyer)
    Fired the first shots against a Japanese midget submarine entering Pearl Harbor just before the attack.
  • USS Reuben James (Clemson-Class Destroyer)
    The first US warship sunk by hostile action in the European theater during WW2 by the Germans. The sinking occurred on 31-October-1941, less than two months before the Pearl Harbor attack.
  • USS Buchanan (Wickes-Class Destroyer), renamed HMS Campbeltown
    The UK turned this ship into a surface running torpedo during the St. Nazaire Raid. The use of a surface ship as a torpedo reminds me of the boat African Queen, which starred in the movie of the same name.

Survival in US and UK Service

Table 4 shows the number of losses and survivors of these three classes, with about 16% of the ships being lost for all causes during WW2. I defined losses to include ships that ran aground or ended up being used as target ships;

Table 4: WW2 Survivors and Losses In the Destroyer Deal Classes.

Table 5 shows the losses of the three classes while in UK service (or those countries who the UK transferred them to), which ended up being about 20% of the ships were lost.

Table 5: Survivors and Losses in UK service.

While the 50 destroyers were traded to the UK, a number of them ended up in Canadian and USSR service (Table 6).

Table 6: Country whose Navy the Destroyers Ended Up.


The ships were not the best, but they were all President Roosevelt could provide at the time and still ensure his re-election in 1940. And this deal provided a small start on what would eventually become the Atlantic Charter, a cornerstone of US and UK cooperation during WW2.

Posted in Excel, History Through Spreadsheets, Military History, Naval History | 1 Comment

BB Ballistic Coefficients

Quote of the Day

A serious problem in planning against American doctrine is that the Americans do not read their manuals, nor do they feel any obligation to follow their doctrine.

— Soviet observation during the Cold War (link).

Figure 1: Steel BBs with copper or zinc jackets. (Wikipedia)

Figure 1: Steel BBs with copper or zinc jackets. (Wikipedia)

A number of years ago, I was asked by a father to assist him and his son with a science project that involved calculating the ballistic coefficient of a BB gun projectile. I provide this father-son duo with the required calculations (documented here) and the answer I obtained seemed reasonable.

Another reader has recently asked further questions on these calculations and I thought I would take a quick look at how the ballistic coefficient of various BBs varies from one manufacturer to another.

This was a quick task that involves scraping some BB data from a web page (here), parse a bit of HTML, and plotting the data. The task is straight forward and the calculations are documented in this Excel workbook (here). This task was a good exercise in using Power Query and Excel to extract data from a web page without tables.

Figure 2 shows the distribution of BBs documented on the previously mentioned web page. My earlier calculations, assuming nominal BB parameters, showed the ballistic coefficient to be ~0.0145, which is near the mode of the chart in Figure 2.

Figure 2: Distribution of BB Ballistic Coefficients from 80 different BBs.

Figure 2: Distribution of BB Ballistic Coefficients from 80 different BBs.

Posted in Ballistics, Excel | Comments Off on BB Ballistic Coefficients