Biased Number Generation?

[plh]

Hi Gurus,
What I want to do is similar to the use of the Rnd() function but I want to bias
the results. In my case I am obtaining numbers from 1 to 4 using
Int((uB - lB + 1) * Rnd + lB)
Where uB = 4 and lB = 1, but I want to skew the outcome so that 1 is more
probable that 2 which is more probable than 3 etc., with the probability values
yet to be determined.
Thank You,
-plh

 

[Gary''s Student]

First generate a value between 1 and 10. Then map it to an output as follows:

1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 3
9 3
10 4

Clearly you will make more 1s than 2s.
more 2s than 3s
more 3s than 4s

You can modify the table size and contents to simulate any arbitrary
distrbution.

 

[Bernard Liengme]

In A11 I entered the numbers 10, 5, 2, 1 representing the weighting for
integers 1,2,3,4.
Thus I want 1 to be 10 times more likely than 4, etc
In A3 I entered =RANDBETWEEN(1,SUM($A$!:$D$1)
In B3 I used the formula
=IF(A4<=$A$1,1,IF(A4<=SUM($A$1:$B$1),2,IF(A4<=SUM($A$1:$C$1),3,4)))
I copied these two down to row 5003 to get 5000 random numbers
Note that RANDBETWEEN need the Analysis Toolpac in pre-2007 versions of
Excel


I used the Frequency function to find I had this distribution
1 2827 10.66792453
2 1362 5.139622642
3 546 2.060377358
4 265 1

So I have 2827 ones and one is 10.7 more frequent than 4
Hit F9 and I get
1 2855 10.93869732
2 1325 5.076628352
3 559 2.141762452
4 261 1

and so on
Any help?
best wishes

 

[Bernd P]

Hello,

I suggest to take my UDF redw or RandHistoGrm:
http://sulprobil.com/html/distributions.html

It should be exactly what you are looking for...

Regards,
Bernd

 

[plh]

Thank You gsnu200901, Bernard Li and Bernd P for your replies.
I used something very much like gsnu200901's method, but with 1-100, which lends
itself to dividing the % values a little finer.
-plh