Random numbers whose sums equals "x"

Is this something close?? Two formulae:
Column A:
=RANDBETWEEN(MAX($I$1,$I$4-(($I$3-COLUMNS($A2:A2))*$I$2)),MIN($I$2,$I$4-$I$3))

Other columns (B-F):

Formula:

Please Login or Register  to view this content.

All variables can be adjusted from the Table on the right.

Cross-posted at https://www.mrexcel.com/forum/excel-...otaling-x.html

Oh Pooh!! Please don't waste everyone's time by cross-posting WITHOUT including links to the other thread(s).

This is something I see alot of people ask for, but if you require the numbers to add up to a specific value...they arent random any more. Especially eliminating decimal values (as using decimals mean an infinite combination is possible). Are we presuming positive integers only or can we use negative numbers?

What you are really asking for is to randomly pick 1 of the possible combinations that add up to a number.

To explain (simplified, presuming positive integers only):

Lets say you want me to randomly pick 2 numbers that add up to 10. I can randomly pick just about any number (random) as my first right? So I pick 7 lets say. Well there is only 1 number I can pick as my second number to satisfy the criteria which is 3...not really a random choice.

So going back to it being "randomly pick 1 of the possible combinations that add up to a number" this then requires other criteria/considerations to determine how it picks. Lets take your request, 5x numbers = 6,000.

You could do:
1st number can be RANDBETWEEN(1,6000)
2nd number can be RANDBETWEEN(1,6000-1st)
3rd can be RANDBETWEEN(1,6000-SUM(1st,2nd))
4th can be RANDBETWEEN(1,6000-SUM(1st,2nd,3rd))
5th has to be 6000-SUM(1st thru 4th)

The above creates a bias though doesnt it, since the 1st selection can always pick from a wider range of number than all those after it and the 4th number for example will never be able to pick at the highest > 6000-3 (if 1st,2nd,3rd were all =1). Then lastly the 5th value isnt really random.

Alternatively you may be able to randomly pick which number you pick 1st, 2nd, 3rd, 4th and 5th...IE: 4th, 3rd, 1st, 2nd, 5th but this is really an illusion (presuming you generate each number in a cell of its own, than the order on sheet is just a different representation of the above)...you still have the same issue as above. With each number you generate in the sequence your options for what numbers can be used to equal your goal diminish.

Any other criteria/considerations can drastically change things...for example someone may then say, "Well I really want the 5 largest numbers that = 6000"...now you have to generate every single combination of 5 numbers that = 6000 and rank each against the others to determine this. Thats very complex, mathematically difficult and not something Excel is good at doing.

So if the above logic accomplishes what you aim to do great, but if what you are aiming for is more complex than you have let on, you are likely using the wrong tool for the job

The approach of generating random numbers and scaling by their sum favors mid-size numbers at the expense of small and large numbers.

The approach described in post #5 favors large and small numbers at the expense of mid-size numbers.

The UDF at the cross-post generates numbers with a Dirichlet distribution, the same distribution as the numbers in all 5-element partitions of the sum.

They are separate fora. the point is that someone could have spent time answering on one forum, totally unaware of solutions already provided elsewhere. Most fora require/request cross-posted threads to be flagged up right at the start.

Cheers. Hope someone has provided you with what you need.