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!
 

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
 

Forum statistics

Threads
1,081,705
Messages
5,360,751
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top