Our House Design Process

 

Quote of the Day

Find out who you are and do it on purpose.

Dolly Parton


Introduction

 

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.

Design

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

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 | Leave a comment

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 | 4 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


Introduction

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.

Background

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.

Analysis

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)
    =14-Mar-2021

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 | 3 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).

Background

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.

Analysis

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 | Leave a comment

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.


Introduction

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.

Background

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.

Analysis

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.

Conclusion

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


Introduction

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.

Background

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.

Analysis

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.

Conclusion

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 | Leave a comment

Optimized Piecewise Linear Model Using Excel

 

Quote of the Day

Never respond too quickly or too well to a request that you do not want to see more of in the future.

— A truism of corporate life. This is my paraphrase of a statement by a coworker, Nick Priote, who often made very insightful observations on the corporate world vs academia.


Introduction

Figure 1: Typical Li-Ion SOC vs Voltage Curve (Reference).

Figure 1: Typical Li-Ion SOC vs Voltage Curve for a specific discharge current (Reference).

I was recently asked to create a piecewise linear model for a rather complex battery discharge curve, which is a type of task that I have performed dozens of times. I was told to perform this task in Excel because that is the only computation tool that this customer uses. I normally do this task in R because I like the segmented package, however, Excel does a very good job with the task, especially if you use the Solver add-in to "tune" the model.

Figure 1 is the kind of curve I normally get from our test battery folks, which is the open-circuit battery voltage (VOC) vs State of Charge (SOC). The software folks, my customers for this modeling effort, typically know VOC and want to know SOC, so they care about the SOC vs VOC curve. This requires that you flip the abscissa and ordinate values, which is a minor transformation.

Because the actual battery data is proprietary, I will create a piecewise-linear approximation to a discharge curve I found on the web (Figure 1). For those who like to follow along, my workbook is available here.

Background

Definitions

Breakpoint (Knee Point)
The point on the curve where the approximation transitions from one line segment to another. Because we need continuity, the line segments must have the same value at the breakpoint.
Objective Function
This is the error measure that our algorithm will be minimizing.
Solver
Solver is an add-in for Excel. This is an unbelievably powerful tool that many folks do not even know exists. The Solver that is included in Excel is a reduced functionality version of a more extensive package from Frontline Systems. There is also an open-source version (link) under development.

Optimization

There are a number of different optimization criteria we could apply to this problem, but for this exercise, I will be minimizing the maximum absolute error. You could easily change the algorithm to minimize the sum of the least square errors or a weighted error approach.

In the real battery problem (not shown in the workbook), I actually weighted the battery capacity errors more heavily when the battery state of charge was low versus when the state of charge was high. This is because people generally want more accuracy when their battery is nearly out than when it is full. I have done similar optimizations for aircraft fuel systems where customers are less concerned with accuracy when the tanks are full than when they are near empty. This is yet another manifestation of the utility curve from economics – stuff has increased value when you are short of it.

Analysis

Setup

For this example, I am going to use five line segments to approximate a cubic function. For the actual problem that I worked, my measured battery curve looked nothing like a cubic function, but the process for determining the piecewise linear approximation is the same.

Here is the process I used (there are many possible alternatives):

  • Assume the State Of Charge (SOC) and battery voltages are known at the end points of battery voltage range: (VOC=2.8 V, SOC=0%), (VOC=3.5 V, SOC = 100%).
  • Pick three points (voltages) in the middle of the VOC range (2.8 V to 3.5 V).
  • Determine lines between each of these points
  • Setup a data table with a large number of points that encompass the whole VOC range.
  • Use the data table to compute a SOC value for every VOC point.
  • Determine the error between the model SOC value and measured SOC value (some interpolation will be needed for the measured SOC values).
  • Compute the maximum absolute error of the absolute differences between the model SOC values and measured SOC values.
  • Use Solver to select (VOC, SOC) values to minimize the absolute errors. The initial guess must be fairly close to the final values to ensure a good approximation.

Optimization

See the workbook to see how the optimization is setup. One issue with this approach is that your starting values must be good for Solver to converge to a good solution.

Result

Figure 2 shows the battery curve of Figure 1 and my interpolated results. Note that the curve of Figure 1 shows VOC vs SOC and Figure 2 shows SOC vs VOC, which is what the firmware folks I work with need. All I needed to do was flip the abscissa and ordinate values.

Conclusion

Generating piecewise linear approximations for non-linear curves is a very common task. In my career, about 90% of this work has involved batteries (always nonlinear) or sensors (frequently nonlinear).

