TUTORIALS 2878

Abstract: This article describes a simple technique for circuit designers to perform a comprehensive statistical analysis on virtually any circuit using just an Excel® spreadsheet. The article shows how to generate random component values to be used in yield analyses. A probability distribution function (pdf) and cumulative distribution function (cdf) are described. For special cases, a simple technique uses a uniformly distributed random number together with the measured cdf to generate arbitrarily distributed random numbers. These techniques are valuable to gain an insight into the operation of any circuit under real-world conditions and to ensure that it will have high manufacturing yields.

It is also well known that circuits that work well, even as circuit parameters vary, are less expensive to build, test, and support.

This article describes the use of tolerances to generate yield analyses—predictions of how many circuits built with varying component values will meet spec. To do a useful yield analysis, you must have:

- A good model of the circuit including important components, strays, etc.
- A good model of expected component variations
- A definition or specification of pass/fail

Yield Analysis | ||

Tool | Technique | Best Use |

SPICE | Multiple simulations | Brute-force proof circuit will work; awkward for gaining insight into a topic |

SPICE | SENS sensitivity analysis | Brute-force method; good for getting insight about what components matter |

Exact closed-form analysis | Equation for the circuit performance are used to calculate sensitivities; equations for component variations and their sensitivities are used to calculate the probability of meeting spec | Very simple problems only; gives good insight into the technique |

Microsoft® Excel, MathCAD | Create cdfs with manufacturing data; to model a circuit | Intermediate complexity; can incorporate real-world data; gives good insight into the problem |

This article will describe how to do yield analysis using Microsoft Excel. It discusses some basic yield-analysis concepts like the probability distribution function (pdf). It explains how to generate random component values that fall within a desired probability distribution.

For example, consider the simple gain circuit of

Gain = - RFor example, if we select R_{f}/R_{g}

Finally, the specification for the circuit might be something like: gain = -1 ± 0.1V/V.

The pdf is a curve or function plotting the possible values, x, of a random variable, X, versus the probability of a particular value occurring. For example, in the simple circuit here we might plot the value of the resistance for R

The cdf is the cumulative distribution function. This is the probability that a random variable, X, will take on a value less than or equal to some value, x. That means that if we have the pdf distribution, we can calculate the cdf by integrating. You have probably already worked with the Gaussian or Normal pdf which is defined with two parameters: the mean (central value) and the standard deviation (~width of peak). The Normal distribution's pdf and cdf are illustrated in

The Normal distribution fits many real-world situations and is easy to work with mathematically. However, beware! The Normal distribution might not describe your particular situation. For example, if you are using resistors with a 20% tolerance, you might find that the 5% resistors have all been removed and sold to someone else. The actual pdf for this looks something like

With this distribution, the probability that a measured resistor value agrees with the resistor's label is zero! Your circuit will behave considerably worse than one with Normal distribution component values. And the designer who got the 5% resistors will find his circuit works better than expected because his distribution has the tails truncated.

The lesson is that many distributions are useful and the choice of distribution model is important. Do not limit yourself to the Normal distribution function.

The analysis above used a Uniform distribution random variable for each of the resistors. The Uniform distribution has equal probability for all resistor values between two limits. You can generate the resistor values a couple of ways in Excel*¹: either select Tools Data Analysis Random Number Generation, or use the RAND() (also take a look at the RANDBETWEEN() function in Excel). If you use the RAND() or RANDBETWEEN() function technique, the values are regenerated each time that the spreadsheet calculates (push F9).

RAND() gives a random number that is greater than or equal to 0 and less than 1. To get a random number greater than or equal to 1 but less than b, use RAND() × (b-a) + a. RANDBETWEEN(a,b) gives an integer that is greater than or equal to a and less than or equal to b, if a and b are both integers. If a and b are not integers, RANDBETWEEN(a,b) gives an integer that is between a and b.

Unfortunately, most components follow distributions other than the Uniform distribution. However, this type of analysis is quick and useful as an estimate of worst-case performance.

Standard deviation (often called sigma) describes the width of the peak in the pdf function and corresponds to the point where the second derivative changes sign. This is what was used to generate the pdf in Figure 2. By changing the "FALSE" to "TRUE" you will get the values for the cdf.

If you do not have better information, assume that the component's stated percent tolerance is ±3 standard deviations. For example, a ±10% part would have a standard deviation of ±10/3 = ±3.33% of the nominal value.

Although the cdf and pdf correctly describe a Normal random variable, they do not generate random component values. Ideally, we would like a function like "RANDNORM()" that returns random numbers that fit the Normal distribution.

- The mean = the nominal value of 1kΩ; the standard deviation is ±20% of 1kΩ divided by 3 = ±200/3 = ±66.67Ω.
- Use the built-in functions to generate a list. Use the Tools Data Analysis Random Number Generation function. The dialog will look like
**Figure 5**.

Note that the mean and standard deviation values were calculated in step 1. A 10 is entered for the number of random numbers (values) to be generated; the Output Range shows the cells on the spreadsheet where we want Excel to put the calculated values. The resulting output looks like

What do you do if your distribution does not match one of the available distributions? Make your own random number generator! We cover that in the next section.

In this situation we would like to draw a distribution; create a distribution with production test data; or create a distribution with calculations. Then we generate random numbers that follow that distribution for the yield analysis.

Creating such a distribution and the resulting random numbers requires several steps (follow along on

- Measure a large quantity of the real parts or generate data using calculations. You might be able to get this data from an incoming inspection process. This raw data will be used to create the pdf.
- Histogram the data and normalize by the total number of samples. This is just another way of saying that the sum of all the probabilities is 1. This normalized histogram is the pdf that we wish to follow with our random numbers.
- Integrate this pdf to create a cdf. Verify that the maximum value of 1 is reached monotonically.
- Generate a uniform random number, y, between 0 and 1: y~UY(0,1).
- Use the uniform random number as an index into the cdf at y = P(X ≤ x) on the cdf and read off the value of x.
- Repeat steps 4 and 5 to generate as many random values of x as you need.

¹If you are using the 2003 or later version of Excel, make sure that you have activated the Add-Ins and Analysis ToolPak. Then you can use this path: Data Analysis Group Data Analysis Random Number Generator.