# Category Archives: Excel

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

| 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

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

## Using Excel to Compute Effective Tax Rate

I have been working as a contractor for the last few months and it is now time for me to make a quarterly tax payment to the folks at the Internal Revenue Service (IRS). This involves using some tables listed on Form 1040ES (see Appendix A). The US tax code uses has a graduated tax rate; the tax rates on higher amounts of income are higher than on lower amounts. As I calculated my tax payment, I became curious as to my effective tax rate. I am going to use Excel to duplicate a graph that I saw on the Wikipedia. My version of the graph is shown in Figure 1. Continue reading

## Using Excel Custom Formatting To Hide Redundant Table Information

I currently am doing some contract work as a system engineer. In this role, I am working on generating and managing lists of requirements to drive the engineering design work. The requirements are stored in a database that I query to generate requirements lists for specific subsystems, like a receiver or transmitter assembly. These requirements are disseminated to the engineers in various subsystem documents (Figure 1). Continue reading

## I Forgot – VLOOKUP is Not Case-Sensitive

I almost sent out a spreadsheet today that contained a VLOOKUP error – I forgot that VLOOKUP is not case sensitive. Fortunately, I caught my error seconds before I hit send. Today's post is about how I chose to perform a case-sensitive lookup in an ancient version of Excel. Continue reading