## Evenly Spaced Points on Logarithmic Graphic Using Excel

Quote of the Day

If I had my life to live over, I would do it all again, but this time I would be nastier.

Jeannette Rankin, the only congressman to vote against the declaration of war on Japan after Pearl Harbor.

Figure 1: Example of Noise Injected as Part of a Susceptibility Test.

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.

The key to determining the points is to observe that consecutive, evenly-spaced points on a logarithmic axis share a common ratio. We can use this fact to derive a solution using Mathcad as shown in Figure 2. Excel users can see the solution here.

Figure 2: Mathcad Calculation of 30 Points Evenly Spaced on a Log Scale.

Posted in Excel, General Mathematics | 1 Comment

## Test Time vs BER and Confidence Level Using Excel

Quote of the Day

When a thing is done, it's done. Don't look back. Look forward to your next objective.

— George C. Marshall. I have met many people who spend their lives constantly looking back and expressing regret for opportunities lost. Learn what you can from your experience and move forward. As Spock used to say "Regret is a useless emotion, especially in humans."

Figure 1: BER Test Data Table for a Single Unit. There are 18 measurements for each unit.

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.

The calculation requires solving Equation 1 for n, the number of bits required, to confirm a maximum BER p at a specific confidence level CL and error number N.

 Eq. 1 $-n\cdot p=\ln \left( 1-CL \right)-\ln \left( \sum\limits_{k=0}^{N}{\frac{{{\left( np \right)}^{k}}}{k!}} \right)$

To convert the number of bits transferred to a test time τ, we need to evaluate Equation 2 with the bit transfer rate, r.

 Eq. 2 $\displaystyle \tau =\frac{n}{r}$

I normally perform this calculation using Mathcad, but in this case, other engineers wanted it written in Excel because they are familiar with that tool. The calculation is a bit messier in Excel because:

• We need to come up with an iterative solution for Equation 1 because it does not have a closed-form solution. In Excel, I used the Goal Seek function with a macro.
• Evaluating summations are a bit messy in Excel. In this case, I decided to use an array formula, which allowed the formula to be evaluated in a single cell.
• You must configure the Excel spreadsheet to use iteration and reduce that maximum change allowed. You adjust these values in Excel by going to File/Options/Formula and checking the following boxes.

Figure 2: Excel Iteration Settings.

Figure 3 is a screenshot of the workbook's test time calculation.  The worksheet is easy to use – just input your p, N, CL, and rate values (highlighted in yellow), then press iterate. This calculation tells me how long a single BER measurement will take. Remember that I need to perform dozens of these measurements, so even small reductions in the single-test time make a big difference to the overall test time.

Figure 3: Screenshot of My BER Workbook.

For those interested, my Excel workbook is here. It includes a very simple macro (shown below) that calls the Goal Seek function for each confidence level and allowed error number. The macro also changes the iteration setting.

## Switch-Controlled Circuit To Isolate Battery from Load

Quote of the Day

Concentration is my motto. First honesty, then industry, then concentration.

— Andrew Carnegie

Figure 1: Block Diagram Showing Role of Battery Isolator.

I recently was asked if it was possible to design a circuit that will isolate a battery from a circuit until a momentary switch is closed (Figure 1). Once the momentary switch is closed, the battery is connected to the rest of the circuit and it cannot be disconnected by further mechanical switch closures. This was just a proof-of-feasibility exercise and NOT a final implementation. However, it was a good example of how to use LTSpice to verify the first design concept and is worthwhile documenting here.

The circuit is a simple MOSFET/JFET latch combo. It has to meet a few requirements:

• Very low DC load on the battery when in isolation mode.
• Capable of latching with a charged battery (~3.5 V)
• Stay latched down to ~2.0 V.

Figure 2 shows the basic circuit. The switch S2 represents the momentary switch. MOSFETs M1 and M2 form the latch.

Figure 1: LTSpice Schematic of Battery Isolator Circuit.

Figure 3 shows my LTSpice simulation.

Figure 3: Simulation of the Battery Isolator Shown in Figure 2.

