Weighted RANDBETWEEN() ?

avsrock90

New Member
Joined
Dec 7, 2010
Messages
2
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!
 

Some videos you may like

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
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
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.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Jul 16, 2012
Messages
2

ADVERTISEMENT

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
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
Use the formulas in post#2 and replace the numbers in col A with your numbers.
 

mtimik

New Member
Joined
Jul 16, 2012
Messages
2

ADVERTISEMENT

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

kissthechef

New Member
Joined
Oct 2, 2011
Messages
6
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
Joined
Mar 2, 2014
Messages
2,813
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Oct 2, 2011
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,502
Messages
5,529,245
Members
409,857
Latest member
KailuaTown
Top