## Fighter Plane Statistics

Quote of the Day

A mathematician is a person who can find analogies between theorems; a better mathematician is one who can see analogies between proofs and the best mathematician can notice analogies between theories. One can imagine that the ultimate mathematician is one who can see analogies between analogies.

— Stefan Banach

## Introduction

Figure 1: F4 Phantom. This aircraft has the most kills of any active jet fighter. (Source)

I was reading  a post on Quora that contained the following statement fragment, "…  primarily the F16[,] which has more kills than any fighter jet in history."  The statement did not seem like it could be correct because the operational tempo of modern air-to-air operations is not like it was during the Vietnam War or the various Middle East conflicts (e.g Iran-Iraq War).

So I prowled around the web and found a site that seemed to have some good information on the combat history of active jet fighters, including the F-16. I thought I would use this question as a vehicle for sharpening my Power Query and Excel web scraping skills by making  comparison table between the active duty fighter jets.

## Background

All the data for this post came from this web site. I did find a couple of inconsistencies in the data the listed loss totals for the MiG-21, MiG-23 and Mirage F.1 were off a small amount from the sum of the individual battle totals. I used the sum of individual battle totals here.

## Analysis

I used Power Query to access the data and clean it up. I then used a pivot table to summarize the results. If you are interested in my workbook, I include it here. Because there was so much uncertainty in the data for ground losses, I did not include that data in the table.

Here are my results. As I suspected, the list is led by a couple of old warhorses, the F-4 and the MiG-21. The F-16 is sixth on the list.

Figure 2: Table of Fighter Victories and Losses.

## Conclusion

• Most of the F-14's combat kills came when it was used by Iran in the Iran-Iraq War.  Of the 135 kills, only 5 involved the US.
• While not shown in Figure 2, the F-4 suffered 545 losses due to ground fire, 447 of which were during the Vietnam War. This is far more than were lost in air-to-air combat. I assume most of these losses were on ground attack missions – very difficult work.
• While the Sea Harrier is considered ground attack aircraft, its record of 21 kills with no losses in the Falklands shows that it has some air-to-air capabilities.
• The kill-to-loss ratio for the MiGs does not appear very good. However, most of these losses involved non-Russian pilots flying export versions of these craft.

Save

Save

## Using Excel's Icon Sets for Testing Equality -- Ugh

Quote of the Day

The greatest mistake we make is living in constant fear that we will make one.

— John Maxwell

## Introduction

Figure 1: Standard Icon List Choices.

I use Excel everyday, but that does not mean that I have used every feature. Yesterday, I was asked to prepare an analysis for our marketing group of the maximum possible distances over which a customer can be served using various types of fiber optic communication systems – we call this parameter "reach". During this analysis, I saw what I thought was an ideal opportunity to use Excel's icon sets for the first time (Figure 1).

I was simply going to generate a check mark if two figures agreed and an X if two figures disagreed – there are many ways to accomplish this task, but I decided to try something new today. What appeared to be a simple application ended up being more difficult than I thought it should be. In this post, I will show how I resolved the issue.

A sample workbook is included here.

## Background

When I do analysis work using a spreadsheet, I like to include some side calculations that I call "check figures", which are non-essential calculations that provide indications that my overall calculations are at least consistent with each other. I include check figures in all my critical spreadsheet work because it is so difficult to catch spreadsheet errors. To remind me of how difficult it is to catch spreadsheet errors, I keep a list of disasters caused by spreadsheet errors (e.g. a spreadsheet error was at the heart of the London Whale incident). The problem of spreadsheet errors has even been discussed in the New York Times. Of course, simple math errors plague even peer-reviewed research papers.

To improve the quality of my spreadsheet work, I keep a close watch on the work of the Spreadsheet Lab at Delft University. These folks are at the cutting edge of helping spreadsheet users improve their quality. For a good presentation on their work, see the video in Figure 2.

 Figure 2: Good Video Briefing on Spreadsheet Work.

## Application

### Problem Description

I want to indicate that two cell are either equal or different using a check mark or "X", respectively, which I illustrate in Figure 3.

Figure 3: Example of My Desired Output.

### Issues

There were two issues that I encountered while trying to perform this simple task:

• I could not use relative addressing.
I was floored to find out that I could not simple point at the reference cell to which I wanted to compare my cell contents. I was able to solve this problem by using the offset function to create a reference without any relative addressing (Figure 4). I should mention that I try not to use the offset function as a matter of principal because it is volatile. I will experiment with using non-volatile functions (e.g. Index) later.

Figure 4: Cell Reference with No Relative Addressing.

• I could not simply copy the format from the first item to all others.
I had to copy the format from the first cell (C9) to all others one at a time. What a pain!

### Icon Set Dialog Configuration

Figure 5 shows my Icon Set dialog box configuration. My logic is simple – I put in an "X" in the cell if the two values are different (> or <), and a check mark in the cell if they are both ≤ and ≥ (i.e. equivalent of =).

Figure 6: Icon Set Dialog Box Configuration.

## Conclusion

I was able to make the icon set feature work for my simple application, but I could not imagine a more painful way for Microsoft to have designed this feature.

Save

Save

Save

Save

Save

Save

Save

Posted in Excel | 7 Comments

## Battery Room Ventilation Math

Quote of the Day

We didn't lose the war for that, but I don't know why we didn't.

Admiral Leahy on Admiral Halsey's actions at the Battle of Leyte Gulf. As I have read more history of WW2, I have come to see that Admiral Halsey had issues – he was way too aggressive. I have also come to admire the thoughtful leadership of Spruance and Leahy.

## Introduction

Figure 1: Photograph of a 2001 Battery Room Explosion in Sacramento. The ventilation in the room had failed and the alarms were ignored. Once the gas concentration had built up sufficiently, all that was needed for an explosion was an ignition source – telecom equipment rooms are full of ignition sources. (Reference)

