Estimating the Lithium Content of a Lithium Battery

 

Quote of the Day

The ideal subject of totalitarian rule is not the convinced Nazi or the dedicated communist, but people for whom the distinction between fact and fiction, true and false, no longer exist.

Hannah Arendt


Introduction

Figure 1: Lithium on the Periodic Chart.

Figure 1: Lithium on the Periodic Chart. (Source)

Most of the products that I work on are powered by lithium batteries. Lithium batteries are popular today because they have excellent energy density but there are safety concerns with using them because there have been issues with battery fires. These fires have caused the shipping industry to impose special labeling and packaging information on their transport. I recently have needed to consider shipping batteries on airplanes, so I have been looking at the International Air Transport Association (IATA) shipment guidance for lithium-ion batteries.  These rules require knowing the amount of total amount of lithium mass present in a lithium-ion battery. This is not a number that is easy to get from the manufacturers, though I do have a number from one vendor.

As usual, I started by googling for a way to estimate the amount of lithium in a battery. I soon came upon a formula used by FedEx (Figure 2).

Figure 1: FedEx Guideline for Calculating Lithium Content in a Lithium Battery.

Figure 2: FedEx Guideline for Calculating Lithium Content in a Lithium Battery. (Source)

I decided that I needed to derive this relationship to understand it, which is the topic of this post. For the rest of this post, I will be working with a single cell. This post will focus on the amount of lithium in a single cell; a battery is just a bunch of cells in a serial and parallel configuration.

Background

A Little Chemistry

There are many different lithium-ion chemistries. A common one uses lithium cobalt oxide and Equation 1 shows the chemical formula for this discharge reaction. Observe that 1 electron is transferred for each atom of lithium reacted.

Eq. 1 \displaystyle Li++{{e}^{-}}+LiCoO2->Li2O+CoO

Equation 1 tells us that one mole of lithium will product 1 mole of electrons.

Alternate Forms of This Relationship

I actually found a number of different relationships online. While their units were all different, they are based on the same principles. One assumption I have made is that the nominal lithium cell voltage is 3.3 V, which allows me to convert between Amp-hours (A-hr) and Watt-hours (W-hr).

Eq. 2 \displaystyle 8\cdot \text{gm}\approx 100\cdot \text{W}\cdot \text{hr}
Eq. 3 \displaystyle 1\cdot \text{gm}\approx 4000\cdot \text{mA}\cdot \text{hr}
Eq. 4 \displaystyle \text{ 0}\text{.3}\cdot \text{gm}\approx \text{1}\cdot \text{A}\cdot \text{hr }

Analysis

Derivation

Figure 2 shows how to derive Equations 2 through 4. I obtain the atomic mass of lithium from the periodic chart symbol shown in Figure 1.

Figure 2: Derivation of Lithium Content Rules of Thumb.

Figure 2: Derivation of Lithium Content Rules of Thumb.

Example with Known Lithium Content

I know the lithium content of one battery. Figure 3 shows a comparison between an equation from Figure 3 and the real value. At least for this case, approximation and reality are within 10%.

Figure M: Comparison of Approximateion with Actual Value.

Figure 3: Comparison of Formula Estimates with Actual Value.

Conclusion

I expect to be using the FedEx approximation in Figure 2 for some battery packaging design in the next few months.

Appendix A: Great Lithium-Ion Safety Infographic

Chemical & Engineering News has a done a superb job of summarizing the fire issues associated with Lithium-ion batteries (Figure 4). Please check out their website for the reference material used in creating it.

Figure M: Lithium-Ion Battery Infographic.

Figure 4: Lithium-Ion Battery Infographic. (Source)

Posted in Batteries, Rules of Thumb | Leave a comment

US Cruiser Production During WW2

 

Quote of the Day

If you want real joy, stop looking at yourself and see how you can help someone else.

Luke Mickelson (2018 CNN Hero). I have found the Benedictine principles of hospitality, stewardship, and service are key to my own happiness and sense of purpose. In particular, the focus of the Benedictines on having an attitude of gratitude is particularly important. This all meshes well with my stoic approach to life.


Introduction

Figure 1: USS Cleveland, which was the lead ship of the most produced US cruiser class of WW2.

Figure 1: USS Cleveland, which was the lead ship of the most produced US cruiser class of WW2. (Wikipedia)

