I am thinking if there is anyway to generate a set of random numbers of which the sum is always a constant. For example, 20 can be divided into 5 numbers ( 1, 2,3,4,10) I don't care what each of the 5 numbers is as long as their sum is equal to 20. Is there anyway to do so programmatically?
You could use the RAND() function to generate N numbers (8 in your case) in column A. Then, in column B you could use the following formula B1=A1/SUM(A:A)*320 , B2=A2/SUM(A:A)*320 and so on (where 320 is the sum that you are interested into). So you can just enter =RAND() in A1, then drag it down to A8.
If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND() in the formula bar, and then press F9 to change the formula to a random number.
The mean number of random number draws to yield a sum which exceeds 1 may then be written as a sum of the number of draws multiplied by the probability, as determined above, of that number of draws being the one which sums to more than 1.
To get a uniform distribution, the trick is to think of your sum as a number line, and rather than generating random numbers for the segments, generate n-1 numbers as points along the line, and subtract to get the segments. Here's the function from ojrandlib:
static int compare(const void *a, const void *b) {
return *(int*)a - *(int*)b;
}
void ojr_array_with_sum(ojr_generator *g, int *a, int count, int sum) {
int i;
for (i = 0; i < count-1; ++i) { a[i] = ojr_rand(g, sum+1); }
qsort(a, count-1, sizeof(int), compare);
a[count-1] = sum;
for (i = count-1; i > 0; --i) { a[i] -= a[i-1]; }
}
ojr_rand(g, limit)
generates a uniform random integer from 0 to limit-1. This function then fills the array a
with count
random integers that add to sum
. Shouldn't be too hard to adapt this to any other RNG.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With