I would like to generate 14 random numbers in excel between a certain range (3 to 5) that adds up to a certain value lets say 48. The numbers generated between 3 to 5 can be repeated.

I used the =RANDBETWEEN(3,5) from columns D5:Q5. And then in another sheet where I want the result to be displayed I wrote =ROUND(Sheet5!B2/SUM(Sheet5!$B$2:$O$2)*48,0)

Everytime I press F9 it generates random numbers that is fine. But not everytime the total adds up to 48. Sometimes the sum is higher and sometimes the total is lower than 48. How can I make it to a total of 48 instead of random sum.

CodePudding user response：

So set it up - I laid it out like this but you can change that:

You can add flexibility by bringing the constraints for the 3 and 5 onto the sheet - which keeps them visible when you keep trying different scenarios. Like this:

So, based on the comment:

Went back to the other method to state the constraints.