Steinhart-Hart Thermistor Calibration in Excel Using Matrix Formulas


Quote of the Day

People don't care how much you know until they know how much you care.

Theodore Roosevelt


Figure 1: NTC Bead Thermistor. (Wikipedia - Ansgar Hellwig )

I had a job this week that required that I use the Steinhart-Hart equation for modeling a thermistor's resistance versus temperature relationship. The requirement was driven by the customer's need for high accuracy. Most thermistor applications do not demand high accuracy, but this application can tolerate no more than ±0.5 °C of error.  This means that I cannot use the β-based thermistor model, which in this application would have an error of more than ±2 °C. This page will show you how how to perform an efficient 3-point calibration using Excel and a bit of matrix math. As a side benefit, I am using this workbook as an example of matrix math in my Excel tutoring at a local library.


The Steinhart-Hart thermistor equation is shown in Equation 1.

Eq. 1 \displaystyle \frac{1}{T}=A+B\cdot \ln \left( R \right)+C\cdot \ln {{\left( R \right)}^{3}}


  • A, B, C are calibration parameters.
  • R is the thermistor resistance (Ω).
  • T is the thermistor temperature (K).

Because there are three unknowns, calibration consists of measuring the thermistor resistance at 3 temperatures and solving the linear system shown in Equation 2.

Eq. 2 \displaystyle \left[ {\begin{array}{*{20}{c}} 1 & {\ln \left( {{{R}_{1}}} \right)} & {\ln {{{\left( {{{R}_{1}}} \right)}}^{3}}} \\ 1 & {\ln \left( {{{R}_{2}}} \right)} & {\ln {{{\left( {{{R}_{2}}} \right)}}^{3}}} \\ 1 & {\ln \left( {{{R}_{3}}} \right)} & {\ln {{{\left( {{{R}_{3}}} \right)}}^{3}}} \end{array}} \right]\cdot \left[ {\begin{array}{*{20}{c}} A \\ B \\ C \end{array}} \right]=\left[ {\begin{array}{*{20}{c}} {\frac{1}{{{{T}_{1}}}}} \\ {\frac{1}{{{{T}_{2}}}}} \\ {\frac{1}{{{{T}_{3}}}}} \end{array}} \right]

Because my customer is Excel-focused, I solved this problem in Excel. The workbook is straightforward to use:

  • Adjust the columns "Temperature (°C)" and "Mfg Spec (Ω)" to match the manufacturer's thermistor specifications for temperature versus resistance.
  • Select the desired calibration temperatures. The resulting maximum error is displayed.
  • The spreadsheet calculates the A, B, and C coefficients needed for using this specific thermistor.

My spreadsheet is available here. The thermistor included in the spreadsheet was an arbitrary choice for use as an example.


Posted in Electronics | 2 Comments

Another Excel TEXTJOIN Example


Quote of the Day

If you haven't read hundreds of books, learning from others who went before you, you are functionally illiterate – you can't coach and you can't lead.

Jim Mattis, scholar general

Figure 1: Glass-Encased Fuse. (Wikipedia)

I have been analyzing seemingly random fuse failures in different products. The failing fuse is similar to the unit shown in Figure 1. My analysis has shown that the fuses are failing because of damage they are sustaining during the manufacturing process. In my analysis report, I was asked to list each product that experienced a fuse failure and to list how many days each product was in service before the fuse failure occurred. Because of the large number of products and failures, I wanted to find a table format that would contain the required information in a condensed format. Excel's TEXTJOIN function and pivot table feature solved my problem nicely. For confidentiality and presentation reasons, my example here will contain simulated failure data that is MUCH shorter than the actual data file.

Figure 2 shows an abbreviated version of my test results table that contains the RMA number, failed assembly name, and the number of days the assembly was in service. I appended a column to this table that contains the Excel function:

=TEXTJOIN(",",TRUE,IF(C17=[Assembly],[Days in Service],""))

This function creates a comma-separated values list of all the "Days in Service" values associated with the failed assemblies. I then used a pivot table to generate my final summary table. The entire process is shown in this Excel workbook, which contains a macro from the Contextures web site that turns off the filter arrows.

