Weighted RANDBETWEEN() ?

avsrock90

New Member
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!

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

shg

MrExcel MVP
Welcome to the forum.

Here's one way:

Code:
``````       -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

New Member
Makes perfect sense. Thank you!

shg

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

=MATCH(RAND(), C2:C12)

mtimik

New Member

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

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

mtimik

New Member

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

kissthechef

New Member
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

Well-known Member
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

New Member
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

Replies
2
Views
98
Replies
2
Views
218
Replies
4
Views
73
Replies
5
Views
134
Replies
1
Views
77