I have been working through the book Collect, Combine and Transform Data Using Power Query in Excel and Power BI by Gil Raviv – it is an excellent Power Query (PQ) resource. I particularly like the methods discussed in Chapter 10, which focused on how to make your queries robust, that is, insensitive to minor deviations in the input data. Chapter 10 spoke to me, and I immediately began looking for some practice data that suffered from common inconsistencies: headings in different cases, minor spelling errors in the data body, and inconsistent wording (example, "Co." instead of "Company"). I found that data in the Wikipedia's information on US WW2 cruisers. In this post, I will look at the production of cruisers by the US during WW2. See Figure 1 for a typical example of a WW2 US light cruiser.

For those who are interested, my workbook is here.

Background

Analysis Approach

Gil presents a number of methods that I applied in this exercise:

  • Use standard PQ functions to ensure the headings were all in the lower, upper, or proper cases.
  • Create custom functions for processing files using a template file as the basis for your work.
  • Setup conversion tables that contain all the inconsistencies present in your data set and the consistent value you want to use. Merging will be used to perform the replacements.

My analysis approach was straightforward:

  • Grab the URLs of the Wikipedia pages with the cruiser data I wanted.
  • Tidy the data from one of those pages in a way that will work with all other pages (the hard part).
  • Make a function of that template.
  • Apply the function to all the different URLs.
  • Aggregate the data using pivot tables.
  • Plot what needs to be plotted.

What is a Cruiser?

I like this definition of a cruiser that I found on the GlobalSecurity.Org website.

By the mid-20th Century, cruisers were medium-sized, general-utility ships. They had a large cruising range and are capable of high speeds (over 30 knots). They served as protective screens against surface and air attacks and also provide gunfire support for land operations. Cruisers were lightly armored, heavily armed, fast ships designed to screen formations and to scout out enemy fleets. Their survivability depended on speed, not armor. This continued to be the meaning until after the Second World War - a fast, long-range, lightly armored ship, although by then more powerful than a destroyer.

Cruisers were further subdivided into three types:

Light Cruiser
For the US Navy under treaty limits, light cruisers mounted 6-inch main guns and had displacements of less than 10,000 tons.
Heavy Cruiser
For the US Navy under treaty limits, heavy cruisers mounted 8-inch main guns and had displacements of ~10,000 tons. Anything larger in terms of guns and displacement would have had them considered capital ships and subject to stringent naval arms control treaty regulations. After WW2, the US increased the displacement for heavy cruisers to 17,000 tons with the commissioning of the Des Moines-class.
Battlecruiser
This class of ship was poorly defined. They were similar in displacement, armament, and cost to battleships, but differed slightly in form and balance of attributes. Battlecruisers typically carried slightly thinner armor and a lighter main gun battery than contemporary battleships, installed on a longer hull with much higher engine power to attain faster speeds. For the US Navy, battlecruisers carried 12-inch main guns. The Alaska-class was comparable to the German's Scharnhorst-class battleships, which carried 11-inch guns.  (Wikipedia)

The cruiser mission changed throughout WW2. Early in the war, cruisers played a crucial surface combat role (for example, see the Battle of Savo Island). As the Imperial Japanese Navy's surface fleet became a shadow of its former self, US cruisers finished the war with carrier air defense and land bombardment as their key roles.

Analysis

Wikipedia List

I am basing my work here on the Wikipedia's list of US WW2-era cruisers. The Wikipedia list includes all cruisers that served in WW2 or were in construction during the conflict (Figure 2), which means that it includes some obsolete classes (example: Omaha-class) and some that were not commissioned until after the war (example: Juneau and Fargo-classes).

Figure 2: Wikipedia List of US WW2

Figure 2: Wikipedia List of US WW2 Cruiser Classes.

US Cruisers Commissioned During WW2

Between the attack on Pearl Harbor (7-Dec-41) and VJ Day (2-Nov-45), the US commissioned 47 cruisers in 4 different classes. Figures 3(a) and 3(b) show that most of the cruisers commissioned were light cruisers.

Figure 3(a): Number of US Light, Heavy, and Battle Cruisers Commissioned that Served During WW2. Figure 3(b): US Cruiser Classes Commissioned During WW2.

The Alaska-class battlecruisers were the bruisers of the bunch but came so late in the war that they had little impact. The reason the Alaska-class was so late was it was a new class with a new gun, and it just takes time to work through the teething problems. In other posts, I have applauded those US war planners that focused on producing war material that could be delivered to the front in time to make a difference – the Liberty ship and M4 Sherman tank are cases in point. The Alaska-class example should serve as a warning to those who are not careful about managing risk during development. For interesting forum discussions on the Alaska-class ships, look here and here.

