### Days Postings

### Blog Series

### Subscribe to Blog via Email

### Copyright Notice

© Mark Biegert and Math Encounters, 2018. 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

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

## Liberty Ship Production Data

One WW2 battle that we hear little about was fought by logisticians. Their battle was between what could be produced versus what could be delivered in time to matter. This point was driven home to me when I heard a WW2 historian say that the US had the manufacturing capacity to produce 150K tanks, but that level of tank production would consume all the US steel and leave nothing to build the ships needed to carry the tanks to the fight. Continue reading

Posted in Excel, History Through Spreadsheets, Naval History
1 Comment

## Minnesotans In the Olympics

I spend a lot of time in northern Minnesota now that I have a home there. I have been surprised as to how popular curling is in the area (Figure 1). The US curling team at the 2018 Olympics is dominated by people from northern Minnesota. I also notice that there are quite a few Minnesotans participating in the games other sports – the numbers are large enough that the New York Times has even written an article called "Team USA? More Like Team Minnesota" on the topic (PDF of the article). Our state does not have a huge population, ~5 million, and most of that population is concentrated around Minneapolis and St. Paul. The northern part of the state is only sparsely populated as it is covered with national forests and wilderness areas. Continue reading

Posted in Excel, Statistics
Leave a comment

## Minor Planet Eccentricity versus Perihelion Chart

While searching the web for information on the outer solar system, I encountered the graph shown in Figure 1. This graph is made using eccentricity and perihelion data for ~1000 outer solar system objects. As I looked at it, I though I could generate a similar chart using data from the JPL Small Body Database Search Engine – a wonderful tool for solar system data exploration efforts. Continue reading

## Using Excel to View US Pre-School Attendance Rate

Jeffery Sachs was on CSPAN this weekend giving a talk on the competitive challenges the US faces with other nations. During his presentation, he showed a chart (Figure 1) that ranks the US as 30th among reporting OECD countries with respect to preschool participation rates for 4-year-old children. The discussion was interesting, but I found myself focusing on the technical aspects of the graphs he was using. I am always looking for good Excel examples for use in training my staff, and the y-axis in Figure 1 contains formatted text, which is something I have not shown my staff how to do. Continue reading

Posted in Excel
Leave a comment