Random number generation with constraints

But, one more constraint is that total number of job size need to fix to 20 (as an example). Any idea ?

Yes. 5 random numbers have a maximum of the number in col G AND the sum of these random numbers needs to add up to 20. I want to find a way to automate this task.
Instead of pressing F9, is it possible to write a code or macro so that it will achieve the desired sum number everytime it run?

Hi

The attached file uses macros to automate the whole process. Another bonus - the numbers don't change every time the sheet is recalculated - they only change when your run the macro.

Enter the target in cell E10. Enter the maxima for each job.

Press ALT-F8 and run "FindResult"

The macro relies on there being two columns between the Max and Job columns. Also, I have added two named ranges.

Cheers, Rob.

Hi Rob,

I not able to open the excel file:

Error message:"Excel found unreadable content in "job size randomness.xlsm" Do you want to recover the contents of this workbook? If you trust the source of this workbook, click yes."

When i clicked yes, the repaired file is blank.

The repaired record:
Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.
Removed Records: Worksheet properties from /xl/workbook.xml part (Workbook)

How to fix this error? I really hope to try your solution.

Regards,
SC

Hi

Here is the retry of the upload. File size looks correct this time.

Cheers, Rob.

Hi Rob,
It is working this time, thank you. But the problem is everytime i re-run it, it gives me the same solution.
I would like to have different combination of job sizes that could meet the target cell (E10). Is it possible to do so?

Regards,
SC

Apologies, SC, I forgot to include the Randomize command at the top of the macro.

Attached file corrects this oversight.

Cheers, Rob.

Rob, Thank you very much. It works exactly what i want.