Hi,

I need some help developing a formula that I believe should probably include RandBetween. I need 50 random numbers total to be placed in column J starting at row 6. I have a target number in cell J3 that all 50 random numbers generated must sum to. Each of the 50 numbers cannot be a larger number than that of the number found in the same row in column I (the numbers in column I change with each row). Example is below:

I J
3 6000


4
5
6 1505 rand
7 5625 rand
8 2568 rand
9 3256 rand

in this example all rand numbers in rows 6-9 will sum to 6000 (found in J3) but will not be larger then their corresponding numbers in column I. So, one combination could be 1123, 856, 2568, 1453.

Can anyone help with a formula NOT VBA method?

Many thanks,
Ann

That is INTERESTING, acbh! I don't even care why you want such an outlandish thing, it's just fascinating to think about it.

Ok, first let me reproduce your sample in a fixed-spacing font so I can see it clearly:Is that right? Now let's break this down. For the last random number (in J9) to be part of the series it must not exceed I9, yet it must make the sequence sum to J$3. In other words J9 will not be random at all; it must be =J$3-SUM(J$6:J$8).

The other three numbers must be random, but they must not be allowed to be so small that their sum is less than J$3-I$9. Let's see, a random number for any row is too small if it, plus all the random numbers below it, plus all the maximum allowed numbers for the rows after it, will still be less than J3. So the bottom value for J7 (for example) is "=J$3-SUM(I8:I$9)-SUM(J$6:J6)". No, wait, it has to start below J6. So what happens if we use this for J6:J8?Ok, that's good for a start but I see it allows one or more of the numbers to be negative. Come to think of it, you didn't forbid that in your definition. Would you regard 40, 5509, -393, 844 to be a valid solution? Or 327, 4583, 2282, -1192? If so, you have an answer.

It isn't necessarily the best answer. It could be you won't care for the distribution, too many high numbers at the top and too many low ones at the end, or something. But I'll start by claiming to have found what you want. If you want to improve on it, for example by forbidding negative numbers, speak up and let's see where this goes from here.