Figure 2: Illustration of My Table Transformation.

Posted in Excel | 1 Comment

Battleship Classes and Throw Weights


Quote of the Day

The only thing standing between you and your dreams is insomnia.

The Tweet of God (David Javerbaum)

Figure 1: USS West Virginia. She demonstrated some of the best battleship gunnery of the war at Surigao Straits. (Wikipedia)

I just finished reading The Battle of Surigao Strait by Anthony Tully, a battle that saw the final clash of battleships. For a battleship aficionado, the climax of the fight was the contest between two Japanese battleships and six US battleships, where five of the six US battleships had been sunk or heavily damaged during the Pearl Harbor attack – only the USS Mississippi had escaped the carnage of Pearl Harbor. These were old battleships (Table 1), with two having been commissioned during WW1 and the rest shortly after the WW1 ended.

When US folks think of WW2 battleships, they generally focus on the Iowa Class, probably because these ships survived the war and avoided the scrap heap. However, they did not begin deployment until 1944, which was after much of the tough surface combat had passed.

Table 1: US Battleships at Surigao Strait.

USS PennsylvaniaBB38Pennsylvania1916
USS MississippiBB41New Mexico1917
USS TennesseeBB43Tennessee1920
USS MarylandBB46Colorado1921
USS CaliforniaBB44Tennessee1921
USS West VirginiaBB48Colorado1923

Table 1 also shows us that there were four different classes of US battleship at Surigao Strait: New Mexico, Pennsylvania, Colorado, and Tennessee. This seemed like a lot and made me curious about the evolution of US battleships relative to other combatants.  Fortunately, Wikipedia has a great table of battleship throw weights (i.e, the weight of a broadside from the main guns).  I used Power Query to download and tidy the table, and R to plot the data.

Figure 2 shows the number of battleships classes in service with major combatants. Notice how the US had the largest number of battleship classes. My personal opinion is that the US had so many classes in operation because

  • It had a lot to learn about battleships during and after WW1 and the commissioning of new classes shows how the US was working hard to catch up.
  • The US operated the old battleships for the entire war because new classes of battleships would not be available until the latter part of the WW2.

Figure 2: Number of Battleship Classes During WW2 By Major Combatants.

An important metric for a battleship is the weight of a broadside.  As I read about the Battle of Surigao Strait, it became clear the rate of the broadsides was also import. To understand the broadside weight per minute, I multiplied the rate of fire by the weight of an individual broadside to find the weight of fire per minute.

Figure 3 shows the top ten weights of fire for the different WW2 battleship classes. The Yamato and US 16-inch gun classes clearly dominate this metric.

Figure 3: Weight of Fire Per Minute.

The weight of a broadside depends on the weight of an individual shell. WW2 saw battleships with a wide range of shell calibers (i.e., diameter in inches). I became curious as to how the shell weight varied by caliber. I also fit a cubic curve (Figure 3) to the data to show that shell weight is roughly related to the cube of the caliber, which follows from the dimensional scaling laws.

Figure 3: Battleship Shell Weights Versus Caliber.

My spreadsheet and R markdown document are included here.

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

Everest Climbers With No Supplemental Oxygen


Quote of the Day

It has been a long road...From a mountain coolie, a bearer of loads, to a wearer of a coat with rows of medals who is carried about in planes and worries about income tax.

Tenzing Norgay, the great Nepalese mountain climber.

Everest Summit Line

Figure 1: Line of Climbers Preparing to Summit Everest. (National Geographic)

I recently have seen pictures on the news of a line of people preparing to summit Mount Everest (Figure 1), which got me thinking about the difficulty of waiting in line under low-pressure conditions. The vast majority of the people who climb Everest use supplemental oxygen. The air pressure at the summit of Everest is about 0.3 atmosphere, which is not enough to support human life for an extended period of time. But a relatively small number of people have climbed Everest with No Supplemental Oxygen (NSO). In this post, I will look at this very select group of people.

Figure 2: Elizabeth Hawley. (Wikipedia)