I recently had an engineer ask me how to determine the ventilation requirements for a battery room that contains lead-acid batteries being charged. As I have discussed in previous posts (here), lead-acid batteries often release hydrogen gas while being charged. Because hydrogen gas is explosive for concentration levels from 4% to 94% (reference), care must be taken to ensure that the hydrogen gas levels in a battery room do not rise to these concentration levels. Safe operation is usually maintained by ensuring that the battery room is properly ventilated. Of course, ventilation systems can fail, which means that battery rooms must have hydrogen sensors to generate alarms in the event of a ventilation failure. I have included references to some well-known hydrogen explosions in Appendix B.

In this post, I will be looking at how these ventilation calculations are performed. My focus here is on standard wet-cell batteries, which simply release any gases generated during charging. Other battery types, like Absorbed Glass Mat (AGM), will attempt to recombine the H2 and O2 released during charging. The AGM battery's internal gas recombination will reduce the amount of H2 released by these batteries – example shown here. I will compare the output of my model to those generated by a number of web-based and textbook sources. The results are in good agreement.

I have included my Mathcad (source and PDF) and Excel version here. The Excel version includes a number of scenarios that compare my worksheet results to the output from various web-based tools. There is a small macro in the worksheet that allows me to choose the scenario desired from a pick list.

## Background

### Definitions

Outgassing
In the case of a battery, outgassing is the undesired release of H2 and O2 gas during the charging process.
Float Voltage
Float voltage is the voltage at which a battery is maintained after being fully charged to maintain that capacity by compensating for self-discharge of the battery. (Source)
Charge Capacity
The charge capacity of a battery is defined as the total charge available from a battery under a constant current load over a specific time interval – usually 20 hours, but discharge intervals of 4, 6, 8, and 10 hours are also used. The choice of time interval is driven by the application. For example, telecom applications are usually required to have a backup time guarantee of 8 hours with a battery that has lost 20% of its charge capacity due to aging. This means the battery must have an initial charge rating specified for 10 hours (i.e. 10 hours · [100% - 20%]=8 hours).
The charge is measured in units of amp-hours (A-hr).
C-Rate
C-rate is the theoretical current that can be drawn in one hour from a battery of nominal capacity. For example, a 10 A-hr battery has a c-rate of 10 A. A battery's charge and discharge currents are often normalized with respect to c-rate. For example, we would refer to 1 A load from a 10 A-hr battery as a 0.1 c-rate load (=1 A/ 10 A).

### Battery Outgassing Basics

#### Why Do Batteries Outgass?

Charging a battery means redepositing lead on the negative terminal and lead oxide on the positive terminal. Because no chemical process is perfectly efficient,  some of the charge current inevitably ends up electrolyzing the water in the electrolyte instead of charging the battery. The electrolysis process releases H2 and O2 molecules – an explosion hazard will exist if the H2 is allowed to accumulate. This electrolysis is inevitable because water electrolyzes at voltages greater than 1.227 V and the battery has a cell voltage greater than 1.75 V. The rate of outgassing increases dramatically with the cell voltage.

Many battery applications involve using batteries as a backup energy source. Ideally, these batteries are kept in a state of full charge until needed. However, all batteries have internal loss mechanisms that require a small charge current be continually applied to make up for these internal losses – we call this float charging. Feeding a constant charge current into a fully charged battery causes the battery to continuously generate H2 and O2. If you drive the battery with too much current at too high a temperature, it can also cause the battery to thermally runaway.

#### Key Points to Remember

The following list summarizes the key points associated with battery outgassing:

• A battery will release H2 when it is being charged.
• When being overcharged, each cell will release H2 at a rate proportional to the amount of excessive charge current. The rate of gas generation, RG, is given by Equation 1.
 Eq. 1 $\displaystyle {{R}_{G}}=7.607\cdot \frac{{\text{mL}}}{{\text{min}}}\cdot {{I}_{{Overcharge}}}\cdot {{N}_{{CellsPerBattery}}}\cdot {{N}_{{Batteries}}}\cdot \left( {1+\frac{{T-{{T}_{{Ref}}}}}{{{{T}_{{Ref}}}}}} \right)$

where

• NCellsPerBattery is the number of cells per battery.
• NBattery is the number of batteries in the room.
• IOvercharge is the amount of current going into gas generation.
• TRef is the reference temperature (77 °F) for the nominal gas generation rate of 7.607 mL/min·amp. (derivation)
• T is the battery temperature.
• The charge current used under float conditions is usually specified as a percentage of the c-rate of the battery. I commonly see float charge currents from 1% to 5% of the c-rate. The choice of charge rate depends on the self-discharge rate of the lead-acid battery chosen. This rate can vary widely based on the chemistry of the battery (e.g. lead-calcium vs lead-antimony).

### Ventilation Basics

Ventilation requirements are usually expressed in terms of the rate of air movement (e.g. cubic feet per minute or CFM) or the air exchange rate, which is the rate at which the entire volume of air in the room is replaced.

The required air flow rate, F,  is dependent only the rate of H2 generation and the required dilution level (Equation 2).

 Eq. 2 $\displaystyle F=\frac{{{{R}_{G}}}}{{{{k}_{{H2Limit}}}}}$

where

• kH2Limit is the maximum percentage of H2 gas allowed in the room.

The rate of air exchange is simple to compute given the volume of the battery room (VRoom) and the flow rate (F).

 Eq. 3 $\displaystyle {{R}_{{Exchange}}}=\frac{F}{{{{V}_{{Room}}}}}$

## Analysis

### Setup

Figure 2 shows how I setup the calculations. It also includes some reference links that I used to test my routine. The gas generation rate function is the key utility function.

Figure 2: Calculation Setup.

### Flow and Exchange Rate Functions

Figure 3 shows the important ventilation function: air flow rate (F) and rate of exchange  (RExchange). These functions are related by the volume of the room (VRoom).

Figure 3: Air Flow and Exchange Formulas.

### Worked Example

I worked the following example using the SBS battery room ventilation site and obtained the same result as my Mathcad and Excel routines (Figure 4). I actually worked many more examples, which are included in the attached Mathcad and Excel material.

Figure 4: Worked Example Using SBS Calculator.

## Conclusion

In this post, I provided Excel and Mathcad models for computing the ventilation requirements for a battery room. I included worked examples that showed that my routine provides the same results as some web-based tools – I even found an error in one online example.

