How to weight randomization (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:00
Joined
May 21, 2018
Messages
8,463
I added a field in your table for the random assignment.

query 1a and 1b work together.
1a demos how to do the select query
1b uses the select query to do an update query

query 2 does it all in one step.
So you can run 1b or 2 and it will update your table.

I did another query to verify the data. It is an aggregate
Code:
Random_Assignment	CountOfRandom_Assignment
0	                502
1	                303
2	                92
3	                103
results are as expected 50, 30, 10, 10
 

Attachments

  • RandomDis.accdb
    956 KB · Views: 102

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:00
Joined
May 21, 2018
Messages
8,463
Not sure of your final purpose, but if you are going to expand this, most often data is pulled from common distributions. For example a bell curve normal distribution. The theory is the same. You sample from a uniform 0,1 and run that value through the inverse CDF. You can call all of these inverse functions from Access using the worksheetfunction.
This example pulls a random number from a normal distribution with a given mean and standard deviation.

Code:
Public Function GetRandFromNormal(Mean As Double, StdDev As Double, Optional ID As Long, Optional Repeatable As Boolean = False) As Double
  Dim rtn As Double
  If Not Repeatable Then Randomize
  GetRandFromNormal = Excel.Application.WorksheetFunction.Norm_Inv(Rnd(), Mean, StdDev)
End Function

This format can be modified for any of the excel inverse cdf functions.
 

Micron

AWF VIP
Local time
Today, 07:00
Joined
Oct 20, 2018
Messages
3,476
@Micron what would the “cumulative” column look like in this case?
Not sure I understand the question because that field is right in the post. Note that the order of random values in the table is in the order of random weights. The first cumulative value should be zero, the rest are the sums of the cumulative value and the random weight from the previous record. The research I did on this indicates that's how it's set up. In the example I posted, if rnd returns 0.14 (X) the SELECT returns 3 because .1 is the highest cumulative value that is less than X. If rnd returns a value from .4 to .99 the selected value would be 0. If rnd returns .21 the value would be 1.

AFAIK, if your cumulative values don't add up to 1, your results will be skewed as I pointed out. If their sum exceeded 1, your rnd results will also skew the results if rnd isn't multiplied by a factor large enough to exceed your highest cumulative value. In the example I provided, rnd only needs to be from just over zero to 1. Lastly, I overlooked that for the approach I posted, X cannot be coerced to Int type.
 

stillsarah

Registered User.
Local time
Today, 04:00
Joined
Jan 26, 2019
Messages
14
I'm back :(

If you get the first part working then we can use that to write back to your table.

@MajP I'm trying to find in your code how you did this, but can't.

Assuming you didn't copy and paste?
 

Users who are viewing this thread

Top Bottom