The simulation shows that the circuit works for this simple test case. Much more checking is needed. This is enough for now – I have shown that a circuit like this is possible. For those who are interested, my LTSpice schematic is available here.

Many thanks to Kurt Raichle for lending his superb analog expertise to this exercise.

## Computing Dates of Fathers Day with Excel

Quote of the Day

I think a man only needs one thing in life. He just needs someone to love. If you can't give him that, then give him something to hope for. And if you can't give him that, just give him something to do.

— Great quote from the movie 'Flight of the Phoenix.'

## Introduction

Table 1: Years where Fathers Day and My Anniversary Coincide.

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. Since the calculation is simple and table-oriented, I will use Excel for this project. Table 1 shows the years with Fathers Days that coincide with my anniversary. I generated this table by filtering the comprehensive list for all Fathers Days on the 16th. Figure 2 shows how I derived my Excel formula. My approach was to look at the latest possible Fathers Day and then determine a correction based on the day of the week that the latest possible Fathers Day falls on. Table 3 is the comprehensive list that shows the dates for every Father's day for the next 100 years.

For those who are interested, my Excel Workbook can be found here.

YearFather's Day
201916-June-2019
202021-June-2020
202120-June-2021
202219-June-2022
202318-June-2023
202416-June-2024
202515-June-2025
202621-June-2026
202720-June-2027
202818-June-2028
202917-June-2029
203016-June-2030
203115-June-2031
203220-June-2032
203319-June-2033
203418-June-2034
203517-June-2035
203615-June-2036
203721-June-2037
203820-June-2038
203919-June-2039
204017-June-2040
204116-June-2041
204215-June-2042
204321-June-2043
204419-June-2044
204518-June-2045
204617-June-2046
204716-June-2047
204821-June-2048
204920-June-2049
205019-June-2050
205118-June-2051
205216-June-2052
205315-June-2053
205421-June-2054
205520-June-2055
205618-June-2056
205717-June-2057
205816-June-2058
205915-June-2059
206020-June-2060
206119-June-2061
206218-June-2062
206317-June-2063
206415-June-2064
206521-June-2065
206620-June-2066
206719-June-2067
206817-June-2068
206916-June-2069
207015-June-2070
207121-June-2071
207219-June-2072
207318-June-2073
207417-June-2074
207516-June-2075
207621-June-2076
207720-June-2077
207819-June-2078
207918-June-2079
208016-June-2080
208115-June-2081
208221-June-2082
208320-June-2083
208418-June-2084
208517-June-2085
208616-June-2086
208715-June-2087
208820-June-2088
208919-June-2089
209018-June-2090
209117-June-2091
209215-June-2092
209321-June-2093
209420-June-2094
209519-June-2095
209617-June-2096
209716-June-2097
209815-June-2098
209921-June-2099
210020-June-2100
210119-June-2101
210218-June-2102
210317-June-2103
210415-June-2104
210521-June-2105
210620-June-2106
210719-June-2107
210817-June-2108
210916-June-2109
211015-June-2110
211121-June-2111
211219-June-2112
211318-June-2113
211417-June-2114
211516-June-2115
211621-June-2116
211720-June-2117
211819-June-2118
Figure 2: Rationale Behind Excel Formula. Table 2: Fathers Day for the Next 100 years.

## Air Conditioning Load of a Group of People

Quote of the Day

Concentration is my motto. First honesty, then industry, then concentration.

Figure 1: HVAC Load for 17 People.

I was reading an article about HVAC (Heating, Ventilation, and Air Conditioning) calculations in the Journal of Light Construction that had a quote I found interesting. It said that

… 17 extra occupants added more than a half ton of cooling load.

Figure 1 shows how to prove this statement assuming that the average person burns 2000 kilocalories a day, which is probably a low number. This problem is all about unit conversion: kilocalories, BTUs, tons of cooling, etc. You might wonder why an electrical engineer would worry about these sorts of things. Unfortunately, I frequently have to specify the cooling capacity required for the electronics that I put into rooms.

## Accelerometer Power Calculation Example

Quote of the Day

I planned on having one husband and seven children, but it turned out the other way around.

Figure 1: Monolithic Accelerometer from Bosch. (Source)

