vba random number generator w/weighted averages

[bahllr]

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!

 

[Andrew Fergus]

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

   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

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