I should mention that the Excel version provides a nice example of how to use Excel's scenario manager with data validation to provide a easy to use tool for engineers.

## Appendix A: Rate of Gas Generation Per Ampere of Current

Figure 5 shows how to derive the hydrogen gas generation constant.

Figure 5: Quick Derivation of Gas Generation Rate.

## Appendix B: Examples of Hydrogen Gas Explosions

• USS Cochino, a US diesel-electric submarine that experienced a hydrogen gas explosion.
• LZ129 Hindenburg, famous airship explosion.
• USS Scorpion, a US nuclear submarine that some feel (e.g. Rear Admiral David Oliver) was lost due to a hydrogen explosion.

## Effective R Value of Common Wall Construction Methods

Quote of the Day

I’ve finally stopped getting dumber.

— Epitaph on the headstone in Budapest of Paul Erdös, one of the world’s most prolific producers of mathematics.

## Introduction

Figure 1: My Current Cabin. It is being torn down next year. I will not miss it.

I have contracted with a firm to build an insulated, steel-sided, garage at my lake site in Northern Minnesota, which they will complete building sometime in December. Next summer, I have a contractor starting construction of a new cabin about 50 meters from the garage. The new cabin will replace my old cabin (Figure 1), which is an old hunting shack that was built back in the 1930s. Because the winters are hard in this part of the country, I have become interested in the insulation value of different types of wall construction. My contractor has his preferred approach (2x6 conventional framing), and I am curious as to how it compares with other framing methods.

In this post, I will put together a simple model for computing the R-value of different types of wall construction. This analysis will provide me a quantitative basis for understanding which methods are the most economically sound.

For those who are interested, I have included my Mathcad source and its PDF here.

## Background

### Definitions

Thermal Conductivity ([κ] = W/m-K)
Thermal conductivity is the property of a material to conduct heat. Heat transfer occurs at a lower rate through materials of low thermal conductivity than through materials of high thermal conductivity. The thermal conductivity of a material usually depends on its temperature – this means that thermal analysis is non-linear in general. It is analogous to electrical conductivity.
Thermal Resistivity ([Rλ]=m-K/W)
The reciprocal of thermal conductivity.
R-value aka Thermal Insulance ([R]=m2K/W or ft2·°F·hr/Btu)
It is the thermal resistance of a unit area of a material. This is the most common way for the insulation properties of building materials to be specified.
Thermal Resistance (Rθ=K/W)
Thermal resistance relates the change in temperature across a material to the heat flow through the material. We define thermal resistance as ${{R}_{\theta }}\triangleq \frac{{\Delta T}}{Q}$.
Thermal resistances combine just like electrical resistances. This means that you can model the thermal resistance of composite structure using series and parallel thermal resistances.
You can relate thermal resistance to R-value using the formula $Q=\frac{{\Delta T}}{{{{R}_{\theta }}}}=\Delta T\cdot \frac{A}{R}$, where Q is the heat flow through a specific piece of material, A is the area of the surface with an R-value of R,.
Thermal Bridging
Thermal bridging occurs when relatively conductive material allows an easy pathway for heat flow around a thermal barrier, like insulation. In electronics, we call the comparable phenomena leakage. The most common form of thermal bridging occurs when wall studs and headers allow heat to flow around insulated wall cavities. I really did not understand the importance of thermal bridging until I used an infrared camera to photograph one of my walls. You could clearly see the framing members (Figure 2). The dark areas indicate lower temperature, which means leakage of heat.

Figure 2: Thermal Photograph of a Stud-Framed Wall and Ceiling in My Montana Condo.

Framing Factor
The percentage of the outer surface of a home with a thermal bridge of framing material between the indoor and the outdoor.

### Wall Construction Methods

Building Sciences Corporation has an excellent discussion on the differences between conventional framing and advanced framing, and I strongly encourage you to visit their site.

There are two main types of residential framing today:

• Conventional framing
Also called platform framing, it started in the 1930s as a way to reduce the fire risk associated with balloon framing.
This approach to framing has been coming on strong since 2000. It uses various techniques to reduce both thermal bridging and the amount of wood used.

#### Conventional Framing

Virtually all homes today are built using conventional framing, which has been the standard since the 1930s and is nicely illustrated in by Figure 3. For the studs that surround the heated rooms, insulation usually fills the gaps between the studs. Conventional framing has the advantages of being simple and using inexpensive materials, but it has the disadvantage of having many paths for heat to flow around insulation through thermal bridging. This makes it expensive to heat or cool.

Figure 3: Example of Traditional Framing. (Source)

Advanced framing methods have two purposes: (a) more efficient use of materials, and (b) more thermally efficient, which it accomplishes by reducing the amount of framing material that will act as a thermal bridge. Figure 4 provides a good example of some of the techniques used with advanced framing.

Figure 4: Example of Advanced Framing Techniques. (Source)

### Computing Wall Component R Values

Figure 5 shows my wall construction model. The variables are all associated with wall construction, i.e. on-center stud separation or pitch, and the wall thickness (τ).

Figure 5: Wall Construction Model.

Figure 6 shows how I defined my R value units and the R values I used for specific components (Source).

Figure 6: R Values Used in this Post.

## Analysis

Figure 7 shows my calculations for the R value of a home as a function of (1) the stud pitch, (2) conventional vs advanced framing, and (3) 2x4 versus 2x6 studs. Both the stud pitch and framing type are handled by way of a parameter called the framing factor (FF). My FF values came from this document. The stud width is represented by the variable τ.

Figure 7: Table of R Values.

## Conclusion

Here is a larger version of the table to make it easier to read.

Figure 8: Table of R Values By Construction Method.

There are several conclusions that I can draw from my table:

• Switching from a 16" stud pitch to a 24" stud pitch makes little thermal difference (~0.7R) , but it will reduce the amount of framing material I need.
• Switching from conventional framing to advanced framing makes little thermal difference (<1R).
• Using 2x6 studs instead of 2x4 studs makes a big difference (~5R).
• Moving from conventionally framed 2x4s with a 16" pitch to advanced framed 2x6s with a 24" pitch gives me 6.7R of additional insulation value – this is a big deal.