I am working on a product that uses a Bosch BMA253 accelerometer as a motion sensor. This family of products has become a defacto standard for inexpensive motion detection. In this post, I will provide a simple power calculation example along with some empirical data that Bosch provided me. Normally, I would not consider a simple power calculation worth writing about, but the datasheet did not provide a worked example. I also provide an Excel workbook that parameterizes the critical variables.

Here are the critical formulas for low power mode 1. For low power mode 2, the formulas are the same with the 1 subscript changed to 2.

 Eq. 1 $\displaystyle {{t}_{{active}}}=\left| \begin{array}{l}{{t}_{{ut}}}+{{t}_{{w,up1}}}-0.9\text{ ms, }bw\ge 31.25\text{ Hz}\\4\cdot {{t}_{{ut}}}+{{t}_{{w,up1}}}-0.9\text{ ms, otherwise}\end{array} \right.$ Eq. 2 $\displaystyle {{I}_{{DDlp1}}}=\frac{{{{t}_{{sleep}}}\cdot {{I}_{{DDsum}}}+{{t}_{{active}}}\cdot {{I}_{{DD}}}}}{{{{t}_{{sleep}}}+{{t}_{{active}}}}}$ Eq. 3 $\displaystyle {{t}_{{ut}}}=\frac{1}{{2\cdot bw}}$ Eq. 4 $\displaystyle {{f}_{{Output}}}=\frac{1}{{{{t}_{{active}}}+{{t}_{{sleep}}}}}$

where

• bw is the bandwidth setting of the digital filter.
• tut is the updated time for the filter output data.
• tw,up1 is the data acquisition stabilization time.
• tsleep is the time during which the BMA253 is put in suspend mode ("sleep").
• tactive is the time during which the BMA253 is stabilizing and acquiring data.
• fOutput is the output data rate from the digital filter.
• IDD is the current draw of the BMA253 when it is performing data acquisition ("active").
• IDDsum is the current draw of the BMA253 when it is in suspend mode ("sleeping").

Figure 2 illustrates the data acquisition timing.

Figure 2: Data Acquisition Timing.

I can use Equations 1-4 to generate Table 1, which assumes that

• bw= 1000 Hz
• tw,up1 = 1.3 ms
• IDD = 130 μA
• IDDsum = 2.1 μA

Table 1: Estimated and Measured BMA253 Current Draws.

What I liked about the data I received from Bosch is that it allows me to determine the accuracy of the current draw formula relative to the measured data.

## State Casualty Rates During the Vietnam War

Quote of the Day

It is foolish and wrong to mourn the men who died. Rather we should thank God such men lived.

— George S. Patton

## Introduction

Figure 1: Ten States with the Highest Military Death Rates During the Vietnam War. The population basis was 1975.

My first engineering manager was named Marl Godfrey. He was an excellent manager who also had keen insights into the human condition. These insights made quite an impression on my 22-year old self – I actually kept a notebook of his comments. Some of his most insightful comments were about the US military and the Vietnam War. Marl had grown up in Oklahoma and he had served in Vietnam. He once commented that Oklahoma had very aggressive draft boards, which resulted in Oklahoma having a relatively high death rate during the conflict. I was reminded of this statement when I recently reviewed my quote database. I thought that I should be able to determine how death rates varied by state during the Vietnam War, which is the subject of this post.

Figure 1 shows my results. I determined the rate based on the state populations during 1975, which was the year the war ended. Marl Godfrey was correct – Oklahoma had a very high death rate during the Vietnam War. I should mention that the rate ranking varies depending on the year from which the state population data was taken.

Figure 2 shows a graphic with all the state data plus Washington DC and the territories of Puerto Rico, Guam, and American Samoa. The range of rates is startling – Guam, in particular, paid a heavy price.

Figure 2: US Military Death Rates By State During Vietnam.

The US Army bore the brunt of the US deaths during the Vietnam War  (Figure 3).

Figure 3: US Military Death Percentages by Service.

For those who are interested, my Excel workbook is located here. The raw data came from this website.

## Liberty Ship Production Data

Quote of the Day

Logistics is the ball and chain of armored warfare.

