Monte Carlo methods to evaluate tolerance stack. I need a random number generator formula.

Hello,

I am creating a Monte Carlo analysis of tolerance stack for the first time. I am using the following formula to randomly generate numbers between my lower spec and higher spec tolerances for a manufactured part.

=RANDBETWEEN($A$1,$B$1)

Where $A$1 is my lower spec and $B$1 is my higher spec.

I run this 1000 times, 1000 cells down, to simulate a 1000 part manufacturing run given the manufactures tolerances. It currently gives me an even distribution from my lower spec to my higher spec randomly. I want a normal distribution, something close to a 3 sigma disruption as this is the convention in manufacturing, With most of the randomly generated numbers in the middle of the lower and upper spec tolerance. Is there a way I can randomly generate a range of numbers that have a normal distribution?

If so, I would then use that range of randomly generated numbers with a normal distribution to plug into a geometric equation that I have derived to determine if the part will function or not.

Thank you, Dan.

Originally Posted by Myproject

I am creating a Monte Carlo analysis of tolerance stack for the first time. I am using the following formula to randomly generate numbers between my lower spec and higher spec tolerances for a manufactured part.
=RANDBETWEEN($A$1,$B$1)
Where $A$1 is my lower spec and $B$1 is my higher spec.

I run this 1000 times, 1000 cells down, to simulate a 1000 part manufacturing run given the manufactures tolerances. It currently gives me an even distribution from my lower spec to my higher spec randomly. I want a normal distribution, something close to a 3 sigma disruption

Try:

=NORMDIST(RAND(),$X$1,$Y$1)
or
=ROUND(NORMDIST(RAND(),$X$1,$Y$1),0)

X1: =AVERAGE(A1,B1)
Y1: =(B1-X1)/3

Use the latter formula if you want only integer results.