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.
Introduction
Designing circuits that work in the real world is challenging. It is not enough just to design the circuit to meet a specification target. It is also important to accurately predict the behavior of the circuits under a range of expected conditions, including the realistic variations among the components. This process is commonly called circuit yield analysis. Once this behavior is well understood, the designer can effectively select circuits and components to work within the expected manufacturing tolerances.
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
With these three inputs you can make the necessary calculations to predict circuit yield. The effectiveness of the tool that you use to make these calculations varies, depending on the problem complexity and your insights. Some of the alternative considerations are summarized in Table 1 below.
| 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.
Component Values and Design Equations Determine the Yield
A circuit is comprised of components. The circuit is assembled from these components and the ensemble's behavior follows certain rules or design equations. To conduct a yield analysis, the designer must know component variation and design equations.
For example, consider the simple gain circuit of Figure 1 below. The gain of this op amp is readily calculated knowing the resistance of both Rf and Rg. (This analysis assumes an ideal op amp.) However, in the real world if we build this circuit several hundred times, components Rf and Rg would take on different values for each build. Then when we test the circuit, the gain will have different values for each build. In this example circuit the component variation is resistor tolerances. The design equation for gain is:
Gain = - Rf/Rg
For example, if we select Rf = 1kΩ and Rg = 1kΩ, the gain would be exactly -1.
Figure 1. Inverting op amp example.
Finally, the specification for the circuit might be something like: gain = -1 ± 0.1V/V.
Component PDFs and CDFs
In general, a designer cannot predict the value that a component will assume. However, with experience we can predict how many components or how a large number of product runs will behave. The description of this behavior is something called a pdf, or probability distribution function.
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 Rf. versus the probability of seeing that resistance in a batch of resistors.
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 Figure 2.
Figure 2. Examples of a PDF and a CDF.
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 Figure 3.
Figure 3. Distributions of sorted parts.
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.
Generating Random Component Values in Excel
If we could generate a list of random numbers, we could use this to represent resistor values in production. Then, we could use these values together with the circuit equations to determine the gain of the circuit, compare it to the spec, and calculate yield. That exercise is exactly what the next figures show. For 20 builds and a gain spec of ±20%, the yield is 80%. (This will vary slightly from run-to-run.)
Figure 4. Generating random resistor values and the resulting gain.
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.
The Normal Distribution PDF and CDF
Excel provides a number of other functions that help us generate more realistic pdfs. To generate a Normal or Gaussian curve use the built-in function, NORMDIST(). For example "=NORMDIST($A7,0,1,FALSE)" returns the probability that x = the value stored in cell A7 for a Normal distribution having mean = 0 and standard deviation = 1.
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.
Generating Random Values for the Normal Distribution
As noted above, Excel does not have a RANDNORM() function. Nonetheless, the Add-Ins functions provide the necessary capability. To generate ten values of resistors that are nominally 1kΩ and have a ±20% spread in production, follow these steps:
- 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.
Figure 5. Random Number Generation dialog box
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 Figure 6.
Figure 6. Excel-generated random component values.
Other Useful Built-In Distributions
You may have noticed that Excel gives you a list of many distributions to choose among on the Tools Data Analysis Random Number dialog. These distributions include Normal, Uniform, Binomial, Bernoulli, Discrete, and several others. Uniform, described earlier, is a simple, useful way to look at worst-case performance. Binominal produces a distribution with only two values (e.g., 1 and 0) as might be seen in a logic circuit. A good statistics book and some experimenting will help you select the right function for your situation.
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.
Generating a Random Number that Matches Production Data
Sometimes none of the built-in or standard pdf functions correspond to your circuit's situation. Moreover, as with the sorted resistor case (see Figure 3), the effect of a nonstandard distribution can be quite dramatic.
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 Figure 7).
- 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.
Figure 7. Generating a random value that matches production data.
Conclusion
In this article we learned how to generate random component values to be used in yield analyses. For many common distributions, Excel offers built-in functions that make the process quick and easy. For special cases, a simple technique was demonstrated that makes use of a uniformly distributed random number together with the measured cdf to generate arbitrarily distributed random numbers.
1 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.
