Category Archives: Excel

I Forgot – VLOOKUP is Not Case-Sensitive

Posted on 20-July-2018 by mathscinotes

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 | Comments Off on I Forgot – VLOOKUP is Not Case-Sensitive

Evenly Spaced Points on Logarithmic Graphic Using Excel

Posted on 9-July-2018 by mathscinotes

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

Posted on 4-July-2018 by mathscinotes

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 | 1 Comment

Computing Dates of Fathers Day with Excel

Posted on 17-June-2018 by mathscinotes

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 | Comments Off on Computing Dates of Fathers Day with Excel

Liberty Ship Production Data

Posted on 22-May-2018 by mathscinotes

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

Minnesotans In the Olympics

Posted on 24-February-2018 by mathscinotes

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 | Comments Off on Minnesotans In the Olympics

Minor Planet Eccentricity versus Perihelion Chart

Posted on 9-January-2018 by mathscinotes

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 →

Posted in Astronomy, Excel | 1 Comment

Using Excel to View US Pre-School Attendance Rate

Posted on 4-January-2018 by mathscinotes

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 | Comments Off on Using Excel to View US Pre-School Attendance Rate

Super Bowl Winners and Losers Using Power Query

Posted on 13-December-2017 by mathscinotes

I was reading a post on Statista showing the NFL teams with the most Super Bowl wins. Since my staff includes a number of football fans — mainly Viking and Packer supporters — I decided it would be a good training exercise to show them how to gather the football statistics and present them in the same manner as shown on Statista. I should mention that I do not follow football at all; this is purely a data analysis exercise for me. Continue reading →

Posted in Excel, Statistics | Comments Off on Super Bowl Winners and Losers Using Power Query

Estimating Asteroid Sizes Using Excel 2D Data Table

Posted on 17-November-2017 by mathscinotes

I recently gave a seminar on how to design products using Thermoelectric Coolers (TECs). During this presentation, I showed the audience how to generate various tables and graphs using Excel data tables. I was surprised to learn that no one in the audience had ever seen an Excel data table in action. Since the middle of a seminar on TECs is probably not the best time to divert to some Excel training, I decided to prepare a simpler example the would be easier to understand on first exposure. Continue reading →

Posted in Astronomy, Excel | 2 Comments