Author Archives: mathscinotes

Using Excel to Convert a Number List to Dashed String of Ranges

While working on a test report for an aircraft manufacturer this week, I needed to convert a large number of number lists to strings of dashed ranges. For example, suppose you are given a list {1, 2, 3, 5, 6, 7, 8, 10}. Converting this list to a dashed set of ranges means generating the string "1-3, 5, 6-8, 10." Figure 1 shows another example with an optional prefix added to each number. Continue reading

Posted in Excel | Leave a comment

US Iron and Aluminum Mining During WW2

My vacation/retirement cabin is in the iron mining region of Minnesota. The rock throughout the area shows the reddish hue of iron. I recently heard some old timers talking about how the intensity of the mining operations during WW2 took the last of the high-grade iron ore (hematite) and left only low-grade ore (taconite). This comment made me curious about mining during WW2. Continue reading

Posted in History Through Spreadsheets | 2 Comments

Estimating Exponential Time Constants

I have been presented with a large amount of experimental data from which I need to determine many exponential time constants. There are so many time constants to calculate that I need to automate the process. Continue reading

Posted in General Mathematics | Leave a comment

Determining RMS Acceleration for a Vibration Acceleration Spectral Density

I was asked last week to write a vibration test plan for a mobile electronic product. I am used to writing vibration test plans that follow canned procedures in standards like MIL-STD-810F or SAE J1455, but this case is different because the customer has specified a non‑standard random vibration acceleration profile, which is also called a Power Spectral Density (PSD). I need to determine the RMS g level for this profile. This post shows how I go about this calculation. I am not going to showing the customer's vibration PSD because it is proprietary. Instead, I will use a well‑known US Navy vibration PSD as a computation example (Figure 1). Continue reading

Posted in General Mathematics | Leave a comment

Let's Grow Some Oats

I am going to grow and process some oats this year. This is a project that I have been interested in doing for a while because one of my sons is now in the oat business and he has shown some interest in working through the entire oat processing cycle. As a boy, I used to mill oats on the family farm, but I remember very little of that time. Continue reading

Posted in Excel, General Mathematics | Leave a comment

Age of Supreme Court Justices at Confirmation with Power Query

I was listening to a political pundit mention that both US political parties want to confirm young Supreme Court justices to ensure that their judicial philosophies endure. I was curious as to whether that was true over time. I went to the Wikipedia and saw that they had a list of all the justices since the founding of the US and web pages for each justice. Sounds like a perfect opportunity for a bit of web scraping! Continue reading

Posted in Civics Through Spreadsheets, Excel, History Through Spreadsheets | Leave a comment

Power Query DATEDIF Function

I have been using Excel's DATEDIF function for years to determine the age of items in years, months, and days. I did not know that the function was unsupported and had issues until I ran into a bug last week. Because much of my personal work involves dates, I need to have an accurate age calculation function for use in Excel and Power Query. In this post, I will discuss a DATEIF workaround that I found online (Figure 1) and a Power Query age calculation function that I wrote based on a concept from Imke Feldmann. My workbook is available here for those who are interested. The workbook shows how I tested the routine by comparing it with the DATEDIF workaround results. I tested the boundary conditions and then random dates. The results agreed with the DATEDIF workaround of Figure 1 and an online date calculator. Continue reading

Posted in Excel | Leave a comment

Computing the Longest Lived US President with Power Query

I was listening to news the other night when I heard a reporter mention that Jimmy Carter just became the oldest US president in history. I thought verifying this fact would be a good Power Query exercise. He had just surpassed George H.W. Bush, the previous record holder. Continue reading

Posted in Excel, History Through Spreadsheets | Leave a comment

Good Use for Excel Textjoin Command

One common Excel task is tracking work hours. As a contractor, I encounter all sorts of approaches to recording work hours. One small company wants all of my hours captured in an Excel workbook that contains one worksheet per week. Every two weeks, an administrator goes in and captures the hours into another worksheet. Continue reading

Posted in Excel, Uncategorized | Leave a comment

BER Test Time Spreadsheet

I test high-speed serial channels every day. The most common test parameter that I need to measure is the Bit Error Rate (BER). Figure 1 shows the most common test configuration used for measuring BER. Because bit errors occur randomly, there is a certain amount of error involved in measuring the parameter. So when you state a BER measurement, you also give a confidence interval to express your level of uncertainty. Continue reading

Posted in Electronics | Leave a comment