Category Archives: Excel

Hurricanes and Typhoons Ranked By Air Pressure

I was watching the weather reports on Hurricane Erma and the discussions on how powerful it is. The most cited metrics for hurricanes and typhoons appears to be wind speed and eye barometric pressure (see Figure 1). I decided to look around for hurricane and typhoon strength data and the Wikipedia turned out to have a page containing large number of tables for all the most intense typhoons and hurricanes in different regions of the world. I used Power Query to (1) import the tables, (2) clean them up, (3) combine them, and (4) rank the storms by air pressure. Continue reading

 
Posted in Excel, History Through Spreadsheets | Leave a comment

Largest Counties in the US

I am currently building a retirement home in Itasca County, Minnesota. While at the construction site, I spoke with with a sheriff's deputy about law enforcement coverage for my area. He told me that there were only two deputies on duty at night covering an area larger than the state of Delaware. He also said that if I the deputies are tied up in the southern part of the county it can take a while for them to get to my place, which is in the the northern part of the county. I had never thought about the size of the counties in northern Minnesota, but there are some large pieces of real estate there. Continue reading

 
Posted in Excel, Personal | Leave a comment

US Navy Warship Mix

While driving home after watching the eclipse, I listened to the news talking about a collision between a US Navy destroyer, the USS John S. McCain (Figure 1), and a commercial tanker.  A previous collision also involved a destroyer, the USS Fitzgerald. These stories have made me curious about how many of these ships the US Navy has.  Continue reading

 
Posted in Excel, Military History | Leave a comment

US Farmland % By State

I am preparing to drive out to Idaho to experience totality during the August 21, 2017 eclipse. I am choosing to view the eclipse from Idaho because my granddaughter (and her parents) live in western Montana, and I can stop there for visit when I return to Minnesota. This will be a long drive through large states (Minnesota, North Dakota, Montana) that have a significant percentage of farm land. While planning my journey, I became curious as to the percentage of farmland in each of the fifty states. In this post, I compute the farm land percentage by taking the farm acreage in each state and dividing by the state's land area (water area removed). Continue reading

 
Posted in Excel | Leave a comment

An Example of Cleaning Untidy Data with Tidyr

I recently decided to take some classes in data analysis at Datacamp, an online training site. My first classes were in dplyr and tidyr – two excellent R-based tools for manipulating files that not amenable to analysis because of inconsistencies and structure: tidyr provides many tools for cleaning up messy data, dplyr provides many tools for restructuring data. After completing the two classes, I decided that I needed to firm up my knowledge of these tools by applying them to a concrete example. Continue reading

 
Posted in Excel, History Through Spreadsheets | 2 Comments

Quick Look at Large US Dams

I was on the phone this morning with a coworker who lives in California, about 150 miles south of the Oroville dam (Figure 1). This dam has recently been in the news because of concerns that spillway erosion could cause a dam failure. At one point, nearly 200K people were evacuated from the potential flood zone. Continue reading

 
Posted in Excel, History of Science and Technology | 3 Comments

Fact Checking: US Murder Rate Over Time

I recently saw a politician claim that the US murder rate is the "highest it's been in 47 years." This is an easy fact to check and provided me another Power Query example to provide for my staff. Continue reading

 
Posted in Excel, Fact Checking | 2 Comments

History Through Spreadsheets: Executive Orders

During my recent seminar on Excel's Power Query feature, I showed my team how to grab data executive order data from the web and generate a simple plot (Figure 1). After generating the plot, I asked the audience what we could learn from this graph. I was expecting to hear that the early 1900s – the time between Teddy Roosevelt and Franklin Roosevelt – was a time of massive use of executive orders. Continue reading

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

Calorie Per Acre Improvements in Staple Crops Over Time

My family has strong agricultural roots – mainly in dairy and potato farming – and our holiday conversations frequently turn to discussions of crop yields (bushels per acre or lbs per acre). As I listened to the discussion between my brothers on this year's crop yields, I realized that the yield numbers they were quoting were much higher today than we saw as children. This made me curious, and I decide to go out to the US Department of Agriculture's National Agricultural Statistics Service crop database and download CSV files on the yield of some key staple crops for processing by Power Query (i.e. recently renamed Get and Transform). I will be using this file to train my staff on defining Power Query functions. No macros were used in this analysis. Continue reading

 
Posted in Excel, General Science, History of Science and Technology, History Through Spreadsheets | 1 Comment

Using Excel's Solver and VBA For Repetitive Table Calculation

I will be providing some employee training on Excel in January, and I need an example of how to automate the use of Excel's Solver add-in – a powerful optimization tool that few engineers use effectively. When I give a training seminar, I make a serious effort to show how I use Excel on real problems. While I generally use Mathcad for most optimization applications, Mathcad does not support integer programming – an optimization method where some or all variables are restricted to be integers. Here is where Solver shines – it supports integer programming. Continue reading

 
Posted in Construction, Excel | 5 Comments