Category Archives: Excel

Let's Grow Some Oats

I am going to grow and process some oats this year. This is a project that I have been interested in doing for a while because one of my sons is now in the oat business and he has shown some interest in working through the entire oat processing cycle. As a boy, I used to mill oats on the family farm, but I remember very little of that time. Continue reading

Posted in Excel, General Mathematics | Leave a comment

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

Power Query DATEDIF Function

I have been using Excel's DATEDIF function for years to determine the age of items in years, months, and days. I did not know that the function was unsupported and had issues until I ran into a bug last week. Because much of my personal work involves dates, I need to have an accurate age calculation function for use in Excel and Power Query. In this post, I will discuss a DATEIF workaround that I found online (Figure 1) and a Power Query age calculation function that I wrote based on a concept from Imke Feldmann. My workbook is available here for those who are interested. The workbook shows how I tested the routine by comparing it with the DATEDIF workaround results. I tested the boundary conditions and then random dates. The results agreed with the DATEDIF workaround of Figure 1 and an online date calculator. Continue reading

Posted in Excel | 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

Good Use for Excel Textjoin Command

One common Excel task is tracking work hours. As a contractor, I encounter all sorts of approaches to recording work hours. One small company wants all of my hours captured in an Excel workbook that contains one worksheet per week. Every two weeks, an administrator goes in and captures the hours into another worksheet. Continue reading

Posted in Excel, Uncategorized | Leave a comment

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

Calculating the Pointing Angle for My Television Antenna

I spend quite a bit of time at a cabin I have built in northern Minnesota. Technically, I spend most of my time in the garage on the site and I have decided that I need to be able to watch the local television stations in Duluth. These stations are ~75 miles away and I need to determine the bearing along which to point my antenna. This seemed like a good Excel exercise that I can also use as an example for those I tutor at the Hennepin County Library. There are web calculators available that perform this calculation (example), but it is more fun doing it myself. Continue reading

Posted in Electronics, Excel | Leave a comment

Medal of Honor Statistics Using Power Query

In this post, I will use Power Query )PQ) to gather (aka web scrape) the US Medal of Honor (MOH) recipient names, rank, service, and conflict from a website called the Congressional Medal of Honor Society (CMOHS), which states that there have been  3505 MOH recipients. They have a beautiful website with the records stored on 140 ages with 25 names per page and one page with 5 names. Normally, I would use Python to scrape a large number of web pages but I am trying to use PQ more because my customers all have Excel. My approach as a contractor is always to work within the existing infrastructure of my customers. A copy of my workbook is available here. Continue reading

Posted in Excel | 3 Comments

Computing the Number of Friday the 13ths in a Year Using Excel

I have been tutoring math at the local library and using Excel as a vehicle for encouraging people to explore everyday math. While at the library, I heard a young man ask "How many Friday the 13ths are in a year?" Since I am always looking for computational examples, I showed him how to use Excel to find the answer for himself. This post shows how I taught him to solve the problem. The solution turned out to be a good example of using Excel's date and array capabilities. Continue reading

Posted in Excel | 3 Comments

Excel VBA Code to Center a Shape in a Cell

I recently finished a job where the customer wanted an Excel dashboard that displayed metrics for test case completion and various success metrics. This dashboard contained many control shapes that I wanted to be centered in cells. I do not like to manually adjust objects so I googled for a VBA routine that would center a shape. I soon found a nice piece of code by HipGecko on the Mr. Excel forum that centered pictures in the active cell. A simple modification of this code allows it to center shapes, an object type that includes pictures and controls. Continue reading

Posted in Excel | Leave a comment