Weighted RANDBETWEEN() ?

[avsrock90]

Hi All,

In cells A1:A11 I have a list of probabilities associated with numbers (1-11) that are listed in cells B1:B11. A1:A11 sum to 100%.

I am wondering if there is a way to return 1,2,3,4...,9,10,11 with the probabilities I specify rather than doing =randbetween(1,11) and having it be roughly 1/11th of the time for each.

Basically in another sheet I want to be able to to have a number 1-11 appear with the probability I define. I guess I'm not really looking for a random number, but I'm looking for a function that will return a number with probabilities I specify. Any ideas?

Thanks for any help!

 

[shg]

Welcome to the forum.

Here's one way:

       -A- --B--- --C--- --------------------D---------------------
   1   Num  Prob   Cumu                                            
   2     1 0.1035 0.0000 C2 and down: =SUM(C1,B1)                  
   3     2 0.0522 0.1035                                           
   4     3 0.0332 0.1557                                           
   5     4 0.0093 0.1889                                           
   6     5 0.1611 0.1982                                           
   7     6 0.0705 0.3594                                           
   8     7 0.0715 0.4298                                           
   9     8 0.0690 0.5013                                           
  10     9 0.1936 0.5703                                           
  11    10 0.1157 0.7639                                           
  12    11 0.1204 0.8796                                           
  13                                                               
  14                   5 C14: =INDEX(A2:A12, MATCH(RAND(), C2:C12))

The values in col B sum to 1.

 

[avsrock90]

Makes perfect sense. Thank you!

 

[shg]

Since your numbers are the same as the indices, you can skip the INDEX part and just use:

=MATCH(RAND(), C2:C12)

 

[mtimik]

How would shg's solution above be modified to use randbetween() so negative integers can be returned using weighted probability?

For example, how would you return a random number between -3 and +4 with the following probabilities:
-A- -B-
Num Probability
-3 1%
-2 6%
-1 10%
0 3%
1 5%
2 36%
3 27%
4 9%

 

[shg]

Use the formulas in post#2 and replace the numbers in col A with your numbers.

 

[mtimik]

Thanks, worked perfectly. Just thought the Rand() function would not return negative values but I see how this formula works now.

 

[kissthechef]

How do you do this for only two numbers i.e. Binary. If I need it to generate randomly between say 1 & 0 with the probability of 0 being 20%. And I need to do a 1000 runs so I get a fair sized sample

 

[joeu2004]

How do you do this for only two numbers i.e. Binary. If I need it to generate randomly between say 1 & 0 with the probability of 0 being 20%. And I need to do a 1000 runs so I get a fair sized sample

Ostensibly, if you want 1000 random values in A2:A1001, enter the following into A2 and copy A2 into A3:A1001:

=(RAND()>=20%)*1

But if you want to ensure that exactly 20% are zeros, enter the following into A2 and copy down (adapted from a suggestion by shg):

=(RAND() >= ($B$3-COUNTIF($A$1:A1,0)) / ($B$2-ROWS($A$1:A1)+1))*1

where:
A1: anything, but not zero
B1: 20%
B2: 1000
B3: =ROUND(B2*B1,0)

 

[kissthechef]

Ostensibly, if you want 1000 random values in A2:A1001, enter the following into A2 and copy A2 into A3:A1001:

=(RAND()>=20%)*1

But if you want to ensure that exactly 20% are zeros, enter the following into A2 and copy down (adapted from a suggestion by shg):

=(RAND() >= ($B$3-COUNTIF($A$1:A1,0)) / ($B$2-ROWS($A$1:A1)+1))*1

where:
A1: anything, but not zero
B1: 20%
B2: 1000
B3: =ROUND(B2*B1,0)

Thanks a ton that worked. I am trying to mimic correct incorrect answers taken by a group of students