How to analyze tolerances using Excel - ASQ



How to Analyze Tolerances Using Excel

The examples provided are tolerance analysis for 2, 4 and 5 random variables. Each example can be taken as a template to be modified for the user's own application. The examples in TolAnal2.xls are as follows:

1. X = A+B

2. X = Sqrt [ Sqr(A) + Sqr(B)]

3. X =ArcTan(B/A)

4. X = ( A - B ) / A

5. V = Pi * L * R*R

The example in TolAnal4.xls is

6. IL (amps) = (VZ - VBE ) * ( ( - 1)

R2 (

and

TolAnal5.xls example is an extension of the TolAnal4.xls

7. PL(watts) = IL2 * RL

EXCEL spreadsheet “Circuit A,B,C,D,E” is used in example 7 and it is described in detail as follows:

Data Enter:

EXCEL EXCEL

Variable Aim / Mean Col &Row Sigma Col & Row

Vz 4.7 A 257 0.05 C 257

VBE 0.64 B 257 0.01 D 257

R2 380 G 257 17 I 257

Beta 35 H 257 5 J 257

RL 1000 K 257 40 L 257

Working columns:

A = Vz B= VBE C = R2 D = Beta E = RL

Power Amp equation in column G for estimating treatment combination

=(( ((A2-B2)/C2)* ((D2-1)/D2) )*(((A2-B2)/C2)*((D2-1)/D2))*E2)

The entries of the Mean and the modified standard deviation or sigma are as follows:

3 1 = 3 Column A: Vz

Row 2: Mean minus Sqrt 3 times sigma

3: Mean

4: Mean plus Sqrt 3 times sigma

Repeat the cycle until row 244

3 2 = 9 Column B: VBE

Row 2: Mean minus Sqrt 3 times sigma.

3: “ “ “ “ “

4: “ “ “ “ “

5: Mean

6: “

7: “

8: Mean plus Sqrt 3 times sigma

9: “ “ “ “ “

10: “ “ “ “ “

Repeat the cycle until row 244

3 3 = 27 Column G: R2

Row 2: Mean minus Sqrt 3 times sigma

3:

…

28: “ “ “ “

3 4 = 81 Column H: Beta

Row 2: Mean minus Sqrt 3 times sigma

3:

…

82: “ “ “ “

3 5 = 243 Column K: RL

Row 2: Mean minus Sqrt 3 times sigma

3:

…

243: “ “ “ “

Appropriate weights of 1/6 or 4/6 are assigned in columns H – L

The Response variable taken together with the corresponding weights for each moment are in columns N - Q

Sensitive analysis is easily performed through a copy of the initial spreadsheet. To estimate the contribution of error for a variable of interest we proceed as follows:

1. The associated sensitivity spreadsheets are used to estimate the percent variation, when a random variable is held constant. A value of 0.0 is entered for sigma in the copied spreadsheet, which will automatically estimate the 2nd moment for the response variable. See Tab “Circuit A, B, C, D, E Sens A” for the Zener diode variable.

2. Create 4 additional copies of the initial spread sheet and name the tabs for the variable whose sensitivity we estimate, i.e. B or C… E.

3. A summary worksheet is provided that graphically portrays the amount of contribution of error from each random variable, as computed in the sensitivity worksheets.

4. A tolerance is computed for the response variable that was estimated from the given functional equation. Typically, tolerance is taken as plus or minus 3 times the standard deviation of the response variable.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download