There is a database of the Himalayan climbers that until recently was maintained by Elizabeth Hawley, a remarkable Chicago native who has documented the climbing efforts from the 1960s until shortly before her death in 2018. Her Microsoft Visual Foxpro 9 database is continuing to be updated. The database and code to access the data are online and can be downloaded here. I used the database, which contains climbing data for many mountains, and filtered it for Everest climbers. I then exported the data as a CSV so that I could import it into Rstudio (R_source). The database has not been updated yet for 2019 activity. The video shown in Figure 3 by Melissa Sue Arnot does a great job showing how climbers worked with Ms. Hawley to record their climbing efforts – Arnot's entire series of Youtube videos are worth your time.  Arnot made her own NSO summit in 2016.

Figure 3: Solid Video on Climbing Everest By a Real Pro.

Once in Rstudio, I filtered the data for successful summits that used no oxygen. The database records if a person used oxygen on the climb, descent, sleeping, or for medical purposes. I am looking for those who did not use any oxygen. By this strict standard, the database lists 163 successful NSO summits.

The first Everest NSO summits occurred in 1978 (Table 1).

Hans EnglW Germany
Peter HabelerAustria
Reinhold MessnerItaly
Ang DorjeNepal
Mingma NuruNepal

Table 2 shows the climbers with more than one NSO summit.  The most NSO summits by far were made by Ang Rita, who has made the trip 9 times including once in the winter – winter summits of any sort are very rare. Note that the Wikipedia credits him as having ten NSO summits, but the database lists him as using O2 while sleeping on his 1983 climb.

Table 3 shows the number of NSO summits by country – Nepal really dominates this statistic.

ClimberNumber of No O2 Climbs
Ang Rita9
Anatoli Boukreev3
Lobsang Jangbu3
Pemba Dorje3
Anatoli Moshnikov2
Ang Dorje2
Babu Chiri (Babu Tshering)2
Gheorghe Dijmarescu2
Lhakpa Dorje2
Marc Batard2
Mingma Dorje2
Nima Rita2
Reinhold Messner2
Silvio Mondinelli2
Timothy John Macartney-Snape2
Vladimir Sergeevich Balyberdin2
CountryNumber of Successful No O2 Ascents
Czech Republic5
New Zealand1
S Korea1
W Germany1

Table 4 contains the entire list of NSO summiteers.