There are additional things I can do to improve the R value of my construction practices, like adding a layer of insulation. These changes can easily be added to my model.

Save

## Effect of Wire Length on Surge Protector Let-Through Voltage

Quote of the Day

You know how an economist gets out of a 20-foot deep hole? He assumes the existence of a 20-foot long ladder.

— Unnamed politician giving his opinion on economists

## Introduction

Figure 1: Common Surge Protector. Notice the 10 AWG (6 mm2) hookup wires. (Source)

I have been looking at different options for providing surge protection on some AC circuits. During my investigations, I started read about the surge protector shown in Figure 1, which is a commonly deployed unit that is well-thought of and has an excellent history in the field.

While reading about how these units worked, I noticed that the amount of surge voltage they let pass (called let-through voltage) is a function of the hookup wire length. The units are tested with a hookup length of 6 inches, and the user is warned that the let-through voltage increased by ~20 V per inch of additional wire. I became curious about the origin of this rule of thumb. In this post, I will show you how to calculate the rule of thumb for yourself.

While most of my surge stories are related to lightning strikes, I do have one story that shows that surges can occur for other reasons. One of our customers had one of our products on the same AC circuit as a very large copy machine – the largest I have every seen. This copy machine continuously generated enormous power surges  that actually wore our surge protectors out. I resolved the issue by convincing the customer to put the copy machine on a different circuit, and the problem vanished.

## Background

### Definitions

Let Through Voltage (VLT)
Voltage let-through refers to the amount of transient voltage passed through a power conditioning device to the load. A transient is a high amplitude, short duration spike or surge superimposed on the normal waveform. (Source)
Self-Inductance (LWire)
Self inductance is defined as the induction of a voltage in a current-carrying wire when the current in the wire itself is changing. In the case of self-inductance, the magnetic field created by a changing current in the circuit itself induces a voltage in the same circuit. Therefore, the voltage is self-induced. (Source)
Metal Oxide Varistor (MOV)
A varistor is an electronic component with an electrical resistance that varies with the applied voltage. A metal oxide varistor is a type of varistor that contains a ceramic mass of zinc oxide grains, in a matrix of other metal oxides (such as small amounts of bismuth, cobalt, manganese) sandwiched between two metal plates (the electrodes). The boundary between each grain and its neighbors forms a diode junction, which allows current to flow in only one direction. The mass of randomly oriented grains is electrically equivalent to a network of back-to-back diode pairs, each pair in parallel with many other pairs. When a small or moderate voltage is applied across the electrodes, only a tiny current flows, caused by reverse leakage through the diode junctions. When a large voltage is applied, the diode junction breaks down due to a combination of thermionic emission and electron tunneling, and a large current flows. The result of this behavior is a highly nonlinear current-voltage characteristic, in which the MOV has a high resistance at low voltages and a low resistance at high voltages. (Source)

### Lightning Surge Model

Figure 2 shows how a surge protector is rated. For the example I will work here, we will be using a 3000 A spice with an 8 μs rise time and 20 μs fall time.

Figure 2: Text Description of Surge Test Waveform.  (Source)

Figure 3 shows what a typical surge spike looks when not driving the low-resistance of the MOV-based surge protector. The presence of a surge protector will put a large load on this waveform and dramatically reduce the peak level down to the let-through voltage. However, the current will surge up to 3000 A.

Figure 3: Surge Voltage Test Waveform. (Source)

Figure 4 shows impact of the surge voltage looks like with different lead lengths. The amplitude reduction is dramatic.

Figure 4: Surge Voltage vs Lead Length. (Source)

### Video Briefing

Figure 5 shows a good demonstration of how a surge protector is built and how it works.

 Figure 5: Good video briefing on Eaton Surge Protectors.

### Installation Model

Figure 6 shows how the effect of lead wire inductance is modeled.

Figure 6: Surge Protector Installation Diagram. (Source I modified to include variable names)

## Analysis

### Inductance Modeling

Figure 7 shows a commonly used formula for the self-inductance of a cylindrical wire.

Figure 7: Clip from Rosa Reference. This formula gives the inductance of a straight wire segment in nH when all dimensions are in cm. (Source)

I usually see the formula of Figure 7 expressed in terms of the ratio of dimensions, which I derive in Figure 8.

Figure 8: Derivation of a Common Alternative Form of Rosa's Formula.

### Wire Dimension Modeling

Figure 9 shows how I used a formula from the Wikipedia to convert wire gauge values into metric diameters. I also put some check figures in my worksheet to show the accuracy of this formula, which is within 0.032% of true over the range of values for which I am interested.

Figure 9: Simple Equation to Compute Diameter from American Wire Gauge Value.

### Let-Through Voltage Due to Lead Length Calculations

Figure 10 shows how to compute the surge voltage across the two lead wires. I am ignoring any contribution from ohmic losses – only inductive effects are modeled.

Figure 10: Calculation of Surge Voltage Across Both Leads.

## Conclusion

I was able to show why the surge protector vendors often warn engineers that every inch of lead wire will increase the let-through voltage by 20 V per inch. This agrees with my constant admonishment to junior engineers about "keeping your leads short".

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

## Video of Wild Cat in Northern Minnesota

Quote of the Day

Hope without a plan is denial.

— Time management expert

Like me, a number of my coworkers have cabins in Northern Minnesota and most of us have cameras that record activity on our properties. A coworker came in the other day with this video that shows a wild cat going by one of his cameras. I am not sure what kind of cat it is – probably a lynx. This site is not far from the Canadian border. Maybe one of you out there can identify it?

## Probability That An Old President Does Not Finish Their Term

Quote of the Day

A man always has two reasons for the things he does -- a good one and the real one.

— J.P. Morgan. I have frequently have found this statement to be true.

## Introduction

Figure 1: Ronald Reagan, The Oldest US President at Inauguration (Wikipedia).

