Random numbers to a sum of constant value in excel

Question

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.

So set it up using the solver, you can add an integer constraint as well. — Solar Mike
– Solar Mike, Commented Mar 8, 2022 at 8:05

Solar Mike · Accepted Answer · 2022-03-08 09:21:57Z

5

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.

edited Mar 8, 2022 at 9:21

answered Mar 8, 2022 at 8:08

Solar Mike

8,4255 gold badges23 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jack Over a year ago

Is it also possible to run the solver for multiple outputs at once? I have a total of 5 entries for different items. Each entry has a different sum of random numbers like in previous case was 48 for the first entry. If I may process for all entries (48, 52, 58, 50, 55) all at once while keeping the other parameters/constraints same as before without running the solver independently for each entry?

Solar Mike Over a year ago

Yes, have the sum of the results as the objective and set it to a max or min. Then set constraints for each of the results to be 488, or 62 or 71 etc just add the constraints for each set.

Solar Mike Over a year ago

@Jack check the edit, just expands the array really. Any further expansion you should be able to figure out.

Collectives™ on Stack Overflow

Random numbers to a sum of constant value in excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related