Random number in specific range & Total

Friends,
I like to randomly distribute #30 between 15 columns

#30 Should be randomly distributed between the 15 columns
Randome numbers should be generated between 0 to 5
Total must be 30

I use the following command to generate random numbers.
=RANDBETWEEN(0,5)

But how to fix the total to 30.

Please check the attached file.

Thanks

There is no problem about duplicate Randoms
(Waiting for your solution friends)

Select B3:P3, pase =RandLen(30,,,TRUE) in the formula bar, press and hold the Ctrl and Shift keys, then press Enter.

There is no limit on size, so there may be values > 5.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hi shg,
What are the other (non-relevant in this case) arguments in your UDF? The second one looks like a minimum value?

Thanks Mr.shg
I'm not a expert in excel and couldn't understand your answer.Can anyone tell me about this briefly?
OR
You can update the formula in the above sample file and attach here

The code is for a UDF (User defined function)
Copy and paste the code into a VBA module
Alt + F11 opens the VBA Editor
Insert> Module
Paste code
Close VBA Editor window (no need to save)

Then Copy
=RandLen(30,,,TRUE)
Select your range which you want to add to 20 (i.e B3:P3)
In the formula bar paste the formula and hit CNTRL SHFT ENTER
That enters the formula as an array into the selected cells. You should see brackets around the formula {}
Did that help?

The function was designed to return a set of numbers with a Beta distribution. It would take some surgery to limit the maximum value, and it would change the distribution of the result. Someone else may have a suggestion that works better for you.

@Chemist: Thank you.

Found an old shg code Treat similarly to above
=RandTot(30,0,5) entered as an array

Please Login or Register  to view this content.