1978Hans EnglM34W Germany
1978Peter HabelerM35Austria
1978Reinhold MessnerM33Italy
1978Ang Dorje M29Nepal
1978Mingma Nuru M0Nepal
1980Reinhold MessnerM35Italy
1982Vladimir Sergeevich BalyberdinM32USSR
1983Haruyuki EndoM26Japan
1983Haruichi KawamuraM35Japan
1983Lawrence Darius (Larry) NielsonM35USA
1984Zoltan DemjanM29Czechoslovakia
1984Timothy John Macartney-SnapeM28Australia
1984Gregory (Greg) MortimerM31Australia
1984Ang Dorje M35Nepal
1984Ang Rita M36Nepal
1985Ang Rita M36Nepal
1986Erhard LoretanM27Switzerland
1987Ang Rita M39Nepal
1988Marc BatardM36France
1988Lydia Margaret Bakewell BradeyF27New Zealand
1988Michel MetzgerM39France
1988Ang Rita M40Nepal
1988Nima Rita M30Nepal
1989Carlos Miguel Carsolio LarreaM27Mexico
1989Nima Rita M31Nepal
1989Nurbu Jangbu M35Nepal
1990Sergei Anatolievich ArsentievM31USSR
1990Marc BatardM38France
1990Grigori LuniakovM35USSR
1990Timothy John Macartney-SnapeM34Australia
1990Anatoli MoshnikovM37USSR
1990Ang Rita M41Nepal
1991Vladimir Sergeevich BalyberdinM42USSR
1991Battistino (Battista) BonaliM28Italy
1991Anatoli BoukreevM33USSR
1991Muneo NukitaM40Japan
1991Nima Dorje (Dorje) M36Nepal
1992Alberto Inurrategi IriarteM23Spain
1992Felix Inurrategi IriarteM25Spain
1992Mario Luciano PanzeriM28Italy
1992Ang Rita M43Nepal
1993Vladimir BachkirovM41Russia
1993Oscar Cadiach PuigM40Spain
1993Hubert GiotM39France
1993Eric GramondM30France
1993Michael Graeme GroomM33Australia
1993Ang Rita M44Nepal
1994Scott Eugene FischerM38USA
1994Ralph Robison (Rob) HessM34USA
1994Lobsang Jangbu M23Nepal
1995Marco BianchiM32Italy
1995Anatoli BoukreevM37Kazakhstan
1995Alison Jane HargreavesF33UK
1995Josef H. (Sepp) HindingM47Austria
1995Christian KuntnerM33Italy
1995Reinhard PatscheiderM38Italy
1995Ang Rita M46Nepal
1995Lhakpa Dorje M29Nepal
1995Lobsang Jangbu M24Nepal
1996Anatoli BoukreevM38Kazakhstan
1996Yuri Contreras CediM32Mexico
1996Sven Hougen GangdalM41Norway
1996Johann (Hans) KammerlanderM39Italy
1996Goran KroppM29Sweden
1996Jesus Martinez NovasM31Spain
1996Josef (Pepino) NezerkaM41Czech Republic
1996Ang Rita M47Nepal
1996Babu Chiri (Babu Tshering) M30Nepal
1996Kipa/Keepa M33Nepal
1996Lhakpa Dorje M30Nepal
1996Lobsang Jangbu M25Nepal
1996Ngatemba (Nga Temba) M40Nepal
1996Panuru (Pasang Nuru, Pa Nuru) M28Nepal
1997Antoine De ChoudensM27France
1997Eero Veikka Juhani GustafssonM29Finland
1997Pavle KozjekM38Slovenia
1998Radek JarosM34Czech Republic
1998Anatoli MoshnikovM45Russia
1998Vladimir NosekM34Czech Republic
1998Vladimir (Vlado) PlulikM35Slovakia
1999Joao Jose Silva Abranches GarciaM31Portugal
1999Vladimir (Volodymyr) GorbachM44Ukraine
1999Heber Alain OronaM28Argentina
1999Babu Chiri (Babu Tshering) M33Nepal
2000Gheorghe DijmarescuM38USA
2000Josef (Sepp) HurschlerM38Switzerland
2001Theodor (Theo) FritscheM51Austria
2001Stefan GattM30Austria
2001Mark Charles McDermottM42UK
2001Silvio MondinelliM42Italy
2001Juan Eusebio (Juanito) Oiarzabal UrteagaM45Spain
2002Alexei BolotovM39Russia
2002Miroslav CabanM38Czech Republic
2002Shinji SatoM51Japan
2003Gheorghe DijmarescuM41USA
2004Khetsun (Kaizun, Kai Zhong) M38China
2004Laji (Lhagyi, La Ji) F34China
2004Lhakpa (Laba, La Ba) M39China
2004Tashi Tsering (Zhaxi Ciren) M37China
2004Claudio BastrentazM38Italy
2004Doichin Vencislavov BoyanovM27Bulgaria
2004Alessandro Mario BuscaM36Italy
2004Dawa Gelu M31Nepal
2004Lhakpa Tshering/Tshiri M28Nepal
2004Namgya/Namgyal M32Nepal
2004Pemba Dorje M27Nepal
2005Gerfried GoeschlM32Austria
2005Marcin Jan MiotkM32Poland
2006David FojtikM33Czech Republic
2006Roberto (Roby) PiantoniM28Italy
2006Kami M39Nepal
2006Lhakpa Nuru M34Nepal
2006Nima Dorje M25Nepal
2007Romano BenetM45Italy
2007Rodney William ColeM48USA
2007Nives MeroiF45Italy
2007Luis Felipe (Lucho) Ossa RodriguezM40Colombia
2007Kami (Kame) M44Nepal
2007Nima Kancha M27Nepal
2007Pemba Dorje M30Nepal
2009Ludovic Paul Nicolas ChalleatM39France
2010Abele BlancM55Italy
2010Marco CamandonaM39Italy
2010Michele EnzioM33Italy
2010Gerlinde KaltenbrunnerF39Austria
2010Silvio MondinelliM51Italy
2010Ang Pasang M42Nepal
2010Chhuwang Nima M42Nepal
2010Dawa Jangbu (Da Jangbu) M30Nepal
2010Dawa Nuru (Da Nuru) M45Nepal
2010Kami Rita (Topke) M40Nepal
2010Lhakpa Rita M43Nepal
2010Mingma Chhiri/Chhiring M41Nepal
2010Mingma Dorje M40Nepal
2010Pema Chhiring/Chhiri M26Nepal
2011Mahdi Amidi AhangM31Iran
2011Helmut OrtnerM46Austria
2011Lhakpa Tshering M38Nepal
2012Conrad Daniel AnkerM49USA
2012Mariano GalvanM32Argentina
2012Rajendra Singh JalalM40India
2012Thupten LobsangM37India
2013Rafael CaceresM32Ecuador
2013Oswaldo Aurielo Freire CartegenaM40Ecuador
2013Chang-Ho KimM43S Korea
2013Esteban Patricio (Topo) Mena YanezM23Ecuador
2013Lama M0Nepal
2013Mingma Thinduk M30Nepal
2013Pemba Dorje M36Nepal
2016Melissa Sue ArnotF32USA
2016Azim GheichisazM34Iran
2016Carla Patricia Perez RualesF33Ecuador
2016Courtney Woodward (Cory) RichardsM35USA
2016David RoeskeM35USA
2017Kalden Panjur M37India
2017Sonam Phuntsok M25India
2017Adrian John BallingerM41UK
2017Kelsang Dorjee BhutiaM35India
2017James Robert BroomanM37UK
2017Horia Dan ColibasanuM40Romania
2017Kilian Jornet BurgadaM29Spain
2017Victor Hugo Rimac TrejoM31Peru
2017Mingma Dorje M0Nepal
Posted in History Through Spreadsheets | Leave a comment

