Category Archives: History Through Spreadsheets

WW2 Fighter Aircraft Fuel Fraction

I recently read a book called Boyd: The Fighter Pilot Who Changed the Art of War that documents the life of John Boyd, a fighter pilot who was critical to putting fighter aircraft design on a firm mathematical footing now called Energy-Maneuvering Theory. He was a key member of the Fighter Mafia, a group of military and industry experts in the 1970s that advocated for a lightweight fighter alternative to the large, heavy fighters like the F-111 and MiG-25. Their concepts played a significant role in the final designs of the F-16 and F-18. Continue reading

 
Posted in Excel, History Through Spreadsheets, Military History | 2 Comments

Combined Excel Timeline/Column Chart using WW2 Naval Air Data

I recently needed to generate a graph in Excel that combined a column chart with a timeline. The graph turned out well and I decided to share my work here. As my original work is proprietary, I will share the technique here using some US Navy (USN) air operations data from WW2. Continue reading

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

US Navy WW2 Aviation Statistics Cleanup Using Power Query

was reading a forum post on fighter kill ratios during WW2 and decide to compute some  Imperial Japanese Navy (IJN) vs  US Navy (USN) ratios for myself. I should point out that these ratios are generally viewed as inflated because of the difficulty of confirming downed aircraft. However, the inflated numbers continue to be quoted. The published reports state that the F6F Hellcat had the best kill ratio of the USN/Marine fighter at 19-to-1, followed by the F4U Corsair at 11-to-1, and the F4F Wildcat at 7-to-1. Continue reading

 
Posted in History Through Spreadsheets, Naval History | 2 Comments

Battleship Classes and Throw Weights

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

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

Everest Climbers With No Supplemental Oxygen

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

 
Posted in History Through Spreadsheets | Leave a comment

US Iron and Aluminum Mining During WW2

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

 
Posted in History Through Spreadsheets | 2 Comments

Age of Supreme Court Justices at Confirmation with Power Query

I was listening to a political pundit mention that both US political parties want to confirm young Supreme Court justices to ensure that their judicial philosophies endure. I was curious as to whether that was true over time. I went to the Wikipedia and saw that they had a list of all the justices since the founding of the US and web pages for each justice. Sounds like a perfect opportunity for a bit of web scraping! Continue reading

 
Posted in Civics Through Spreadsheets, Excel, History Through Spreadsheets | Leave a comment

Computing the Longest Lived US President with Power Query

I was listening to news the other night when I heard a reporter mention that Jimmy Carter just became the oldest US president in history. I thought verifying this fact would be a good Power Query exercise. He had just surpassed George H.W. Bush, the previous record holder. Continue reading

 
Posted in Excel, History Through Spreadsheets | Leave a comment

US Cruiser Production During WW2

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

 
Posted in History Through Spreadsheets, Naval History | 2 Comments

US Submarine Production During WW2

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

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