Monthly Archives: November 2018

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

Tensions and Angles in a Simple Rope Rigging

I have been tutoring math and physics at the local library for the last few months. As part of this tutoring, I have been looking for good graphics that illustrate basic science concepts. One common high-school physics problem involves computing the tension in ropes tied to an anchor by a pulley. Figure 1 is a graphic that nicely illustrates the tension relationship between two ropes connected to an anchor point by a carabiner. Continue reading

Posted in Cabin, Construction, Daily Math | 10 Comments