One frequent problem is that the SOC vs VOC curve is frequently not a one-to-one function. This means that the VOC does not map to a unique SOC value. This makes SOC determination much more difficult, which means it will be a topic for another post.

Posted in Batteries, Excel | Leave a comment

US WW2 Torpedo Production Chart Using Power Query

 

Quote of the Day

Satisfaction is always available. It is just not always looked for. If, when you enter any experience, you enter with curiosity, respect and interest you will emerge enriched and with awareness you have been enriched. Awareness of enrichment is what satisfaction is.

— Ann Hastings, Author of The Mechanics of Reality: The Teachings of Vywamus Channeled.


Introduction

Figure 1: US Production of Torpedoes in WW2. (Source)

Figure 1: US Production of Torpedoes in WW2. (Wikipedia)

During my readings on the Pacific War, I often see the chart shown in Figure 1. I decided to do a bit of digging and find the source data for this chart in the hope of making a version of this chart that is a bit clearer and easier to use.

My go-to source for munitions production data is the report Official Munitions Production of the United States By Months, July 1, 1940-August 31, 1945 (link). Fortunately, this report has a table of US torpedo production data, but simply plotting the data did not produce Figure 1. However, I was able to reproduce Figure 1 by doing a bit of data filtering.

This post documents my process for gathering the data, cleaning it, filtering it, and generating a cleaner and easier-to-use version of this chart. My workbook is here.

Background

Figure 1 only shows the production of anti-surface ship torpedoes, which were grouped according to the platform they were designed to be launched from:

  • Submarine-Launched: Torpedo Mk 14, 18, and 23
  • Aircraft-Launched: Torpedo Mk 13
  • Surface Ship-Launched: Torpedo Mk 15

There were other torpedoes produced by the US during WW2 but they are not included in the chart. For example, the Torpedo Mk 24 ("FIDO") was an acoustic homing torpedo used primarily to destroy submarines.

Many older anti-surface ship torpedo versions were used during the war but were not being manufactured at that time. For example, the Bliss–Leavitt Mark 8 torpedo was used extensively by PT boats. Wikipedia has a table that summarizes the US torpedo versions.

Analysis

Data Processing

The basic data extraction process was simple:

    • OCR the torpedo production table from the Offical Munitions Production document.
    • Put the OCR data into Excel
    • Import the data into Power Query
      • This is a complex table with a multi-level heading, so there was a fair amount of heading manipulation involved.
      • Convert the table to tidy format
      • Filter the data
        • The data contains some UK torpedoes that the US made but did not chart.
        • The data contains a small number of US Torpedo Mk 14 Mod 4, which I can find no record of it being deployed nor did it appear to be included in the chart.
    • Generate a pivot table of the data
    • Generate a chart of the pivot table.

Charting

Figure 2 shows my rendering of the data. I did change the chart format just a bit by adding production numbers to each column of the chart and removing the gridlines, which makes things a bit clearer for me.

Figure 2: US Torpedo Production in WW2.

Figure 2: US Torpedo Production in WW2.

Conclusion

My goal was to reproduce the chart shown in Figure 1, which I have done.

Posted in Excel, History Through Spreadsheets, Military History, Naval History | Leave a comment

18% of American's Can Determine 52 Senators?

 

Quote of the Day

The fastest way to teach someone nothing is to try telling them everything.

— Susanna L. Harris on Twitter, quoting her plumber explaining why her pipes were making noise. His quote is true for all science communication.


Introduction

Figure 1: 26 States with the Least Population (mapchart.net).

Figure 1: 26 States with the Least Population (mapchart.net).

I was listening to a podcast this week where I heard James Carville state that "18% of American's can determine 52 senators." I thought this was an interesting quote that I could have the students I tutor verify using Excel and Power Query. All of the data is available online and the problem has a relatively short solution.

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

Background

The only background information that you need to verify this statement is that every state has two senators. To ensure that I have complete population data, I used 2019 census data.

Analysis

Approach

I will verify Carville's statement using the following process:

  • Download a list of US states with their populations into Power Query.
  • Insert the total population of the US  for computing population percentages (this number includes the District of Columbia and territories that do not have senators).
  • Convert the populations into percentages of the whole.
  • Sort the list in ascending order of population percentage
  • Look at the population percentage for the least populated 26 states = 52 senators

Results

Figure 2 shows my spreadsheet results, which show that the 26 least populous states having 17.6% of the US population could determine control of the US Senate if they all voted the same way.

Figure 2: List of US States Sorted By Population.

Figure 2: List of US States Sorted By Population.

Posted in Civics Through Spreadsheets, Excel | 1 Comment