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!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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.
 
Upvote 0
Since your numbers are the same as the indices, you can skip the INDEX part and just use:

=MATCH(RAND(), C2:C12)
 
Upvote 0
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%
 
Upvote 0
Use the formulas in post#2 and replace the numbers in col A with your numbers.
 
Upvote 0
Thanks, worked perfectly. Just thought the Rand() function would not return negative values but I see how this formula works now.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top