vba random number generator w/weighted averages

bahllr

Board Regular
Joined
May 7, 2009
Messages
62
I currently have the following code to generate a random number between the constraints of B9 and B8 (dynamic):

Randomize
MyValue = Int((Range("B9") - Range("B8")) * Rnd + 0.5) + Range("B8")

As it currently is calculated, each number in that range of B8:B9 (ex: 1-4) is weighted equally and given the same chance of being selected as a random number.....

I would like to incorporate a set of cells that allow the user to assign a specific weight/probability of the values inbetween the range B8:B9 (ex: 1-4).

So: Instead of chances 1-4 all having a 25% chance of being returned by the code, I would like to be able to say for each spot

Spot 1: 10%
Spot 2: 30%
Spot 3: 40%
Spot 4: 20%

etc. That way, Spot 3 actually has a 40% chance of being selected (rather than 25%).

Does anyone know how to adapt the code i have to get this....or new code?

Thanks in advance!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

Your current formula is not giving you an even spread of values. Check out the attached spreadsheet which compares your method and the distribution of results with an alternative method I have labelled as 'New' in column D.

I have also shown the mechanics you would use for 'rigging' the distribution of values. Note I used a Rnd interval of 1/16 and then a range with a similar multiple (of 4) for the purposes of this demonstration.

I haven't accounted for the probability the Rnd function will retun a 0 - but you could test for that and force the value to a very very small value so it doesn't throw up a strange value. Notice also in cell I24 I have 'cheated' by forcing the lookup value to be fractionally over my split % to get the lookup function to work.

Hopefully this will give you some food for thought. Also, if you want to 'rig' the outcome then you will need to hold the percentage values somewhere on the worksheet.

Andrew

Code:
   A            B   C       D      E      F  G       H     I      
 1 Min          Max Range          Data                           
 2 3            6   4              16                             
 3                                                                
 4 Rnd              Current New    Rigged                         
 5 0.0625           3       3      3                              
 6 0.125            3       3      3                              
 7 0.1875           4       3      4                              
 8 0.25             4       3      4                              
 9 0.3125           4       4      4                              
10 0.375            4       4      4                              
11 0.4375           4       4      5                              
12 0.5              5       4      5                              
13 0.5625           5       5      5                              
14 0.625            5       5      5                              
15 0.6875           5       5      5                              
16 0.75             5       5      5                              
17 0.8125           5       6      6                              
18 0.875            6       6      6                              
19 0.9375           6       6      6                              
20 1                6       6      6                              
21                                                                
22 Distribution     Current Better Rigged    Rigging Split Lookup 
23 3                12.5%   25.0%  12.5%     3       12.5% 0.0%   
24 4                31.3%   25.0%  25.0%     4       25.0% 12.5%  
25 5                37.5%   25.0%  37.5%     5       37.5% 37.5%  
26 6                18.8%   25.0%  25.0%     6       25.0% 75.0%  
Sheet2
[Table-It] version 09 by Erik Van Geit
Code:
RANGE   FORMULA (1st cell)
A5      =1/16
A6:A20  =A5+1/16
C2      =B2-A2+1
C5:C20  =INT(($B$2-$A$2)*A5+0.5)+$A$2
C23:E26 =COUNTIF(C$5:C$20,$A23)/$E$2
D5:D20  =(($C$2*CEILING(A5,1/$C$2)))+$A$2-1
E2      =COUNT(A5:A20)
E5:E20  =LOOKUP(A5,$I$23:$I$26,$G$23:$G$26)
H23     =2/16
H24     =4/16
H25     =6/16
H26     =1-SUM(H23:H25)
I24     =H23+0.000000001
I25:I26 =I24+H24
[Table-It] version 09 by Erik Van Geit
 

Watch MrExcel Video

Forum statistics

Threads
1,099,795
Messages
5,470,827
Members
406,727
Latest member
Kimze

This Week's Hot Topics

Top