Random numbers with bias

[DavidObeid]

Is there a simple (non-vba) way to generate random numbers within
given range of integers where some numbers have a different bias tha
others?

EG: I want to generate random numbers from 3 to 10 where the proportio
of expected 3's will be say, 5%, 4's will be 10%, 5's will be 13%, 6'
will be 15%, 7's will be 18%, 8's wil be 20%, 9's wil be 11% and 10'
will be 8%.

Thanks in advance,

Dav

 

[beeawwb]

Not exactly simple, but...

In A1, I have a random number generating a percent value, that being,
to 100.

In B1, I have a cell with the following formula. It takes the percen
value, and converts it to a corresponding number. Probably more comple
then what you want to do though.

=IF(A1<=5,3,IF(AND(A1>=6,A1<=14),4,IF(AND(A1>=15,A1<=27),5,IF(AND(A1>=28,A1<=42),6,IF(AND(A1>=43,A1<=60),7,IF(AND(A1>=61,A1<=80),8,IF(AND(A1>=81,A1<=91),9,10)))))))

In any case,

Hope it helps,

Bo

 

[Bernie Deitrick]

Dave,

If you need to have a list of numbers, then you can simply enter five 3s,
ten 4s, etc. in cells A1:A100, and enter =RAND() in B1:B100. Then sort
A1:B100 based on column B, and your biased numbers will be randomized in
column A.

HTH,
Bernie
MS Excel MVP

 

[Evan]

I would map [0,1) output from Rnd() thru a cdf (cumulative
distribution function) with a udf (user defined funtion).
It would be called like any other Excel funtion. The udf
is essentially VBA and may not be the most reliable. More
to come tonight unless I fall asleep first.

 

[Guest]

Here is a VBA routine

Option Base
Function CDFGen(
y = Array(3, 4, 5
x = Array(0.2, 0.3, 0.5
RndVal = Rnd(
xaccum =
n = UBound(x
For i = 1 To
If RndVal > xaccum Then CDFGen = y(i
xaccum = xaccum + x(i
Next
End Functio

Install it with Tools > Macros > Macro Name = whatever > Macros In = This Workbook > Create. Copy/Paste the above over whatever. Then File > Exit the VBA window. Use it like any Excel function, i.e., =CDFGen(). I tested it with 1000 entries and COUNTIF(A:A,=3) summations. An interesting twist is repeatability. It doesn't seem to have it, i.e., if you delete some entries and replace them with the same thing, then the results are not the same. Not good.