# 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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### 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

#### 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
86
Replies
2
Views
172
Replies
4
Views
50
Replies
5
Views
105
Replies
1
Views
64