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. This techique is 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 of the components. Once this behavior is well understood, the designer can effectively select circuits and components to work with the expected manufacturing tolerances.
Circuits that work 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 tool you use to make these calculations varies depending on the problem complexity and your need for intuition. I've summarized some of the alternatives in Table 1 below.
Table 1. Methods of Yield Analysis
Yield Analysis Tool
Technique
Best used for
SPICE
Multiple simulations
Brute force proof circuit will work, awkward for gaining intuition
SPICE
SENS sensitivity analysis
Brute force method, good for getting intuition about what components matter
Exact closed-form analysis
Using an equation for the circuit performance calculate sensitivities. Using equations for the component variations and the sensitivities calculate the probability of meeting spec
Very simple problems only, gives good insight into technique
Microsoft Excel, MathCAD
Create cdfs with manufacturing data, model circuit with
Intermediate complexity, can incorporate real world data, gives good intuition into the problem
This article will describe how to do yield analysis using Microsoft Excel.
We discuss some basic yield analysis concepts like the probability distribution function. We will learn how to generate random component values that fall in a desired probability distribution.
Component Values and Design Equations Determine the Yield
A circuit is made up of components. The circuit is assembled from these components and the ensemble behavior follows certain rules or design equations. To carry out a yield analysis we need knowledge of both component variation and design equations.
For example, let's look at the simple gain circuit of Figure 1 below. The gain of this circuit is readily calculated knowing the resistance of both Rf and Rg (we assume an ideal op amp). In the real world, if we built this circuit several hundred times we find that components Rf and Rg take on different values for each build. When we test the circuit the gain is found to also take on different values for each build.
In this circuit the component variation is our resistor tolerances. The design equation for gain is Gain = - Rf / Rg. For example, if we selected Rf = 1 kΩand Rg = 1 kΩ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.1 V/V.
Component PDFs and CDFs
In general, we cannot predict the value a component will take on ahead of time. However, with experience we can predict how a large number of components behave or how a large number of product runs 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 vs. the probability of a particular value occurring. For example, in our simple circuit we might plot the value of the resistance for Rf. vs. 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 if we have the pdf we can calculate the cdf by integrating.
You have probably 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 below.
Figure 2. Examples of a PDF and a CDF.
The Normal distribution fits a lot of 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 you see looks something like Figure 3.
Figure 3. Distributions of sorted parts.
With this distribution, the probability a measured resistor value agrees with the resistor's label is zero! Your circuit will behave considerably worse than one with Normally distributed component values. And the guy who got the 5% resistors will find his works better than expected because his distribution has the tails truncated.
The lesson is that there are many distributions that are useful and the choice is important--so don't limit yourself to the Normal distribution.
Generating Random Component Values in Excel
If we could generate a list of random numbers we could us 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. I've done that in the figures below. For 20 builds, and a gain spec of ±20% the yield was 80% (this will vary slightly from run-to-run).
Figure 4. Generating random resistor values.
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-either select Tools | Data Analysis | Random Number Generation or use the RAND() (also take a look at RANDBETWEEN()). If you use the RAND() function technique the values are regenerated each time the spreadsheet calculates (push F9).
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.
Excel provides a number of other functions that help us generate more realistic pdfs. These will be described in the next section followed by techniques to generate any arbitrary pdf from manufacturing data.
The Normal Distribution PDF and CDF
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 I used to generate the pdf in Figure 2. By changing the "FALSE" to "TRUE" you will get the values for the cdf.
If you don't have better information, assume that the component's stated percent tolerance is ±3 standard deviations. For example, that means 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 don't 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
Excel does not have a RANDNORM() function but the add-in functions provide the necessary capability. To generate ten values of resistors that are nominally 1 kΩand have a ±20% spread in production the steps are as follows:
our mean = the nominal value of 1 kΩ, our standard deviation is ±20% of 1 kΩ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 this.
Figure 5. Random number generation dialog box.
Note that I have filled in the mean and standard deviation I calculated in step 1. I have entered 10 for the number of random numbers (values) to be generated. I also entered the cells on the spreadsheet where I want Excel to put my values. The resulting output looks like this:
Figure 6. Excel generated random component values.
Other Useful Built-In Distributions
You may have noticed that Excel gave you a list of many distributions to choose from on the Tools | Data Analysis | Random Number dialog. These 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 one for your situation. What do you do if your distribution doesn't match one of the available distributions? Make your own random number generator! We will 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. As we saw in the sorted resistor case (see Figure 3) the effect of a non-standard 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 and then generate random numbers that follow that distribution for the yield analysis.
Creating such a distribution and random numbers that follow that distribution comprises several steps (follow along on Figure 7).
Measure a large quantity of the real parts or generate data using calculations, etc. You may 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 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 have seen 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.
下载，PDF格式
