Category Archives: Excel

Was Ulysses Grant a Butcher?

This post uses a combination of data from a Github repo by Jeffery Arnold that contains a fantastic amount of Civil War battle data and some Wikipedia scraping to generate similar tables. I should note that my casualty results are significantly different than Bonekemper’s because there are large differences between sources of Civil War casualty data. The reasons behind these differences are complex, but the Arnold repo has data from a number of sources. I chose his Wikipedia file because it is easy to check where the data came from. Continue reading

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

Duplicate Drach’s U-Boat Tonnage and Sunk Charts

I am a huge fan of Drachinifel’s naval history channel. The other day, Drach was participating in the Armchair Admiral’s program, during which he presented two charts on the Battle of the Atlantic that I have never seen before: (1) A chart of tonnage sunk by U-boats versus time and (2) a chart of U-boats sunk versus time. The unique aspect of the charts was that the data points were colored based on whether the Enigma cipher was broken at the time and whether centimetric (microwave) radar was deployed. These charts really got me thinking about the impact of technology on the struggle against U-boats. Continue reading

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

Excel Generated Traceability Matrix

I recently needed to generate a table that showed how every user requirement generated by our Marketing Department was mapped to one or more system requirements. This table is known as a flowdown table. For all sorts of reasons, none of them good, our requirements database could not generate the report. I decided that Excel would be the appropriate tool to generate the table we required. I should note that a Python version was also developed and will likely be used in the future. Continue reading

 
Posted in Excel, Management | Leave a comment

Sunken Ships at Ironbottom Sound

I just finished watching a series of videos on the Guadalcanal Campaign by Drachinifel, whose work is superb (Figure 2). The marines derisively referred to this campaign as Operation Shoestring because of the resource limitations. Things were no better for the sailors. Unlike many WW2 island campaigns, more sailors died in the battles than ground troops (link). The Allies, and in particular the US Navy (USN), had to learn the hard way that the Imperial Japanese Navy (IJN) was a force that deserved respect. Many Allied ships were sunk while learning this lesson. Continue reading

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

US Navy Ship Numbers Versus Time

My reading and watching lectures on Mahan have motivated me to look at how the US Navy grew and shrank over the years. Fortunately, the Naval History and Heritage Command (NHHC) have an excellent page on the size of the US Navy over time. Unfortunately, the data is scattered throughout the page and it must be scrapped so that I can consolidate and graph it. This post is about using Power Query to scrape the data from the page and generate a graph in Excel of the number of active ships in the US Navy over time. Continue reading

 
Posted in Excel, Military History | Leave a comment

Excel Spillable Ranges are Great!

I use Python or R for my large-scale data work, but I do find Excel a very powerful ad hoc data analysis tool, particularly with some of the new functions that use spillable ranges. Today, I was given a large table of Engineering Change Orders (ECOs) and a comma-separated list of the documents each ECO affected (very abbreviated form shown in Figure 1). Continue reading

 
Posted in Excel | Leave a comment

US Daylight Saving Time Date Calculation in Excel

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

 
Posted in Excel | 3 Comments

50 Destroyer Pre-War Base Deal

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

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

BB Ballistic Coefficients

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 dthis father-son duo with the required calculations (documented here) and the answer I obtained seemed reasonable. Continue reading

 
Posted in Ballistics, Excel | Leave a comment

Optimized Piecewise Linear Model Using Excel

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

 
Posted in Batteries, Excel | Leave a comment