My Robot Lawn Mowers


Quote of the Day

People will provoke you until they bring out your ugly side, then play victim when you go there.

— Internet meme. I have seen this play out many times. I first heard this from a friend of mine who is a marriage counselor. He said husbands and wives often argue this way.

Figure 1: My Worx Landroid Robot Lawn Mower. (Source: Me)

I am working today in my garage office at my cabin in northern Minnesota. From the office window, I can see a Worx Landroid robot mowing the lawn (Figure 1). I installed two of these mowers  (one for garage and one for cabin) because I am not regularly at the cabin and wanted to ensure the lawn was mowed even when I could not be there.

When I am not at my cabin, I can monitor what they are doing with some outdoor cameras that I have installed.  The mowers go out every day at a preset time and for a preset duration. They dutiful mow two areas that I have marked off with 1 mm diameter (18 AWG) buried wire. I installed the robots three weeks ago and have been delighted with the quality of their work.

The Landroids operate very simply:

  • They mow a path randomly chosen straight line path within the wire boundary.
  • You can define where they start along the wire boundary.
  • You can define different regions within the wire-defined boundary and specify the amount of time the mowers spend in each region. This works well when you have a lawn with two large areas connected with a narrow area.
  • They work best when they cut a small amount of grass length every day.
  • They are battery-powered and very quiet.
  • They cut the lawn using a rotating cutter with three razor blades. You replace the razors when they become dull.

The only difficult part of the effort was trenching 750 meters (2500 feet) of wire to mark the mowing boundaries.  Renting the EZ-Cable Installer TP400CL3 simplified burying the wire enormously (Figure 2). I buried the wire about 8 cm (3 inches) deep.