Ronald Reagan (Figure 1) was our oldest president at the time of inauguration  – 69 years 349 days old. The 2016 US presidential election is giving us a choice of two candidates that will be relatively old at inauguration: Donald Trump (70 years, 220 days), and Hilary Clinton (69 years, 86 days). Since US presidents often serve 2 terms, it is conceivable they we may have a 77- to 78-year old president in 2024. This fact makes me curious as to what is the likelihood that a 70 year-old's natural life will be long enough for them to serve one or two terms.

We can easily compute this probability using an actuarial life table. In this post, I will compute the probability that a 70-year old male or female will complete one or two 4-year terms.

## Background

The basic math behind the life table is well covered in the Wikipedia, and I will refer you there the details.

## Analysis

I have included an actuarial life table in Appendix A. The actual calculations are easily performed using a filtered pivot table version of the life table, which I show in Figure 2.

Figure 2: Calculation of the probability a 70 -year old will not live to be 74 or 78-years old.

This data is interesting:

• A 70-year old male has a 10% chance of dying by 74 and a 23% chance of dying by 78.
• A 70-year old female has a 7% chance of dying by 74 and a 17% chance of dying by 78.

## Conclusion

There is a ~23% chance that a 70-year old male president would not be able to complete 2 terms, which is about what I would have expected. There is ~17% chance that a 70-year old female president would not be able to complete 2 terms, which is higher than I would have expected.

### Appendix A: US Social Security Administration Life Table.

Table 1 is the 2013 life table used by the Social Security Administration (SSA). The analysis in this post consists of filtered pivot table version of this date.

Table 1: 2013 Actuarial Life Table from the SSA.
Age Male Prob of Death Males Alive Male Life Exp. Female Prob of Death Female Alive Female Life Exp.
0 0.006519 100000 76.28 0.005377 100000 81.05
1 0.000462 99348 75.78 0.000379 99462 80.49
2 0.000291 99302 74.82 0.000221 99425 79.52
3 0.000209 99273 73.84 0.000162 99403 78.54
4 0.000176 99252 72.85 0.000133 99387 77.55
5 0.000159 99235 71.87 0.000119 99373 76.56
6 0.000146 99219 70.88 0.000109 99361 75.57
7 0.000133 99205 69.89 0.000101 99351 74.58
8 0.000118 99192 68.9 0.000096 99341 73.58
9 0.000102 99180 67.9 0.000093 99331 72.59
10 0.000091 99170 66.91 0.000094 99322 71.60
11 0.000096 99161 65.92 0.0001 99312 70.60
12 0.000128 99151 64.92 0.000112 99303 69.61
13 0.000195 99138 63.93 0.000134 99291 68.62
14 0.000288 99119 62.94 0.000162 99278 67.63
15 0.000389 99091 61.96 0.000194 99262 66.64
16 0.000492 99052 60.99 0.000226 99243 65.65
17 0.000607 99003 60.02 0.000261 99220 64.67
18 0.000735 98943 59.05 0.000297 99194 63.68
19 0.000869 98870 58.09 0.000334 99165 62.70
20 0.001011 98785 57.14 0.000373 99132 61.72
21 0.001145 98685 56.2 0.000412 99095 60.75
22 0.001246 98572 55.27 0.000446 99054 59.77
23 0.001301 98449 54.33 0.000472 99010 58.80
24 0.001321 98321 53.4 0.000493 98963 57.82
25 0.00133 98191 52.47 0.000513 98915 56.85
26 0.001345 98060 51.54 0.000537 98864 55.88
27 0.001363 97928 50.61 0.000563 98811 54.91
28 0.001391 97795 49.68 0.000593 98755 53.94
29 0.001427 97659 48.75 0.000627 98697 52.97
30 0.001467 97519 47.82 0.000664 98635 52.01
31 0.001505 97376 46.89 0.000705 98569 51.04
32 0.001541 97230 45.96 0.000748 98500 50.08
33 0.001573 97080 45.03 0.000794 98426 49.11
34 0.001606 96927 44.1 0.000845 98348 48.15
35 0.001648 96772 43.17 0.000903 98265 47.19
36 0.001704 96612 42.24 0.000968 98176 46.23
37 0.001774 96448 41.31 0.001038 98081 45.28
38 0.001861 96277 40.38 0.001113 97979 44.33
39 0.001967 96097 39.46 0.001196 97870 43.37
40 0.002092 95908 38.53 0.001287 97753 42.43
41 0.00224 95708 37.61 0.001393 97627 41.48
42 0.002418 95493 36.7 0.001517 97491 40.54
43 0.002629 95262 35.78 0.001662 97343 39.60
44 0.002873 95012 34.88 0.001827 97182 38.66
45 0.003146 94739 33.98 0.002005 97004 37.73
46 0.003447 94441 33.08 0.002198 96810 36.81
47 0.003787 94115 32.19 0.002412 96597 35.89
48 0.004167 93759 31.32 0.002648 96364 34.97
49 0.004586 93368 30.44 0.002904 96109 34.06
50 0.005038 92940 29.58 0.003182 95829 33.16
51 0.00552 92472 28.73 0.003473 95524 32.27
52 0.006036 91961 27.89 0.003767 95193 31.38
53 0.006587 91406 27.05 0.004058 94834 30.49
54 0.00717 90804 26.23 0.004352 94449 29.62
55 0.007801 90153 25.41 0.004681 94038 28.74
56 0.008466 89450 24.61 0.00504 93598 27.88
57 0.009133 88693 23.82 0.0054 93126 27.01
58 0.009792 87883 23.03 0.005756 92623 26.16
59 0.010462 87022 22.25 0.006128 92090 25.31
60 0.011197 86112 21.48 0.006545 91526 24.46
61 0.012009 85147 20.72 0.007034 90927 23.62
62 0.012867 84125 19.97 0.007607 90287 22.78
63 0.013772 83042 19.22 0.008281 89600 21.95
64 0.014749 81899 18.48 0.009057 88858 21.13
65 0.015852 80691 17.75 0.009953 88054 20.32
66 0.017097 79412 17.03 0.01095 87177 19.52
67 0.018463 78054 16.32 0.01201 86223 18.73
68 0.019959 76613 15.61 0.013124 85187 17.95
69 0.021616 75084 14.92 0.01433 84069 17.18
70 0.023528 73461 14.24 0.015728 82864 16.43
71 0.025693 71732 13.57 0.017338 81561 15.68
72 0.028041 69889 12.92 0.019108 80147 14.95
73 0.030567 67930 12.27 0.021041 78616 14.23
74 0.033347 65853 11.65 0.023191 76961 13.53
75 0.036572 63657 11.03 0.025713 75177 12.83
76 0.040276 61329 10.43 0.028609 73244 12.16
77 0.044348 58859 9.85 0.03176 71148 11.50
78 0.048797 56249 9.28 0.035157 68888 10.86
79 0.053739 53504 8.73 0.03892 66467 10.24
80 0.059403 50629 8.2 0.043289 63880 9.64
81 0.065873 47621 7.68 0.048356 61114 9.05
82 0.073082 44484 7.19 0.054041 58159 8.48
83 0.08107 41233 6.72 0.060384 55016 7.94
84 0.089947 37890 6.27 0.067498 51694 7.42
85 0.099842 34482 5.84 0.075516 48205 6.92
86 0.110863 31040 5.43 0.084556 44565 6.44
87 0.123088 27598 5.04 0.094703 40796 5.99
88 0.136563 24201 4.68 0.106014 36933 5.57
89 0.151299 20896 4.34 0.118513 33017 5.17
90 0.167291 17735 4.03 0.132206 29104 4.80
91 0.18452 14768 3.74 0.147092 25257 4.45
92 0.202954 12043 3.47 0.163154 21542 4.13
93 0.222555 9599 3.23 0.180371 18027 3.84
94 0.243272 7463 3.01 0.198714 14775 3.57
95 0.263821 5647 2.82 0.217264 11839 3.34
96 0.283833 4157 2.64 0.235735 9267 3.12
97 0.302916 2977 2.49 0.25381 7083 2.93
98 0.320672 2075 2.36 0.271155 5285 2.76
99 0.336706 1410 2.24 0.287424 3852 2.60
100 0.353541 935 2.12 0.30467 2745 2.45
101 0.371218 605 2.01 0.32295 1909 2.30
102 0.389779 380 1.9 0.342327 1292 2.17
103 0.409268 232 1.8 0.362867 850 2.03
104 0.429732 137 1.7 0.384639 541 1.91
105 0.451218 78 1.6 0.407717 333 1.78
106 0.473779 43 1.51 0.43218 197 1.67
107 0.497468 23 1.42 0.458111 112 1.56
108 0.522341 11 1.34 0.485597 61 1.45
109 0.548458 5 1.26 0.514733 31 1.35
110 0.575881 2 1.18 0.545617 15 1.26
111 0.604675 1 1.11 0.578354 7 1.17
112 0.634909 0 1.04 0.613055 3 1.08
113 0.666655 0 0.97 0.649839 1 1.00
114 0.699987 0 0.9 0.688829 0 0.92
115 0.734987 0 0.84 0.730159 0 0.85
116 0.771736 0 0.78 0.771736 0 0.78
117 0.810323 0 0.72 0.810323 0 0.72
118 0.850839 0 0.67 0.850839 0 0.67
119 0.893381 0 0.61 0.893381 0 0.61

