Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random generator using a range with range frequency
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random generator using a range with range frequency
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 "Okstate_Fan131313" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random generator using a range with range frequency
On Mon, 4 Aug 2008 12:30:14 -0700, Okstate_Fan131313
wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random generator using a range with range frequency
On Mon, 04 Aug 2008 22:28:59 GMT, Lars-Åke Aspelin
wrote: On Mon, 4 Aug 2008 12:30:14 -0700, Okstate_Fan131313 wrote: 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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic range generator | Excel Programming | |||
Random Generator | Excel Discussion (Misc queries) | |||
Random Name Generator | Excel Worksheet Functions | |||
Random Number Generator | Excel Worksheet Functions | |||
Random # Generator | Excel Worksheet Functions |