Figure 2: E-Z Wire Trencher. (Source:

With the E-Z Trencher, I was able to lay 750 meters of wire in less than 5 hours. I rented the unit from Sunbelt Rental in Minneapolis.

Posted in Cabin | 2 Comments

US Dairy Industry Stats


Quote of the Day

Words are like harpoons. Once they go in, they are very hard to pull out.

Fred Hoyle, astrophysicist

Figure 1: Number of US Dairy Farms Over Time. (Source: USDA)

Early in my life, I spent quite a bit of time around cows on small farms with about 160 to 200 acres of land, and 40 to 50 cows. I never saw a corporate farm. Three years ago, I spent quite a bit of time in Iowa working on a number of fiber optic deployments in rural areas populated with a large number of dairy operations. While performing fiber installation inspections, I noticed that many of the old farmhouses were occupied by renters and the land was being farmed by corporations. As I looked around both Iowa and Minnesota, I realized that the number of dairy farms is dropping fast (Figure 1).

This change in farming is affecting my extended family, which is descended from two German brothers who came to America to escape the Franco-Prussian War. They setup their dairy farm on a 160-acre homestead. That farm will not go forward in our family because it is just too small to be competitive. I talked to my uncle about the situation and he said that the problem occurred when the time came to pass the farm from one generation to the next. The farm simply was not big enough to support two families while the transition should have occurred. After much angst, he decided that it was time for the family farm to be consolidated with a larger operation. His son will not be farming that land.

A key part of my uncle's decision is the unprofitability of the US Dairy industry. The lack of profit in the dairy industry is a complex tale of:

Figure 2 shows the red ink of today's dairy industry. The revenue per hundredweight of milk (a standard unit for US milk production) has not covered the Cost of Goods Sold (COGS) for years.

Figure 2: Economics of Today's Dairy Market. (Source)

To support two families when the farm transitioned from father to son, my uncle said that he would have to increase the number of cows from the 43 he has managed for years. But every cow requires a certain amount of land on which to graze and to grow feed crops. He said that buying feed was a money loser because it was too expensive – you must support your herd with the land you have. Let's walk through the numbers of running a dairy operation on a small Midwest farm (160 acres) using the analysis method from the Wisconsin Department of Agriculture. Wisconsin has similar conditions to those in Minnesota and the numbers for both states are similar.

This analysis assumes that the cows just eat what is grown on the land. While my uncle did not use any formulas, his rules of thumb were very similar to key constants that drive this analysis, namely the amount of food that a cow needs per day and what the land can produce. The bottom line is that a 160 acre Minnesota farm can support about 40 dairy cows with a 50/50 split between pasture and field crops.

Carrying Capacity Calculations

Figure 3: Cow Carrying Capacity for a 160 Acre Minnesota Farm.

Today, this land is being farmed by a larger dairy operation – they have the size needed to reduce the cost of production through automation and improved efficiency. The same economics is affecting the other dairy farmers in the region. This means that we will see US dairy operations become fewer in number but larger in the number of cows per operation. Figure 4 shows the number of cows per dairy operation by region. The US West already has huge operations, but the Midwest is going to experience much painful restructuring.

Figure 4: Dairy Herd Size By Region.

For those who are interested, my Excel workbooks and R markdown code are here.

Voyager 1 current status Voyager 1 current status Alfalfa Forage
Posted in Farming | 1 Comment

Using Excel to Convert a Number List to Dashed String of Ranges


Quote of the Day

Gratitude is riches. Complaint is poverty.

Doris Day. This quote is another expression of the Benedictine philosophy.

Figure 1: Yellow highlight shows the function output for the number list shown.

While working on a test report for an aircraft manufacturer this week, I needed to convert a large number of number lists to strings of dashed ranges. For illustration, suppose you are given a list {1, 2, 3, 5, 6, 7, 8, 10}. Converting this list to a dashed set of ranges means generating the string "1-3, 5, 6-8, 10." Figure 1 shows another example with an optional prefix added to each number.

The need for this operation is more common than you might think. I usually see the dashed string format used on Bills of Materials (BOMs) for listing out the reference designators for specific part numbers, like resistors. A PCB BOM might have multiple 10Ω resistors with reference designators listed as "R1-R3, R5, R6-R8, R10." Normally, the schematic capture system would generate the reference designators in this format automatically, but I have worked in situations where the parts lists were in Excel and you needed to convert lists to list of dashed ranges.

Today's situation involved creating a dashed range list of the various graphs in a test report that had calibration and data charts interspersed. The customer wanted their data formatted this way and I complied – the life of a contractor.

My workbook is attached here. The VBA code is shown below.

Posted in Excel | 1 Comment

US Iron and Aluminum Mining During WW2


Quote of the Day

Nothing. I keep other people awake at night.

— General Jim Mattis. His answer to the question, "What keeps you up at night?"

Figure 1: Hematite Crystal. I used to find smaller versions of these crystals when I was a boy. (Wikipedia)

Figure 1: Hematite Crystal. I used to find smaller versions of these crystals when I was a boy. (Wikipedia)

My vacation/retirement cabin is in the iron mining region of Minnesota. The rock throughout the area shows the reddish hue of iron. I recently heard some old-timers talking about how the intensity of mining operations during WW2 took the last of the high-grade iron ore (hematite –Figure 1) and left only low-grade ore (taconite). This comment made me curious about mining during WW2.

Because metals were so important to the war effort during WW2, I thought it would be interesting to look at how the war affected iron and aluminum mining. Since I have often heard people say that WW2 pulled the US out of the Great Depression, this data may help illustrate what really happened.

All of the data is from the US Census Bureau's The Historical Statistics of the United States 1789 - 1945 (link – 55 MBytes). I am not going into the nuances of gathering the data because it required only doing Optical Character Recognition (OCR) on the document. All I did was make a couple of simple graphs. However, the graphs are interesting.

For this exercise, I defined the war years as 1940 through 1945. Technically, the US did not enter the war until late 1941, but the US had started supplying materials almost immediately after hostilities started on 1-Sep-1939. Production greatly increased with the passing of the Two-Ocean Navy Act of 1940.

Figure 2 shows how WW2 affected iron ore production. Note how iron ore production had dropped to less than 30 million tons in 1938 (see Recession of 1937-1938), but rose steadily to its peak in 1942. Production then declined each year during the rest of the war (1943-1945). We see that iron ore production increased, but it was not an order of magnitude. Aluminum ore was a different matter.

Figure 2: US Iron Ore Production from 1914 to 1945.

Figure 2: US Iron Ore Production from 1914 to 1945.

Figure 3 shows how WW2 affected aluminum ore (bauxite) production. Ore is key to the production of primary (non-recycled) aluminum. Note the dramatic rise in bauxite production as compared to that of iron ore. Since aluminum is important to the production of aircraft, this chart shows the massive increase in the production of aircraft that occurred during the war.

Figure 3: US Bauxite Production from 1914 to 1945.

Figure 3: US Bauxite Production from 1914 to 1945.

For those who are curious, my Excel workbook is here.

Posted in History Through Spreadsheets | 2 Comments

Estimating Exponential Time Constants


Quote of the Day

Engineering is achieving function while avoiding failure.

Henry Petroski

Figure 1: Annotated Exponential Curve.

Figure 1: Annotated Exponential Curve.

I have been presented with a large amount of experimental data from which I need to determine many exponential time constants. There are so many time constants to calculate that I need to automate the process.

I have data with dozens of exponential curves like shown in Figure 1.  I My plan is to estimate three points from every exponential curve: (t0, V0); (t1,V1); and (t2,V2). I will then determine the exponential time constant (τ) and the final voltage (VF) by fitting these three points to Equation 1.

Eq. 1 \displaystyle v(t)={{V}_{F}}+\left( {{{V}_{I}}-{{V}_{F}}} \right)\cdot {{e}^{{-\frac{t}{\tau }}}}


  • VF is the final voltage of reached by the curve.
  • VI is the initial voltage of the curve.
  • τ is the exponential time constant that I need to compute.
  • v(t) is the exponential voltage as a function of time.
  • t is time.

Because I have two unknowns (τ, VF), I will need to solve two equations. In my case, I cannot see the point where the exponential curve begins – I will show below that it does not matter. In Figure 1, I select a reference point (t0, V0) and measure the two other points relative to V0, i.e., (V1, t1-t0) and (V2, t2-t0).

To find (τ, VF), I chose to use a nonlinear solver in Mathcad to solve the problem (Figure 2).

Figure M: Mathcad Solution.

Figure 2: Mathcad Solution with Test Case.

I should mention that it does not matter which point is chosen for the reference. You can prove this as shown below. In this derivation, I show that when you pick a reference V0, the point value of the point at t1 is driven by the time difference t1-t0.

Figure M:

Figure 3: Derivation Showing that the Value of V1 is related only to the time difference from V0.

Thanks to this approach, I was able to determine all the exponential time constants quickly and accurately.

Posted in General Mathematics | Leave a comment

Determining RMS Acceleration for a Vibration Acceleration Spectral Density


Quote of the Day

The most important 6 inches on the battlefield is between your ears.

General James Mattis


Figure 1: Vibration Acceleration Spectral Density Example From NAVMAT P-9492. (Source)

Figure 1: Vibration Acceleration Spectral Density Example From NAVMAT P-9492.

I was asked last week to write a vibration test plan for a mobile electronic product. I am used to writing vibration test plans that follow canned procedures in standards like MIL-STD-810F or SAE J1455, but this case is different because the customer has specified a non‑standard random vibration acceleration profile, which is also called a Power Spectral Density (PSD). I need to determine the RMS g level for this profile. This post shows how I go about this calculation. I am not going to showing the customer's vibration PSD because it is proprietary. Instead, I will use a well‑known US Navy vibration PSD as a computation example (Figure 1).

In the case of vibration testing, the term PSD is a misnomer. Vibration testing is normally based on an Acceleration Spectral Density (ASD) function expressed in the form of a graph or table. However, the term PSD is commonly used because the analysis methods are identical to those used with PSDs in digital signal processing, where the term PSD really does refer to a power spectrum.



For my random vibration test problem, I was given a normalized ASD shape (no absolute levels) and the customer's desired RMS g (acceleration) level. I must integrate the ASD to determine the RMS g level. Given the integral, I can set the absolute levels to obtain the required RMS g level. This effort is complicated slightly by the fact that the ASD is usually specified in terms of piecewise linear segments on a log-log graph. What is the function that I must integrate? This post will show you how to integrate an ASD to determine the RMS g level.

ASD Example

Figure 1 shows a US Navy example that has been used for decades to test electronic gear, which developed by Willis Willoughby, who developed much of the US Navy's quality program. These piecewise linear approximations are important because vibration test equipment (Figure 2) is usually programmed using piecewise linear approximations to ASDs. The approximation process is actually quite interesting, and NASA presents a good example of the process here.

Figure 2 shows a typical vibration table.

Figure 2: Example of a NASA Vibration Test Fixture.

Figure 2: Example of a NASA Vibration Test Fixture.



A line segment on a log-log graph does not represent a line segment on a linear scale. My integration needs to be performed with the function on a linear scale. So I need to convert that log-log line segment into its linear scale form. Figure 3 shows how I did that conversion.

The yellow highlighted areas of Figure 3 shows how to compute the slope and intercept of the line segment on a log-log plot. The green highlighted area shows how to convert a point on a log-log plot to a linear scale.

Figure 2: Log-Log Graph Line Derivation.

Figure 3: Log-Log to Linear Graph Line Derivation.

Now that I have my vibration function, the integration operation is straightforward. I will be using Mathcad's built-in integration function, but many tools support similar capabilities (e.g., R has its trapz function, which I use regularly).


To compute the RMS g level, I must integrate the ASD curve for the frequencies of interest, in this case, 10 Hz to 2000 Hz. Figure 4 shows my work for the US Navy example of Figure 1. I computed 6 gRMS, which I highlight in yellow in Figure 4. The US Navy lists this profile as 6.06 gRMS, which is close agreement.

Figure 4: My Calculation for the RMS g Level.

Figure 4: My Calculation for the RMS g Level.

I should mention that there are other ways to compute RMS g level. Take a look at this website and this website.

Posted in General Mathematics | Leave a comment