US Rate of Cruiser Commissioning During WW2

Figure 4 shows the rate of the US cruiser commissioning during WW2. The US had been ramping up warship production starting in 1940 with the Two-Ocean Navy Act, which authorized the adding 257 ships to the US Navy. After the Pearl Harbor attack, warship production accelerated even further. However, warships take a long time to build. Figure 4 shows that the cruiser commissioning rate did not significantly increase for two years after Pearl Harbor.

Figure 2: US Cruiser Commissioning During WW2.

Figure 4: US Cruiser Commissioning During WW2.

Shipyard Statistics

Figure 5 shows the US shipyards tasked with building the new cruisers. These shipyards faced enormous challenges. While the US Navy had an immediate demand for a massive number of warships, the shipyards faced difficulties with finding the materials and skilled workers that were needed to fulfill the wartime need

Figure 5: US Shipyards Producing Cruisers During WW2.

Figure 5: US Shipyards Producing Cruisers During WW2.

The William Cramp & Sons shipyard is an example as to the difficulties in converting a civilian shipyard to warship production; it had abysmal keel laid-to-commission durations. Their submarine build times were similarly bad.

Conclusion

Analyzing US WW2 cruiser production was a good training exercise for the methods that Gil Raviv shows in Chapter 10 of his PQ book. I have to say that I have learned a lot.

What can we learn about WW2 from the data? This post did not look at the fates of these warships after VJ day, but I could not escape seeing that so many ships were scrapped shortly after the war ended. The US left WW2 with the largest navy in the world, but it was a navy perfectly designed to defeat the Imperial Japanese Navy – an opponent that no longer existed.

The war created technological changes that affected every aspect of the US Navy:

  • Aircraft carriers needed to be redesigned to support jet aircraft.
  • US diesel-electric submarines were rendered obsolete first by the German Type XXI boats and shortly after that by nuclear boats.
  • Battleships were rendered obsolete by their big guns being outranged by aircraft launched from aircraft carriers.
  • Big gun cruisers were rendered obsolete as carriers and their air wings became the dominant ship killers in surface battles. Gun battles between big gun warships became a feature of an earlier time. Cruisers needed to focus more on air defense for the aircraft carriers.
  • The US and Canada manufactured ~300K propeller-driven aircraft during WW2. Nearly all were obsolete at the end of the war.
  • Jet aircraft rendered anti-aircraft artillery ineffective – the focus would shift to missile-armed vessels.

Whole new classes of warships would be needed going forward to face the threats in the Cold War.

Posted in History Through Spreadsheets, Naval History | 2 Comments

US Submarine Production During WW2

 

Quote of the Day

Those that travel the high road of humility in Washington are not bothered by heavy traffic.

— Sen. Alan Simpson


Figure 1: USS Balao, lead ship of the most numerous class of US WW2 Submarines.

Figure 1: USS Balao, lead ship of the most numerous class of US WW2 Submarines.

I recently purchased the book Collect, Combine and Transform Data Using Power Query in Excel and Power BI by Gil Raviv and am learning a lot – so much so that I am motivated to go hunt some additional data examples for processing by Power Query.

One WW2 topic that continues to intrigue me was how US war planners kept the Imperial Japanese Navy (IJN) at bay long enough to build a large naval force. The key was the use of submarines for commerce raiding to disrupt the war material supply chain and tie down Japanese surface forces with convoy defense duty. This post will use Power Query to scrape the Wikipedia for this data. The Wikipedia is becoming a wonderful source for WW2 information.

Figure 2: US Submarine Classes During WW2.

Figure 2: US Submarine Classes Commissioned During WW2 (Dec 7, 1941 – Sep 2, 1945).

Early in the Pacific War, the US Navy could only project power into Japanese waters on a sustained basis by using its submarines. The early actions were often conducted by smaller boats (for example, S-boats) that were inadequate for a theater as large as the Pacific. To paraphrase Donald Rumsfeld, you go to war with the Navy you have – not the Navy you might wish you have. Fortunately, the US had begun building its larger and more capable fleet submarines just before WW2. Figure 2 shows the three classes of fleet submarines that were commissioned during WW2: Gato, Balao, and Tench (in order of introduction).