## Quick Look at a High-Power PoE Graph

Quote of the Day

Whatever you are, be a good one.

— Abraham Lincoln

## Introduction

Figure 1: Graphic From High-Power PoE Presentation.

I have been sitting in a meeting on a high power version of Power over Ethernet (PoE) known as IEEE 802.3bt. It supports 90 W of output power with a guarantee of 71 W at the load. During the talk, Figure 1 was discussed (my version of the chart). When I am given some mathematical information, I like to experiment with it to see if I understand what I am being told.

In this post, I am going to summarize some quick calculations that I did while sitting in the meeting that helped me understand how this proposed version of PoE transfers power over a data cable. The math shown is simple, but of a type that is very common for a working electrical engineer.

## Background

### Definitions

Power over Ethernet (PoE)
PoE is IEEE 802.3af/at which supports sending power and data over the same category 5e Ethernet cable, which contains four wire-pairs (i.e. 8 wires total). PoE is enormously popular because only one cable is required to network an Ethernet-fed device, which greatly reduces the cost and complexity of networking remote devices, like cameras. For the version of PoE discussed in this post, power is transmitted over two wire-pairs by applying a DC voltage between each pair (see Figure 1). Superimposing DC on the wire-pairs does not interfere with data transmission because Ethernet uses differential signaling.
Type 1 PoE
Type 1 PoE is an IEEE standard (802.3af) for transferring as much as 13 W over an Ethernet cable.
Type 2 PoE
Type 2 PoE is an IEEE standard (802.3at) for transferring as much as 25.5 W over an Ethernet cable. The standard is also known as  "PoE+".
Power Supplying Device (PSE)
A PSE is a device that provides power on an Ethernet cable.
Powered Device (PD)
A PD is a device powered by a PSE.
IEEE 803.3bt
A proposed version of PoE that can transmit as much as 71 W over an Ethernet cable. The extra power is achieved by using all four available wire pairs (8 wires total).

### Post Objective

My objective with this post is to show how a number of important features of the proposed PoE version can be derived from Figure 1.

## Analysis

### Key Graph Characteristics

Figure 1 is a simple graph:

• It is linear with a y-intercept of 90 W (the maximum allowed source power) and a slope of -0.19 W/m.
• The graph ends with a power of 71 W and a range of 100 m, which is the maximum reach guaranteed for 1000Base-T Ethernet.
• The power source have an output voltage that must be greater than 52 V with a load of 90 W.

We also need know that standard Category 5e (Cat 5e) Ethernet cable is composed of 4 pairs of 24 American Wire Gauge (AWG) wire.

### Calculations

#### Wire Resistance Utility Functions

Figure 2 shows the utility functions that I use to compute the resistance of annealed copper wire as a function of temperature, length, and gauge. I have used these functions for years  – they are based on curve fits of old Bellcore wire resistance data.

Figure 2: Utility Functions for Computing Wire Resistance as a Function of Temperature, Length, and Wire Gauge.

#### Resistance Model

Figure 3 shows the circuit model for this PoE circuit.

