Forum Discussion
Solved
Random Number Generator
How can i create a random number generator with 125 values, a range of 1.1-2.1, where the sum of those numbers equal 245
Sgeffert22 I think the concept is possible but your criteria may be the issue. 125 numbers with a sum of 245 means an average of 1.96 and your allowed range is 1.1 - 2.1 so the average is almost at the top of the scale. Here is a solution that 'works' but quickly maxes out and you end up with many 2.1 values:
=LET(countdown,SCAN(C2,SEQUENCE(B2,,B2,-1), LAMBDA(p,i, LET(high,MIN(A3,p-(i-1)*A2), low,MAX(A2,p-(i-1)*A3), p-low-RAND()*(high-low)))), DROP(VSTACK(C2,countdown),-1)-countdown)what this does is start with the sum value and subtracts a random value in the available range until it reaches 0. actually I just realized I need to adjust it to do only n-1 cases and the last case is the remainder. but that said, it will keep adjusting the 'high' and 'low' range to make sure it is possible to reach that max and because of the range and values you picked it doesn't take long before it maxes out and returns 2.1 every time there after.
One more possible way...
=MIN(1.1+RAND()*3.2, 2.1)
Tap the F9 key a few times to see what you can get.'---
Nothing Left To Lose
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
(free excel programs)
Theoretically, I believe this *might* be possible with a recursive LAMBDA. I've made some attempts, but each resulted in a memory error:
'Regen =LAMBDA(n,LET(r,RANDARRAY(n,,1.1,2.1,0),IF(SUM(r)=245,r,Regen(n))))Another approach might be to start with 245 and repeatedly subtract between 1.1 and 2.1 until the # of subtractions = 125. This is tricky because there would have to be recursive stacking (and dropping) to arrive at a 245 total with precisely 125 attempts.
mtarler might have a different take on this task
- Patrick2788 I’ve looked all over and tried everything I can think of. I’m beginning to think they may not be possible to do.
Sgeffert22 I think the concept is possible but your criteria may be the issue. 125 numbers with a sum of 245 means an average of 1.96 and your allowed range is 1.1 - 2.1 so the average is almost at the top of the scale. Here is a solution that 'works' but quickly maxes out and you end up with many 2.1 values:
=LET(countdown,SCAN(C2,SEQUENCE(B2,,B2,-1), LAMBDA(p,i, LET(high,MIN(A3,p-(i-1)*A2), low,MAX(A2,p-(i-1)*A3), p-low-RAND()*(high-low)))), DROP(VSTACK(C2,countdown),-1)-countdown)what this does is start with the sum value and subtracts a random value in the available range until it reaches 0. actually I just realized I need to adjust it to do only n-1 cases and the last case is the remainder. but that said, it will keep adjusting the 'high' and 'low' range to make sure it is possible to reach that max and because of the range and values you picked it doesn't take long before it maxes out and returns 2.1 every time there after.