The Wikipedia maintains a list of US WW2 submarines. I will use this list of submarines to access data from the Wikipedia page for the individual boats. I will filter the data to focu only on fleet submarines commissioned during the war. Some folks may disagree with individual boats on the Wikipedia list. For example, the Wikipedia does not list the USS Mero as a US WW2 submarine, but it technically was commissioned during WW2 so late in the war that it never entered combat. However, their approach is defensible and does not have a significant effect on my results.

I will access the information using the same approach as in this earlier  post:

  • create a list of WW2 submarine web pages
  • access each page and extract the relevant information
  • generate pivot tables for the required reports

I followed this procedure to generate a list of submarines commissioned during the war, their shipyard, and the time between keels being laid and submarines being commissioned, which I call the build time. Figure 3 when keels were laid and when boats were commissioned. Note how the number of keels laid dropped precipitously after August 1944. By that point, the IJN was on the ropes and US Navy surface units were in Japanese waters. The war planners saw that more submarines were not needed at that point.

Figure 3: Dates of Submarine Keel Laying Versus Commissioning.

Figure 3: Dates of Submarine Keel Laying Versus Commissioning.

Figure 4 shows the median build times by class. When you consider that the US role in WW2 lasted 44 months, build times of 9 to 12 months were significant, especially when given that it takes additional months for a commissioned boat to get into combat.

Figure 4: Median Build Times By Class.

Figure 4: Median Build Times By Class.

Submarines are very difficult to build and the shipyards must have specialized skills, like for the welding of thick steel pressure hulls. There were five shipyards that commissioned fleet boats during WW2 (Figure 5). The Manitowoc yard is on Lake Michigan and had been building ferries and ore haulers prior to the war. While Manitowoc had no submarine building experience, it was able to develop these skills under war-time conditions. I find this an impressive accomplishment. Manitowoc-built subs performed well during the war; one, USS Rasher, had the third-highest sunk tonnage total.

Figure 4: US Submarine Shipyards During WW2.

Figure 5: US Submarine Shipyards During WW2.

If you are interested in how the data was gathered and analyzed, my spreadsheet is here.

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

Calculating the Pointing Angle for My Television Antenna

 

Quote of the Day

If I strive for perfection, I can generally achieve good'nuff. If I strive for good'nuff, I generally achieve firewood.

— Woodworkers Credo. I find this quote holds true for so many things. I used to work for an HP engineer, Warren Pratt, who said similar things about designing printed circuit boards. Basically, you must strive for perfection because things will go wrong that you cannot anticipate. So nothing will be perfect, but you might get something worthwhile.


Figure 1: Television Stations Closest to My Cabin.

Figure 1: Television Stations Closest to My Cabin. (Source)

I spend quite a bit of time at a cabin I have built in northern Minnesota. Technically, I spend most of my time in the garage on the site and I have decided that I need to be able to watch the local television stations in Duluth. These stations are ~75 miles away and I need to determine the bearing along which to point my antenna. This seemed like a good Excel exercise that I can also use as an example for those I tutor at the Hennepin County Library. There are web calculators available that perform this calculation (example), but it is more fun doing it myself.

Once I decided that I needed an antenna, there are two numbers I in order to purchase the correct unit and to point it properly:

  • Distance to the Stations
    Distance determines the type of antenna that I will need.
  • Bearing of the Stations Relative to North
    I will be using a compass to point the antenna and I need an angle from my garage to Duluth.

Equation 1 contains the formulas needed for computing the range and bearing between two points on Earth given their latitudes and longitudes. For more details on these formulas, see this discussion on the haversine formula, which can be derived using spherical trigonometry.

Eq. 1

where

  • θ is the bearing to the antenna from the garage
  • R is the distance to the television antenna.
  • ϕlat is the latitude of the garage
  • ϕlon is the longitude of the garage
  • ψlat is the latitude of the television station antenna
  • ψlon is the longitude of the television station antenna

I implemented the equation in an Excel spreadsheet, which you can download here.  Based on my range calculation, I ended up buying a Yagi-Uda television antenna (Figure 2).

Figure 2: My new television antenna.

Figure 2: My new television antenna.

Posted in Electronics, Excel | Leave a comment

Medal of Honor Statistics Using Power Query

 

Quote of the Day

Their drills are bloodless battles, and their battles bloody drills.

Publius Flavius Vegetius Renatus, Roman writer on the Roman military. An older version of "train like you fight, fight like you train."