Figure 3: PoE Resistance Model. L is the total length of the wire, R is the total resistance of the wire, and λ is the 2-way resistance of the wire.

#### Maximum Supply Current (IMax) and Line Resistance Per Meter (λ)

Figure 4 shows how to use Ohm's law with Figure 1 to determine (a) the maximum current on the line, and (b) the two-way resistance of the line per meter, and (c) the wire gauge that meets this resistance requirement. As you would expect, 24 AWG wire meets the requirements for the PoE standard and is the wire gauge used in Cat 5e cable.

Figure 4: Resistivity Implied By Figure 1.

### Minimum Load Voltage

Figure 5 shows how we can compute the load voltage when the cable is carrying the maximum current over the maximum length cable.

Figure 5: Minimum Load Voltage Calculation.

This is the minimum voltage at which the load's power converter must operate.

### Cross-Check Calculations

Figure 6  shows some calculations that demonstrate that my results are internally consistent.

Figure 6: Cross-Check Calculations.

## Conclusion

I have always felt that the today's PoE power limits of 15.5 W (type 1) and 25.5 W (type 2) are not adequate for many applications involving MIMO wireless access points. This new standard will be a real boon to customers with wireless coverage issues.

Save

## Chromatic Dispersion with 10 Gigabit Optical Transports

Quote of the Day

The road of life is paved with flat squirrels who couldn’t make a decision.

— Source unknown. Indecision is fatal for squirrels. It is also the most destructive management impairment that I can think of.

## Introduction

Figure 1: Illustration of Chromatic Dispersion on a Glass Fiber. A single optical pulse is composed on a range of wavelengths. Because each color travels on the fiber with a slightly different speed, the wavelengths separate as the pulse travels down the fiber.

The Fiber-To-The-Home (FTTH) market is preparing to make the transition to a data rate of 10 Gigabits Per Second (Gbps) to and from the home. The current Gigabit PON (GPON) standard support 2.5 Gbps to the home and 1.25 Gbps from the home. One of the challenges we are facing with 10 Gbps is managing chromatic dispersion, which is an  important optical impairment (see Figure 1).

Transmitting information over a glass fiber requires that we use a laser to  modulate the power of the optical signal. To receive the optical signal with a minimum of errors, we need to provide the receiver with (a) an adequate amount of optical power and (2) most of the power for each bit must remain within its assigned bit time. Unfortunately, as the modulated signal travels on the fiber it encounters a number of impairments that reduce its power and limit its range. For PON systems, the most significant impairments are:

In this post, I will be showing how we model the effect of small amounts of dispersion as a power loss. We commonly refer to this power loss at the dispersion power penalty. I will also show how the need to limit the power penalty drives a critical laser parameter, the laser spectral width.

## Background

### My Quick Description of Chromatic Dispersion

For those who are looking for a quick description of dispersion, I will give you my "elevator pitch" on dispersion in fiber optic communications:

• Different wavelengths of light move at slightly different speeds along a fiber.
• Digital signals are sent on a fiber in the form of pulses of optical power.
• The pulses of optical power are generated by a laser, which produces produces optical power in a narrow range of wavelengths, usually on the order of 0.1 nm.
• As the pulses move down the fiber, the slower wavelengths of light eventually separate from the faster wavelengths of light.
• The separation of the wavelengths as they travel down the fiber causes the pulse to spread out. Eventually, the bits begin to merge together and become impossible to separate. Also, the difference in power between a logic 1 and logic 0 begins to reduce, making it impossible to accurately determine whether a time slot contains a 1 or 0.

### Definitions

Optical Impairment
Optical fiber is a very good transmission medium, but it is not perfect. It has a number of characteristics that limit its performance, which are referred to as impairments. The usually are broken down into linear and non-linear impairments. Our focus here is chromatic dispersion, which is a very important linear impairment. (Source)
Chromatic Dispersion
Dispersion is a physical phenomenon comprising the dependence of the phase or group velocity of a light wave in the medium on its propagation characteristics such as optical frequency (wavelength) or polarization mode. (Source: ITU G.989.2 standard)
Chromatic dispersion is the result of the different colors, or wavelengths, in a light beam arriving at their destination at slightly different times.  The result is a spreading in time of the on-off light pulses that convey digital information.  Chromatic dispersion is commonplace, as it is actually what causes rainbows – sunlight is dispersed by droplets of water in the air.  In fiber-based systems, an optical fiber, comprised of a core and cladding with differing refractive index materials, inevitably causes some wavelengths of light to travel slower or faster than others. Chromatic dispersion is usually modeled as a combination of material dispersion and waveguide dispersion. (Reference)
Laser Linewidth
A spectral line extends over a range of frequencies, not a single frequency (i.e., it has a nonzero linewidth). In addition, its center may be shifted from its nominal central wavelength. (Source)
Zero-Dispersion Wavelength
In a single-mode optical fiber, the zero-dispersion wavelength is the wavelength or wavelengths at which material dispersion and waveguide dispersion cancel one another. In all silica-based optical fibers, minimum material dispersion occurs naturally at a wavelength of approximately 1300 nm. (Source)

### Wavelengths Used

Figure 2 shows the wavelength plans for the various PON standards. The GPON (aka G-PON in Figure 2) is the most common PON type deployed in North America.

Figure 2: Good Summary of the Various PON Wavelength Plans. DS means downstream from the central office. US means upstream to the central office from the homes.  PtP means point-to-point and refers to wavelengths dedicated to specific customers. Video refers to the band reserved for NTSC RF video transmissions. (Source)

### Dispersion Power Penalty

There are a number of formulas that are used to model the loss of signal power caused by dispersion. For this post, I will be using Equation 1. I chose this formula because it is the most conservative of the commonly used models.

 Eq. 1 ${PP _D} = -5 \cdot \log \left( {1 - {{\left( {4 \cdot D \cdot L \cdot B \cdot {\sigma _\lambda }} \right)}^2}} \right)$

where

• PPD is the dispersion power penalty [dB].
• $\sigma _{\lambda}$ is the spectral standard deviation of the laser [nm].
• B is bandwidth of the signal [Hz].
• L is the length of the fiber [km].
• D is the dispersion constant of the fiber [ps/nm·km].

