Random List of Numbers not Exceeding Sum

[benderboyboy]

Heyas.

I'm looking to generate a list of 7 random numbers that when added up will not exceed a predetermined number or go below a minimum number. For example, I enter a predetermined number at 50 and minimum at 5, and generate a list of 7,6,12,9,9,6,1.

I've done a workaround by using this on a column, with B9 being the max and B10 being the minimum.

=RANDBETWEEN($B$10; IF($B$9-($B$10*5)-1>20; 20; $B$9-($B$10*5)-1))
=RANDBETWEEN($B$10; IF(($B$9-SUM(B2:B2))-($B$10*4)-1>20; 20; ($B$9-SUM(B2:B2))-($B$10*4)-1))
=RANDBETWEEN($B$10; IF(($B$9-SUM(B2:B3))-($B$10*3)-1>20; 20; ($B$9-SUM(B2:B3))-($B$10*3)-1))
=RANDBETWEEN($B$10; IF(($B$9-SUM(B2:B4))-($B$10*2)-1>20; 20; ($B$9-SUM(B2:B4))-($B$10*2)-1))
=RANDBETWEEN($B$10; IF(($B$9-SUM(B2:B5))-($B$10*1)-1>20; 20; ($B$9-SUM(B2:B5))-($B$10*1)-1))
=RANDBETWEEN($B$10; IF(($B$9-SUM(B2:B6))-($B$10*0)-1>20; 20; ($B$9-SUM(B2:B6))-($B$10*0)-1))
=$B$9-SUM(B2:B7)

But the problem with this is that it runs each number individually from top to bottom. So when the max number is small, it has a tendency to stack on top.

Example:
Max: 15; Min: 1
5
2
3
2
1
1
1

The numbers are used separately for another calculation, so I need them to be random within themselves. Any solutions?

[Zizi64]

Please upload a real, ODF type sample file here.

[RusselB]

Each of the RANDBETWEEN functions is working with a relatively smaller set, as your high end number is restricted by the sum of the previously generated numbers.
If I understand correctly, you not only want the random generated numbers to be between the minimum and maximum that you have stipulated, but they also need to be between the smallest number generated and the largest number generated.
If this is correct, I'm going to need some time to figure out how to do it. I know it can be done, I just don't know how easily it can be setup in Calc.
If this is not correct, please try to correct/clarify my understanding.

It would also be helpful if you uploaded a sample of your spreadsheet, with some dummy data, showing not only the results you are getting, but also (probably manually entered) the results you were wanting/expecting.

[RoryOF]

Would it not be simpler to generate a list of all the combinations that satisfy the criteria, then choose among these list elements at random?.

[benderboyboy]

@Zizi64 - I've uploaded a sample.

@RusselB - Yes, that's more or less correct. The number generated is regulated by a IF-ELSE, where if there are more "points" available, they will generate between the minimum and 20. But if there are less, they will generate between the minimum and whatever is left. I should have also clarified, the final number (SEED_7) in the set need not be more than the minimum, and can just be whatever is left (optional). However, as you point out, the larger my numbers start with, the lower the set is as it goes down the line, making the randomness less... well, random.

@RoryOF - Would it? I'm not sure if OOC can generate such a list. If so, then yes, it would definitely be a lot easier. But the list would be ridiculously large. There will be at least 300 million possible combinations. Unless there is a way to only generate a new list every time I enter a new MAX/MIN.

[RoryOF]

Sorry - I misunderstood - I took it that you were requiring only the 50/5 limits.

[keme]

One approach is to generate random numbers first. When you have the full list you can scale each element by some random factor to fit the set limitations.

In the attachment it is conceivable (though unlikely) that rounding errors push the sum out of bounds. You could bias the multiplier bounds (max and min multiplier) towards the middle to reduce the probability of this error.

[benderboyboy]

One approach is to generate random numbers first. When you have the full list you can scale each element by some random factor to fit the set limitations.

In the attachment it is conceivable (though unlikely) that rounding errors push the sum out of bounds. You could bias the multiplier bounds (max and min multiplier) towards the middle to reduce the probability of this error.

Yes, I was considering that. But it would be impossible for a random list to adhere to max limit, or at least, that I know of.

Another idea I have is to randomise a list of 1-6 in random order, and to assign those numbers as multiplicable factors to the set I want to generate. But I'm currently having a hard time getting both of those portions to work.

[benderboyboy]

So, currently, this is my solution. I use a RANK list of 1-6 to add a high-low probability to the main list. Where the main list divides by whatever the RANK number they correspond to when below a certain level and minus after. It's still not perfect, but it's the closest I have to a truly random list of numbers without exceeding their sum total.