Figure 1: US Air Force Medal of Honor.

Figure 1: US Air Force Medal of Honor. (Source)

In this post, I will use Power Query (PQ) to gather (aka web scrape) the US Medal of Honor (MOH) recipient names, rank, service, and conflict from a website called the Congressional Medal of Honor Society (CMOHS), which states that there have been  3505 MOH recipients. They have a beautiful website with the records stored on 140 ages with 25 names per page and one page with 5 names. Normally, I would use Python to scrape a large number of web pages but I am trying to use PQ more because my customers all have Excel. My approach as a contractor is always to work within the existing infrastructure of my customers. A copy of my workbook is available here.

I should mention that the Wikipedia states there have been 3522 MOH recipients. I do not know the reason for the discrepancy, but I do know that I gathered every MOH recipient from the CMOHS pages.

The web scraping process was straightforward:

  • Create a list of all web pages with MOH recipients.
    This was easier than you might think. There were 141 pages, each with the base URL and an appended number (1 through 141). You can see the format in the attached workbook.
  • Build a parsing template by parsing one webpage
    This was a little harder than you might think. PQ could not determine how to identify the information on the web page because it was not table-oriented. However, I took a standard web query and modified it by converting a WebPage command into a WebContents command –  it loaded in the raw HTML. I then parsed the HTML to extract the information. I illustrate the process in Figure 2, a figure which is large and you must click on it to view. The HTML parsing was routine and I just refer you to the query to see how I did it.

    Figure 2: Grabbing HTML..

    Figure 2: Grabbing HTML..

  • Turn the single-page parsing routine into a function
    This is a routine process. You add a function header (aka signature) and a closing phrase. I illustrate the process in Figure 3.

    Figure 3: Turn a Query Into a Function.

    Figure 3: Turn a Query Into a Function.

  • Load the list of web page addresses into PQ
    Make the list of web page addresses into a table and load it into PQ.
  • Run the function over every web address, producing a column of tables.
    I used the "Invoke Custom Function" command from the menu to create a new column with the parsed data. I illustrate the process in Figure 4.

    Figure 4: Calling the Parsing Function.

    Figure 4: Calling the Parsing Function.

  • Expand the column of tables into its separate fields.
    You now have all the MOH recipients. At this point, I can begin filtering and pivoting.

I know it sounds complicated but it is simple when you see it in the spreadsheet.

Now that we have the data, let's see what we can learn. I decided to focus on WW2 and later conflicts. I wanted to know the number of MOH recipients by:

  • conflict
  • service
  • rank

I now could generate the pivot tables that I wanted (Figures 5 through 7).

Figure 1: Number of MOH Recipients By Conflict.

Figure 5: Number of MOH Recipients By Conflict.

Figure 1: Number of MOH Recipients By Conflict.Figure 1: Number of MOH Recipients By Conflict.

Figure 6: Number of MOH Recipients By Service.

Figure 3: Number of MOH Recipients By Rank (Top 10).

Figure 7: Number of MOH Recipients By Rank (Top 10).

The results make sense:

  • WW2 was such a massive conflict that the number of recipients should be larger.
  • The US Army is the largest of the US military services, so it would make sense for it to have the most recipients.
  • Enlisted ranks would be more likely to be in the life and death situations that would call for such acts of bravery.
Posted in Excel | 3 Comments

Computing the Number of Friday the 13ths in a Year Using Excel

 

Quote of the Day

What is our intention for this meeting? What’s important? What matters?

Oprah Winfrey. She starts every meeting with these three questions.


Figure 1: Number of Friday the 13ths By Year.

Figure 1: Number of Friday the 13ths By Year.

I have been tutoring math at the local library and using Excel as a vehicle for encouraging people to explore everyday math. While at the library, I heard a young man ask "How many Friday the 13ths are in a year?" Since I am always looking for computational examples, I showed him how to use Excel to find the answer for himself. This post shows how I taught him to solve the problem. The solution turned out to be a good example of using Excel's date and array capabilities.

Figure 1 shows a simple table with the number of Friday the 13ths by year. I deliberately made the table 28 years per column to show that calendars repeat with a period of 28 years.  I use this characteristic of calendars to buy old calendars that I can reuse; I love old Christmas and tool company calendars.

Figure 2 shows the Excel array formula I used to compute the number of Friday the 13ths. My workbook is here.