Equation 1 assumes that the engineer wishes to define the bit time as the period required to contain 95% of the pulse energy at the time of reception. There are lengths that will experience so much dispersion that you will not be able to contain 95% of the pulse energy within the assigned bit time. Mathematically, this means that the expression within Equation 1's logarithm will become zero or negative, which indicates no real solution. I should also make clear that Equation 1 does not model Intersymbol Interference (ISI) – it only models the reduction in bit power.

I like to look at the terms in Equation 1 as shown in Figure 2. I think of dispersion as being a function of two terms: (1) a fiber plant dependent term and (2) a laser transport dependent term.

Figure 3: Breakdown of the Dispersion Power Penalty Formula.

Figure 3 shows a formula with five variables. In the case of G.989.2, the 10 Gbps portion of the standard specifies four of these numbers indirectly as follows:

• B = 10 Gbps

This is the transport data rate and is a fundamental system parameter.

• D · L  = 24 ps/nm · 20 km = 480 ps/km

I refer the product of the dispersion constant and the distance as the "dispersion load". It tells you how much delay variance per nm that your system can tolerate. For more information on the dispersion constant, see Appendix A.

• PPD = 0.25 dB (my allotment to dispersion from the total 1 dB power penalty)

There a number of optical impairments that reduce the effective power of the transport.

Given these values from the G.989.2 standard, we can determine our laser's maximum allowed spectral width – the objective of this post.

## Analysis

### Laser Spectra Width Requirement Derivation

Figure 4 shows how I can determine the required laser spectral width assuming the values for B, L · D, and PPD from the ITU specification. The laser spectral width is normally specified in terms of its Side-Mode Suppression Ratio (SMSR) value. We relate the SMSR value, Δλ,  to the σλ using the formula Δλ = 6.07 · σλ. Another common measure of spectral width, Full-Width at Half Maximum (FWHM), has a different conversion factor.

Figure 4: Laser Spectral Width Determination.

A laser with  Δλ=0.1 nm is readily obtainable.

### Variation of Power Penalty with Distance

Figure 5 shows how the power penalty for a laser with a 0.1 nm spectral width varies with distance.

Figure 5: Power Penalty Versus Distance.

## Conclusion

I was able to determine the required laser spectral width to meet the requirements in the NGPNO2 specification. I then used this spectral width to determine how the dispersion power penalty varies with distance.

## Appendix A: Dispersion Constant for SMF-28e Fiber

Figure 6 shows how to determine the dispersion constant SMF-28e using Sellmeier's formula. The model and its constants (S0 and λ0) are given in the SMF-28e datasheet.

Figure 6: Dispersion Constant Versus Wavelength for SMF-28e.

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

Save

## Using SUMPRODUCT to Evaluate Two-Variable Polynomial

Quote of the Day

Rub my knee coach – I just got kicked in the groin.

— Said by one of my brothers to a trainer while writhing in pain on a football field in front of thousands of people. He did not want everyone in the stadium to know what really happened.

## Introduction

Figure 1: Battery Capacity Chart from Innovative Battery Technologies. (Source)

I occasionally am asked to write Excel-based tools for customer use that have implementation restrictions because of security issues. The most common restriction is that macros cannot be used. This restriction makes sense because macros are an easy way for a hacker penetrate a system, however, macros also make complex algorithms available within Excel. In this post, I will show you how to use the SUMPRODUCT function to implement a polynomial curve fit and avoid the use of a macro.

One of the most common computation tasks that my customers face is estimating battery capacity based on the battery's temperature and discharge current. Figure 1 shows a example of the capacity curves for a typical lead-acid battery. Ten years ago, I chose to implement this function with an Excel spreadsheet that used a polynomial approximation for this function. An engineer today asked me to explain how my Excel implementation works, and I felt this would be a good topic for a post. This approach is implemented using SUMPRODUCT – no helper cells were required.

## Background

### Generating the Polynomial Approximation

For those curious about how I came up with the polynomial approximation, see this zip file with my Mathcad source, my Excel workbook example, and a PDF of the Mathcad source. The polynomial coefficient were generated using Mathcad's regression function.

### The Polynomial

Figure 2 shows the cubic, two-variable polynomial that I implemented in Excel. The x variable is temperature (°C) and the y variable is load current expressed in C-rate.

Figure 2: Cubic Polynomial Approximation for Battery Capacity Versus Temperature (x) and Load Current (y).

### SUMPRODUCT Implementation

Figure 3 shows how I evaluated the polynomial of Figure 2 using SUMPRODUCT. It may look complex, but I put each term on its own line to make understanding it easier. The key to understanding the implementation is to realize that you can (a) capture a range of values within braces, and (2) you can use the brace quantities as exponents.

Figure 3: Description of SUMPRODUCT Implementation.

I also should explain why I divide the function by 97.164%. I normally work with batteries that are specified to operate for 20 hours with a 0.05 C-rate load. I needed to adjust the function to ensure that I would have 100% capacity at a 0.05 C-rate load.

### Generating a Table of Capacity Values

We can illustrate the use of this formula by generating a two-dimensional data table of battery capacity values. Figure 4 shows the data table that I generated using the Excel formula of Figure 3

Figure 4: Date Table Example.

Ideally, Figure 4 would have had the same values as Figure 5, which was generated using a cubic spline routine of the same data used for determining my regression equation (Figure 2). As you can see, my regression formula is not perfect but it is more than adequate for estimating battery capacity.

Figure 5: Capacity Table of Interpolated Raw Data.

Figure 6 shows another view of the difference between my regression equation and the capacity curve (Figure 1). My regression formula (colored surface)  roughly follows the data from Figure 1 (round dots).

Figure 6: 3D Plot of Regression Surface and Raw Data (points).

## Conclusion

I often use polynomial approximations to implement empirical functions. While not always very accurate, they are simple to use, quick to implement, and usually accurate enough. In the case of a lead-acid battery, the battery-to-battery variation in capacity is larger than the error in my polynomial approximation.

Save

Save

Posted in Batteries, Electronics | 2 Comments