## Introduction

Figure 1: Photograph of the USS John W. Brown, one of two Liberty Ships serving as museums (Source).

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.

## Background

Figure 2: Cross-Section of a Liberty Cargo Ship  (Source).

WW2 logisticians needed to balance performance and quality with time to build and deliver. The Liberty Ship was a prime example of this balancing act. It was a key contributor to the timely delivery of war materials to all fronts during WW2. Figure 2 shows the basic layout of a Liberty Ship configured for carrying cargo, which was its most common configuration. These ships could carry just over 10,000 tonnes of cargo. This meant that a Liberty Ship could carry 2,840 jeeps, 440 light tanks or 260 medium tanks, or 230 million rounds of rifle ammunition (Source).

The design of the Liberty Ship was very simple, which allowed it to be built by many shipyards. Its simple design also made it easily configurable for other applications. There were three basic types: Cargo, Tanker, and Collier. However, some of these ended up configured as hospital ships, floating maintenance platforms, boxed aircraft transports (i.e., carried aircraft that were in boxes), and troopships.

## Analysis

### Process

I found all the data that I needed in the tables within this pdf document. I extracted the tables using the free tool called tabula. I then used the regular expression processing ability of Notepad++ to tidy up the data for processing. The actual analysis was performed using Excel, Power Query, and pivot tables. My workbook and the associated text files are in this zip files.

### Summary Statistics

Table 1 shows the number of Liberty Ships built per year. As you can see, production peaked in 1943. This makes sense when you see that the US was preparing to supply its big push during 1944. Table 2 shows the number of Liberty Ships built per shipyard. There were 16 shipyards that laid keels for 2711 Liberty Ship – one ship, Louis C. Tiffany, was destroyed by fire before it was completed. Table 3 shows how the median number of days to produce a Liberty Ship varied by year. The median number of days required to product a Liberty Ship reached its minimum during the year when production peaked.

Table 1: Liberty Ships Built Per Year. Table 2: Liberty Ships By Shipyard.
Completed (Year)Number Completed
Grand Total2711
Fire During Construction1
19412
1942542
19431294
1944728
1945144
ShipyardShips Laid
Total Number of Ships Laid (i.e. Started)2711
Permanente Metals Co Yard489
Bethlehem-Fairfield Shipyards385
California Shipbuilding Corp336
Oregon Shipbuilding Corp322
New England Shipbuilding Corp244
Todd Houston Shipbuilding Corp208
Delta Shipbuilding Co188
North Carolina Shipbuilding Co126
J A Jones Construction Co (Panama City)102
Southeastern Shipbuilding Corp88
J A Jones Construction Co (Brunswick)85
St Johns River Shipbuilding Co82
Alabama Dry Dock Co20
Marinship Corp15
Walsh-Kaiser Co11
Kaiser Co10
Table 3: Median Days to Completion from Laying.
Laid_Down (Year) Median Days to Completion
1941225
194260
194339
194451
194572

## Conclusion

There was nothing pretty about a Liberty Ship – FDR called it "a dreadful-looking object." It provided sealift when needed to support the major campaigns of 1944 and 1945. It certainly had issues. It was vulnerable to U-boat attack because it was underpowered and slow. Also, a major problem was discovered after three ships split in two while operating in cold water (Figure 3). 30% of the Liberty Ship fleet eventually experienced the cracking problem (Source). A pioneering female metallurgist, Constance Tipper, discovered that the steel used in the Liberty Ships became brittle below a critical temperature. A series of remedies were provided the resolved the issue for later production runs. One contributing factor to the cracking problem was the extensive use of welding in the fabrication of the Liberty Ships. ww2 shipyards had relatively little experience with welding because previous ship designs had been built using rivets – a form of fastening that is much less susceptible to cracking issues, but not applicable to modern mass-production methods. Welding and design practices were eventually developed that made welding a mainstay of the shipbuilding industry.

Figure 3: Picture of the SS Schenectady after a cracking failure (Source).

Because the Liberty Ship's slow speed made it vulnerable to U-boats, the US developed the Victory Ship class that had more powerful engines and higher speed. This made it usable in high-speed convoys, which the lower speed U-boats had more difficulty engaging.

