Category Archives: Excel

Lake Water Temperature Report Using Power Query and FILTERXML

Posted on 18-October-2020 by mathscinotes

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

Posted on 17-October-2020 by mathscinotes

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 →

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

US Government Management of Western Forest Lands

Posted on 19-September-2020 by mathscinotes

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 →

 
Posted in Civics Through Spreadsheets, Excel | 3 Comments

Computing Bacteria Reproduction Rate and Doubling Time

Posted on 22-July-2020 by mathscinotes

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

Posted on 4-April-2020 by mathscinotes

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 →

 
Posted in Civics Through Spreadsheets, Excel | 3 Comments

WW2 Fighter Aircraft Fuel Fraction

Posted on 22-March-2020 by mathscinotes

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

Posted on 10-March-2020 by mathscinotes

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

Web Scraping Sailboat Reviews Using Power Query

Posted on 25-February-2020 by mathscinotes

While it is true that I worked on US Navy contracts for twelve years and spent some time on ships testing new underwater vehicles, I know very little about recreational boating. However, I have always been fascinated by sailing, though this fascination has been limited to reading books about the Age of Sail. Continue reading →

 
Posted in Excel, Personal | Leave a comment

Visualizing House of Representative Data Using Power Query

Posted on 18-February-2020 by mathscinotes

I have been following certain bills through the US House of Representatives and wanted to know how the voting varied by US regions and political parties. I also want to generate tables that show how my state's representatives vote (Figure 1). Fortunately, the votes are documented online and Power Query was able to easily grab and process the data. Continue reading →

 
Posted in Civics Through Spreadsheets, Excel | Leave a comment

Power Query Regular Expression Hack

Posted on 12-February-2020 by mathscinotes

I use Python, R, and Excel every day in the course of my work. Because many corporations are focused on the Microsoft Office suite of tools, many businesses require that I use Excel/Power Query so that they can work with the tools I develop after I am done. Fortunately, I really enjoy using Power Query, but I find it irritating that it does not support regular expressions. I must admit that Power Query's standard functions can do a good job of extracting strings, but the process is a bit tedious.  However, I have a large library of regular expressions for extracting email addresses, phone numbers, social security numbers, and the like that would be efficient for me to use. Continue reading →

 
Posted in Excel | Leave a comment