Basically, I would like to take a random list of positive whole numbers whose sum is equal to a specific range.
For example:
Generating 10 random positive whole numbers whose total sum is between the values of 25 and 100 inclusive.
Coming up with a formula for excel is a bit over my head. If anyone has any suggestions that would be very helpful!
One way to do this is as follows. I will use the numbers you gave in the example, but the method is easily generalized to arbitrary numbers.
In column B, I generate numbers from an exponential distribution. Then in Column C, I normalize them by dividing each one over their sum. This way, each number turns into a proportion (i.e., they all sum up to 100%). Finally, in Column D I take this proportion and multiply it with a random number between 25 and 100 (better consider generating numbers in a tighter range, to avoid rounding errors - I am using 27 and 95 in my example, to also account for the bias of rounding up), and round it up. The sum is in cell D12.
Here is a sample screenshot:
and here is the distribution of the sum of these numbers, when using 1,000 iterations:
A further idea to tackle rounding is to round up only when the resulting number is zero:
ROUND(C2*$E$2,0)+(ROUND(C2*$E$2,0) = 0)
This introduces a small bias as getting a 1 is slightly more likely than getting other numbers - but the distribution of the final sum is practically uniform.
I hope this helps.
Disclaimer: I used this answer as a base for mine. The statistical justification is that this problem is equivalent to generating numbers from a Dirichlet distribution.
In A1 through A9 enter:
=RANDBETWEEN(2,10)
and in A10 enter:
=RANDBETWEEN(7,10)
