Days Postings
Blog Series
Subscribe to Blog via Email
Copyright Notice
© Mark Biegert and Math Encounters, 2019. Publication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Mark Biegert and Math Encounters with appropriate and specific direction to the original content.
Disclaimer
All content provided on the mathscinotes.com blog is for informational purposes only. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The owner of mathscinotes.com will not be liable for any errors or omissions in this information nor for the availability of this information. The owner will not be liable for any losses, injuries, or damages from the display or use of this information.
Category Archives: Excel
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
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
Posted in Excel, Financial
Leave a comment
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
Posted in Excel
Leave a comment
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
Posted in Excel
Leave a comment
Evenly Spaced Points on Logarithmic Graphic Using Excel
I am doing some testing at an Electromagnetic Compatibility (EMC) facility this week. Part of the test specification requires that we inject audio frequency interference on the power supply lines at discrete frequencies that range from 10Hz to 100+KHz, with 30 frequencies selected from each decade of frequencies (e.g. 10 Hz to 100 Hz, 100 Hz to 1 kHz, etc.). Figure 1 shows a specification similar to the one I am performing. My test facility that has chosen the discrete frequencies to be evenly spaced on a logarithmic axis. I started to wonder how the frequencies were selected – let's work through it. Continue reading
Posted in Excel, General Mathematics
1 Comment
Test Time vs BER and Confidence Level Using Excel
I am currently working as a project manager on a serial data channel qualification task. During this work, I need to estimate the time required to perform dozens of Bit Error Rate (BER) measurements under different scenarios (see Figure 1). In these measurements, we are working to ensure that our BER is always 1E-10 or less. I have performed this calculation many times and have derived all the associated formulas here. BER is a statistical parameter and its measurement is a function of the confidence level you want in your answer. In this case, we want the highest confidence level possible consistent with a two-week test effort. Continue reading
Posted in Electronics, Excel
Leave a comment
Computing Dates of Fathers Day with Excel
My wedding anniversary (16-June) and Fathers Day are on the same weekend this year. Next year, Fathers Day and my anniversary are on the same day. I became curious about (a) how to compute the date of Fathers Day for each year, and (b) determining the years when Fathers Day and my anniversary occur on the same date. Continue reading
Posted in Excel, Math Education
Leave a comment