Random generator using a range with range frequency

[Okstate_Fan131313]

I'm trying to find a way to randomly choose a number within a given range but
have a given frequency for each random number.

For example.

Range: 1-5

Frequency of appearance:

1=5%
2=10%
3=30%
4=50%
5=5%

I was trying to find a way using the random function with upper & lower
bounds but wasn't sure how in incorporate the "frequency of appearance"
factor.

Any and all help is greatly appreciated,

Thanks.

 

[Tom Hutchins]

Here are two ways:

On a new sheet, enter 1 in A1. Enter 2 in A2-A3. Enter 3 in A4-A9. Enter 4
in A10-A19. Enter 5 in A20. In B1, enter =Rand() and copy it down through
B20. Now recalc and sort all the data based on column B. This gives you the
numbers 1 through 5 in random order with the exact frequency desired.

Or...

=CHOOSE(INT(RAND()*20)+1,1,2,2,3,3,3,3,3,3,4,4,4,4,4,4,4,4,4,4,5)

This formula generates a random integer from 1 to 20, and based on that
integer, returns 1/2/3/4/5. Over a large sample, this should come close to
your desired frequency.

Hope this helps,

Hutch

 

[Lars-Åke Aspelin]

I'm trying to find a way to randomly choose a number within a given range but
have a given frequency for each random number.

For example.

Range: 1-5

Frequency of appearance:

1=5%
2=10%
3=30%
4=50%
5=5%

I was trying to find a way using the random function with upper & lower
bounds but wasn't sure how in incorporate the "frequency of appearance"
factor.

Any and all help is greatly appreciated,

Thanks.

Try the following formula to get a random number from 1 to 5 with the
frequencies stated above:

=MAX((RAND()>{0,0.05,0.15,0.45,0.95})*{1,2,3,4,5})

Note that the numbers in the first array are the cumulative
frequencies.

Hope this helps / Lars-Åke

 

[Lars-Åke Aspelin]

Try the following formula to get a random number from 1 to 5 with the
frequencies stated above:

=MAX((RAND()>{0,0.05,0.15,0.45,0.95})*{1,2,3,4,5})

Note that the numbers in the first array are the cumulative
frequencies.

Hope this helps / Lars-Åke

And for the unlikely event that RAND() should return exactly 0, the
formula should rather be like

=MAX((RAND()>={0,0.05,0.15,0.45,0.95})*{1,2,3,4,5})

Lars-Åke