Figure 4 shows my summary of Victory Ship production during WW2. Here is my Victory Ship workbook for those who are interested. There were five wartime Victory Ship variants:

• VC2-S-AP2: 6,000 SHP steam turbine engine
• VC2-S-AP3: 8,500 SHP steam turbine engine
• VC2-M-AP4: single ship, MS Emory Victory, 5,850 SHP diesel engine
• VC2-S-A2: single ship, SS Sea Marlin, built to US Army requirements

This data ignores three Victory Ships made post-war by Alcoa in 1947.

Figure 4: Summary of Victory Ship Data.

## Shotgun Bore Diameter Math

Quote of the Day

A successful person finds right places for himself. But a successful leader finds the right place for others.

— John C. Maxwell

## Introduction

Figure 1: Relative Bore Diameters of Shotgun Gauges. (Source)

I have been doing some metalwork lately that involves using units of "gauge". You will find the term gauge used in the measurement of wire, metal thickness, and pipe bore diameter. This quaint, but confusing, measurement system is slowly falling out of favor (example, sheet metal thickness gauge).

One area where I do not see gauge measurements going away is in the specification of shotgun bore diameters. I was talking to friends recently about their recent hunting adventures, and the subject of shotgun gauges came up. During this conversation, I mentioned that in my youth I hunted using my grandfather's 10 gauge shotgun  – he called it his "goose gun". I was surprised to hear that some folks consider the 10 gauge shotgun obsolete (example). Their arguments were based on old 10 gauge guns only supporting limited chamber pressures compared to newer 12 gauge shotguns.

As we talked, I realized that I did not know how to convert between shotgun gauge and bore diameter. This post examines the derivation of a formula that relates shotgun gauge to bore diameter.

## Analysis

The Wikipedia defines shotgun gauge as

An n-gauge diameter means that a ball of lead (density 11.34 g/cm3 or 0.4097 lb/in3) with that diameter has a mass equal to 1/n part of the mass of the international avoirdupois pound (approx. 454 grams), that is, that n such lead balls could be cast from a pound weight of lead.

This means that a 10 gauge shotgun bore has the same diameter as a 1/10 pound ball of lead. The gauge number and the bore diameter are related by Equation 1.

 Eq. 1 $\displaystyle {{d}_{G}}\left( {{{n}_{{Gauge}}}} \right)=\frac{{1.67049}}{{n_{{Gauge}}^{{\frac{1}{3}}}}}\cdot \text{in}$

where

• dG is bore diameter of a shotgun of gauge nGauge
• nGauge is gauge of the shotgun in question.

Using Mathcad, we can derive Equation 1 as follows.

Figure 2: Derivation of Equation 1.

## Example Calculations

We can use Equation 1 to compute some common shotgun bore diameters. I will also compare my computed diameters with the diameters listed on a popular website. I will also compute the equivalent gauge of a 410 caliber shotgun.

Figure 3: Simple Calculation Examples Using Equation 1.

Posted in Metrology | 3 Comments

## Thanks Team

Quote of the Day

Thoroughly conscious ignorance is the prelude to every real advance in science.

Figure 1: A Gift From A Very Fine Engineer. Thanks, Becky.

I have now started on my next employment adventure. I can only say thanks to the wonderful team of people that I leave behind. They created the products that allowed the Fiber-To-The-Home (FTTTH) market to flourish. Tens of millions of FTTH products are now manufactured every year by companies around the world. These products are amazing in that for very low-cost they can contain such diverse technology: high-speed digital electronics, FPGAs, RF video,  telephony, battery backup, and wireless. The team can be proud of what they have done. You succeeded where many others failed.

Figure 1 shows something that is very special to me. I have stood while working for many years on a beat-up old plastic mat that I referred to as my "anti-cynicism mat." I always told folks that while I was on this mat, there would be no cynicism. I also warned certain people that they should never stand on my mat because their feet burn would through it. On my last day, an engineer presented me with the mat shown in Figure 1. It will occupy a place of honor in my new garage/workshop, and I will cherish it.