Biased Number Generation?

  • Thread starter Thread starter plh
  • Start date Start date
P

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
 
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.
 
In A1:D1 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
 
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
 
Back
Top