Use of RAND() and RANDBETWEEN() functions to get a certain fixed sum.

[empk]

I have to fill a range of 6 cells with random numbers from 0 - 4 (repetition allowed) and also limit the sum of the numbers in the range to 15. How do I achieve that using either RAND() or RANDBETWEEN() functions?

Thanks in advance for your help.

 

[Haz]

Formula in A2
=RANDBETWEEN(0, MIN(15 - SUM($A$1:A1), 4))

Copy and paste in A3:A7

 

[empk]

Formula in A2
=RANDBETWEEN(0, MIN(15 - SUM($A$1:A1), 4))

Copy and paste in A3:A7

Thanks a ton Mr. Haz. It worked fine.

 

[Peter Bartholomew]

That will ensure the total does not exceed 15 but does not ensure that the total is reached. It also would produce a higher proportion of 0s in the final places than might be expected of a random process.

At any given stage the number of combinations available to complete the sequence will depend upon the number chosen at that step so rather than taking a uniform distribution for each number, it should be drawn from a non-uniform probability distribution. That involves forming a cumulative distribution and using lookup to convert the random value to an integer [0-4].