# Category Archives: Excel

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

## US WW2 Torpedo Production Chart Using Power Query

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

## 18% of American’s Can Determine 52 Senators?

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

## Relative Cost of WW2 US Fighters

A reader of this blog mentioned in a comment that cost might be a big reason for the US Army Air Corps (USAAC) switchover to the P-51 from P-38s and P-47s. I thought I would put together a quick report on the relative cost of the three main USAAC fighters. The cost of these fighters by year was available in the Army Air Forces Statistical Digest (Hyperwar Site). The approach to Extracting, Transforming, and Loading (ETL) the data are the same as I used to determine the on-hand numbers of aircraft (link). For those who are interested in the details, my workbook is available here. Continue reading

## Lake Water Temperature Report Using Power Query and FILTERXML

I have a cabin in northern Minnesota on a small lake where I have been staying isolated from the rest of the US while COVID rages around me.  I track various lake metrics like height relative to sea level, clarity, and temperature.  I gather this information into a report that I send to the other folks who live on the lake. In this post, I show how I put together a chart of lake water temperature vs time using Excel and Power Query. I also demonstrate how to use the FILTERXML function to parse some rather messy data. I find myself using FILTERXML for data parsing more and more. Continue reading

Posted in Cabin, Excel | 1 Comment

## US Army Air Corp Fighters on Hand During WW2

Many years ago, at the start of my career, I worked with an excellent safety engineer who had served in WW2 as a fighter pilot in Europe with the US Army Air Corps (USAAC). You could tell that flying was the love of his life. Though we were working on naval weapons systems, our lunchtime talks often focused on his experiences flying aircraft during the war. His war service began in a P-47 (Figure 1) and his unit later transitioned to the P‑51 (Figure 2). Continue reading

## US Government Management of Western Forest Lands

I am always looking for data analysis exercises for the young folks that I tutor online. While watching the coverage of the massive forest fires on the west coast of the US, I noticed that all the media were reporting that much of the forest land in these states are managed by the US government, which makes them responsible for managing these forests. I thought it would be a good exercise to show the students how to calculate the percentage of forest land that is managed by the US government. Continue reading

## Computing Bacteria Reproduction Rate and Doubling Time

Until the arrival of the coronavirus, I looked forward every week to volunteering at a local library as a tutor for university students. Now that COVID is raging around me, I have moved the tutoring online. Most of the students are training for some form of a medical career. This week a student presented me with bacterial growth data and was wondering how to estimate the growth rate and doubling time for the bacteria using Excel. This exercise nicely illustrates the entire data analysis process (Figure 1) in a single example and I decided to post my solution here. Continue reading

Posted in Excel, General Mathematics, Health, optics, Statistics | 2 Comments

## USNS Comfort Bed Count Relative To Other US Hospitals

Because of COVID-19, I am currently sheltering-in-place at my cabin in northern Minnesota. I was reading news reports of the USNS Comfort docking in New York City to provide the city with another 1000 bed hospital. I had no idea as to the average size of a hospital and decided to take a quick look at the number of beds in hospitals around the United States using a histogram. Continue reading