Reliability HotWire

Issue 102, August 2009

Hot Topics

Using Spreadsheets in RENO

ReliaSoft's RENO software tool is a platform for building and running analyses for probabilistic or deterministic scenarios using an intuitive flowchart modeling approach and simulation. RENO can be thought of as a "visual spreadsheet" — a way to work with data and equations graphically, clearly showing the connections. However, RENO also goes a step further and provides the ability to manipulate internal Excel®-compatible spreadsheets from the flowcharts. In this article we will present an example using these spreadsheets.

Example
A company manufactures hinges that are made up of four components, where components A, B and C must fit inside component D when the unit is assembled. The next figure shows a schematic of the hinge's assembly.

Hinge Assembly

The manufacturer wants to determine the percentage of hinges that would fall out of specifications. Specifically, the manufacturer wants to estimate the probability that the size of (A+B+C) will be greater than the size of the space within D. For that purpose, the manufacturer collects data about the dimensions of each of the manufactured components and determines the part dimensions as follows:

  • A – Normal distribution with mean = 2 and std = .02
  • B - Normal distribution with mean = 2 and std = .02
  • C - Normal distribution with mean = 30 and std = .2
  • D - Normal distribution with mean = 34.5 and std = .5

We could approach this problem in RENO in many ways. The main idea is to generate four random variables for A, B, C and D and determine whether the sum of A+B+C exceeds the dimensions of D. We would repeat this simulation a number of times and determine the number of times we would have a hinge that cannot be assembled. To do this, we could use RENO’s flowchart capabilities exclusively. An example of this can be found here [1]. We could also set up this problem in a spreadsheet and use the flowcharting capabilities of RENO to update the spreadsheet for each simulation. This spreadsheet-based approach may be more familiar for some users and it may make it easier to expand the model, for example if you need to add a large number of additional parts to the analysis

The first step would be to set up the spreadsheet for this model as shown next:

RENO Spreadsheet

The cells with a gray background are user input values such as the mean and standard deviation of the hinge components. The cells with a yellow background are cells that contain equations. Cell D9 contains the following equation:

=IF((D4+D5+D6)>D7,1,0)

The value in cell D9 will be 1 when the hinge is out of spec (i.e. when the sum of the cells D4, D5 and D6 is greater than the value in cell D7). It will be zero otherwise.

Cell D13 contains the following equation:

=IF(B1>0,D12/B1,0)

The value in cell D13 is the ratio obtained by dividing the out of spec count found in cell D12 by the number of simulations in cell B1. If the number of simulations is zero, then the value of this cell is 0. Note that this cell has been formatted as a percentage.

We are now ready to build a flowchart that will:

  1. Write the number of simulations run in cell B1.
  2. Write four random variables for A, B, C and D in cells D4, D5, D6 and D7 respectively.
  3. Update the out of spec count in cell D12.
  4. Repeat N number of simulations.

The flowchart is shown next.

RENO Flowchart

Step 1: Use a block to write the number of simulations that have been run in cell B1, as shown next.

Block 1: Initialize

The function SET_NUMBER(Spreadsheet Name, Sheet Index, Column, Row, New Value) used above is a reserved keyword in RENO that sets the spreadsheet cell at the specified Column and Row to the specified New Value. In this case it is setting cell B1 in Spreadsheet1 to SIMS_TOTAL. SIMS_TOTAL is another reserved keyword in RENO that evaluates to the total number of simulations defined during a run.

RENO's reserved keywords, definitions and predefined functions can be accessed though the Function Wizard. The Function Wizard also can be used to build equations for definitions and standard blocks. We can use the Function Wizard to build the equation for "Block 1: Initialize" as shown next.

RENO Function Wizard

Step 2: Use a block to draw four random variables using the parameters entered by the user in Spreadsheet1 and then to write the variables to cells D4, D5, D6 and D7 respectively.

Block 2: Write A, B, C and D

The function SET_NUMBER is used again here to write the values NormalRV(1), NormalRV(2), NormalRV(3) and NormalRV(4) to cells D4, D5, D6 and D7 respectively. The function NormalRV is a Definition created by the user that takes one variable (the row where the parameters can be found), and returns a normal random variable using those parameters.

Function: NormalRV

NormalRV uses the intrinsic function NORMINV(probability, mean, standard_dev), which returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

Remember that in order to draw a random number that follows a normal distribution, we need the parameters of the distribution and a uniform random number that will be used as the probability. The parameters of the distribution are obtained from the user inputs in the second and third columns of Spreadsheet1 in the row passed to the function NormalRV. The probability is generated by the user-created Random Variable Definition shown next.

URN Random Variable

Note that RENO will initialize (draw a value for) URN once at the beginning of each simulation. From there on, it will be a constant. Therefore, in the NormalRV function, we use the intrinsic function RESET_RV(Random Variable), which resets the value of the specified Random Variable (URN) by drawing a new value. In this manner, we ensure that a new random variable is returned each time NormalRV is called.

Step 3: Use a Block that will add the value in cell D9 to the cell in D12 after each simulation.

Block 3: Add Count

Step 4: Specify the number of simulations in the Simulation Console, as shown next.

RENO's Simulation Console

When the simulation is complete, the results are displayed in Spreadsheet1, as shown next. Note that the values of A, B, C and D are the random values used in the last simulation (the 1,000th simulation).

RENO Spreadsheet with Results

Based on 1,000 simulations, A+B+C will exceed D 17.7% of the time.

References
[1] "Example R-3 - Hinge Assembly Variability," Internet: http://www.ReliaSoft.com/reno/examples/renoexr3/index.htm, [Aug. 6, 2009].
[2] Wittwer, J.W., "Stochastic Model Example: Tolerance Stack-Up," Internet: http://vertex42.com/ExcelArticles/mc/StochasticModel.html, June 1, 2004 [Aug. 6, 2009].

Copyright © 2009 ReliaSoft Corporation, ALL RIGHTS RESERVED