generating a set of random numbers to total a set value

[yaldan]

Is it possible to generate a set of random numbers between say 50 - 150 whereby the sum of those random numbers totals a value in the spreadsheet which is determined by two other values in the spreadsheet?

Eg: value 1 = 10, value 2 = 300. The difference = 290. Now generate 6 or so random numbers between 50 - 150 that come to a total of 290.

If so could you helpme with formula or script.

Regards

Yaldan

 

[melvinkoshy]

‘w’ denotes working day. The program should populate data only against “w”. “h” is deemed as holiday.

 

[is2_egypt]

Hello , may be the wording of my question was not vert clear.

i want formula give me random values M (lets say 5 values ) , these values are integers not fractions , sum of them equal X , lets say 100
Example : results can be like : 5,10,14,9,62

Thanks for your support.

 

[oldbrewer]

if they are random you might get 62,31,9 so target exceeded - a macro would abort and start again at this stage - easy to write

if 4 numbers were 15,20,25, 29, then could I make the last one 11 ?

 

[is2_egypt]

Dear

this is not correct , this formula for example give the perfect results but unfortunately it allows fractions , but total will be always equal to X (100 here)

=A1*100/SUM(A$1:A$M) where M equal 10 for example (10 random values).

i wish i have modification to this formula that allows only integer values (not fraction) and keep sum up to 100 exact

 

[oldbrewer]

use a macro to generate 5 random different integers and if they add up to exactly 100 print them out, or restart macro.