Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Biased Number Generation?
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 -- Where are we going and why am I in this HAND BASKET?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Biased Number Generation?
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. -- Gary''s Student - gsnu200901 "plh" wrote: 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 -- Where are we going and why am I in this HAND BASKET?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Biased Number Generation?
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "plh" wrote in message ... 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 -- Where are we going and why am I in this HAND BASKET?? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Biased Number Generation?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Biased Number Generation?
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 In article , ?B?R2FyeScncyBTdHVkZW50?= says... 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. -- Gary''s Student - gsnu200901 "plh" wrote: 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 -- Where are we going and why am I in this HAND BASKET?? -- Where are we going and why am I in this HAND BASKET?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random Number Generation | Excel Programming | |||
Covariance in Excel in biased | Excel Programming | |||
Random Number Generation | Excel Programming | |||
I need help with random number generation | Excel Worksheet Functions | |||
random number generation | Excel Programming |