Figure 2: Formula Breakdown for Calculating the Number of Friday the 13ths in a Year.

Figure 2: Formula Breakdown for Calculating the Number of Friday the 13ths in a Year.

Posted in Excel | 3 Comments

Excel VBA Code to Center a Shape in a Cell

 

Quote of the Day

The worst pain a man can suffer is to have insight into much and power over nothing.

Herodotus. This quote reminds me a bit of the curse of Cassandra.


Figure 1: Animated GIF Showing Operation of VBA Shape Centering Macro.

Figure 1: Animated GIF Showing Operation of VBA Shape Centering Macro.

I recently finished a job where the customer wanted an Excel dashboard that displayed metrics for test case completion and various success metrics. This dashboard contained many control shapes that I wanted to be centered in cells. I do not like to manually adjust objects so I googled for a VBA routine that would center a shape. I soon found a nice piece of code by HipGecko on the Mr. Excel forum that centered pictures in the active cell. A simple modification of this code allows it to center shapes, an object type that includes pictures and controls.

Figure 1 shows an animated GIF of the macro centering a flower picture a button shape in two different cells. The code will center the object in the cell if the upper-left-hand corner of the object is in the cell. If multiple shapes are in the cell, the code will center all the shapes on top of one another.

You can download a workbook with the code here. The source is shown below.

Posted in Excel | Leave a comment

Tensions and Angles in a Simple Rope Rigging

 

Quote of the Day

Once you get to earth orbit, you’re halfway to anywhere in the solar system.

Robert A. Heinlein


Figure 1: Great Rope Tension Infographic.

Figure 1: Great Rope Tension Infographic. (Source)

I have been tutoring math and physics at the local library for the last few months. As part of this tutoring, I have been looking for good graphics that illustrate basic science concepts. One common high-school physics problem involves computing the tension in ropes tied to an anchor by a pulley. Figure 1 is a graphic that nicely illustrates the tension between two ropes connected to an anchor point by a carabiner.

In this post, I will show how to derive a couple of formulas for the various angles and forces shown in Figure 1. The derivation assumes that there is no friction associated with the carabiner, which is not true. This idealization would be better if the carabiner was replaced with a pulley.

I find infographics like this useful because I frequently use ropes, pulleys, and winches at my cabin to perform tasks like removing tree stumps, pulling docks out of the water, and helping folks whose cars are stuck in the mud.

Figure 2 shows a free body diagram of the rigging and my derivation of the angles and forces involved.

Figure 2: Free Body Diagram and Derivation.

Figure 2: Free Body Diagram and Derivation.

I created an Excel Workbook that computes the values shown in Figure 3. It also computes the angle of the gray rope in Figure 1, which is not shown in that diagram.

Figure 3: Excel Table of Rope Tensions and Angles.

Figure 3: Excel Table of Rope Tensions and Angles.

In Figure 2, I derived F=T\cdot \frac{{\sin \left( \theta \right)}}{{\sin \left( \varphi \right)}}. This equation is not defined when φ = 0. You can circumvent this discontinuity using one of three techniques:

  • Assume that φ is very small rather than 0. The classic approach for an engineer and the one I typically use.
  • Derive an alternate expression that removes the discontinuity, which is shown in Figure 4.
  • Just eliminate φ from the equation for F as shown in Figure 5.
Figure 4: Derivation of Alternate Solution.

Figure 4: Derivation of Alternate Solution without a Discontinuity at φ =0.

Figure 5: Formula Eliminating Φ.

Figure 5: Formula Eliminating Φ.

Posted in Cabin, Construction, Daily Math | 10 Comments

Measuring Countersink Angle Using Gage Balls

 

Quote of the Day

A wealth of information creates a poverty of attention.

Herbert Simon, economist


Figure 1: Example of a Countersink Specification.

Figure 1: Example of a Countersink Specification. (Source)

This post will cover how to measure a countersink angle using gage balls. Figure 1 shows how a countersink is normally specified on an engineering drawing. I frequently use countersinks in my wood and metal working hobbies. In addition, using gage balls to measure the countersink angle provides a good example of how to apply basic geometry concepts to a practical problem.  I use this example in my role as a volunteer adult math tutor at our local library.

Two gage balls of different diameters are used to measure the countersink angle (θ). Figure 2 shows the key variables involved: the diameter of ball 1 (B1), the diameter of ball 2 (B2), and the height difference when placed into the countersink (M). Figure 2 also presents the formula for the countersink angle θ.  The derivation requires only to apply the definition of the sine of a right triangle.

Figure 2: Illustration Defining the Variables Used in This Derivation.

Figure 2: Illustration Defining the Variables Used in This Derivation.

Figure 3 shows a scale drawing of a countersink angle measurement example.

Figure 3: Countersink Angle Determination Example.

Figure 3: Countersink Angle Determination Example.

Figure 4 shows how I performed the calculations in Mathcad.

Figure 4: Example Calculations.

Figure 4: Example Calculations.

Posted in General Mathematics, Metrology | Leave a comment

US and Canada Aircraft Production During WW2

 

Quote of the Day

Followers will take on the personality and the character of the leader. As a leader, make sure you show the team your very best.

Mark Hertling. No truer words on management have ever been spoken. Every leader is the model for their team.


Figure 1: Consolidated B-24 Liberator was the Most Produced WW2 Heavy Bomber.

Figure 1: The Consolidated B-24 Liberator was the Most Produced WW2 Heavy Bomber. (Photo)

While looking for some good summer history reading, I found the book America's Hundred Thousand: U.S. Production Fighters of World War II. This book covers the production miracle associated with scaling up up the US aircraft industry to supply planes for every front during WW2. Its title refers to the fact that the US produced ~100K fighter aircraft during WW2, which lasted for 44 months for the US (Figure 2). I decided that I would look at the numbers for all forms of aircraft produced by the US during WW2. Fortunately, the Hyperwar website has put the Army Air Forces Statistical Digest online, which gives me easy access to the data. The Digest contains aircraft production data for both the US and Canada. Figure 1 shows the production numbers for the 11 categories of aircraft production listed in the Statistical Digest. In addition to 100K fighter aircraft, there were nearly 200K of other aircraft manufactured as well.

I should mention that different WW2 sources have somewhat different totals. Some databases show more than 100K fighters having been produced by the US and Canada during WW2.  This is because of a number of experimental and special purpose versions of aircraft being manufactured. The tables I chose in the Hyperwar database include a class of aircraft called "other" that includes these unusual units. Other databases eliminate the "other" category and include these aircraft in a related category.

Figure 1: Hyperwar Data on US Aircraft Production During WW2. (Source)

Figure 2: Hyperwar Data on US Aircraft Production During WW2. (Source)

The increase in the level of aircraft production is easy to see using Figure 3.

Figure 3: WW2 US and Canada Aircraft Production Numbers By Year.Figure 3: WW2 US and Canada Aircraft Production Numbers By Year.

Figure 3: WW2 US and Canada Aircraft Production Numbers By Year.

You can see the scope of the effort in the US by looking at the number of states where the production occurred – 24 states out of 48 were producing aircraft (Figure 4). I would have expected that California would dominate all the other states in terms of production because it was the prewar heart of US aircraft production. The table shows that other states, like New York and Kansas, also had significant production.

Figure 4: Aircraft Production By State Plus Canada.

Figure 4: Aircraft Production By State Plus Canada.

There were 53 companies making aircraft during WW2, plus thousands of subcontractors. Figure 5 shows the top 15. Notice how Ford is in the top 15 of aircraft manufacturers. During WW2, they set up an assembly line for B-24 bombers at Willow Run. At one point, 70% of the B-24 production was from Willow Run.

Figure 4: Top 12 Companies For Aircraft Production.

Figure 5: Top 15 Companies For Aircraft Production.

In Figure 6, I broke out the production for North American Aviation because it was so massive. Its portfolio included iconic aircraft like the P-51, B-25, and B-24. I should mention that the A-36 was a ground attack version of the P-51, so you could argue that production of the AT-6 and P-51 were about equal.

Figure 6: Aircraft Produced By North American Aviation.

Figure 6: Aircraft Produced By North American Aviation During WW2.

The US and Canada produced nearly 300K airframes during WW2. Figure 7 shows the percentage breakdown by major category. While fighter planes may have dominated in quantity, the B-29 very heavy bomber dominated in terms of development cost. At $3 billion dollars, it was the single most expensive development program during WW2 – its cost beat the Manhattan project by $1 billion.

Figure 6: Aircraft Manufactured Percentages By Category.

Figure 7: Aircraft Manufactured Percentages By Category.

For those who are interested, my workbook is here.

Posted in History Through Spreadsheets | 5 Comments