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]

As an extension to this problem, I have dates and the day as columns and also included a working day / holiday flag as w & h respectively. The populated values shall be dependent on flags. If the flag is 'h', then the km should be zero. It should also take into account number of working days by counting the “w”. I have made the sheet but is held up at the flag field.
How to modify this?

WORKING FILE : http://s000.tinyupload.com/?file_id=12237625099340265774

 

[oldbrewer]

can't see the link between this and previous posts, and what do you mean by counting the w's

 

[is2_egypt]

Hello

this is good , but how can we make the random values integers without any fraction please ?

 

[oldbrewer]

=int(rand())

 

[joeu2004]

but how can we make the random values integers without any fraction please ?

If you want a random integer between -10 and 10, for example, use =RANDBETWEEN(-10,10) .

=int(rand())

That should always return zero.

There is a "corner-case" where INT(n*RAND()) might return n (!) instead 0 to n-1. The defect arises because INT rounds to 15 significant digits before rounding down, and n*RAND() is sufficiently close to n that INT effectively rounds up. I have seen this defect in Excel 2007 and earlier. Not sure it can happen in Excel 2010 and later, wherre RAND uses a different algorithm.

 

[is2_egypt]

If you want a random integer between -10 and 10, for example,

hello , yes but how can we make it generate integers (not fraction ) and the sum up of all random values is constant value lets say 20 random numbers and sum is 100

thanks alot for your support


There is a "corner-case" where INT(n*RAND()) might return n (!) instead 0 to n-1. The defect arises because INT rounds to 15 significant digits before rounding down, and n*RAND() is sufficiently close to n that INT effectively rounds up. I have seen this defect in Excel 2007 and earlier. Not sure it can happen in Excel 2010 and later, wherre RAND uses a different algorithm.

yours
Ibrahim

 

[joeu2004]

There is a "corner-case" where INT(n*RAND()) might return n (!) instead 0 to n-1. The defect arises because INT rounds to 15 significant digits before rounding down, and n*RAND() is sufficiently close to n that INT effectively rounds up.

I meant add....

So, if you do not want to use RANDBETWEEN(0, n-1) for some reason, it would be prudent to write:

MIN(n-1, INT(n*RAND()))

 

[oldbrewer]

typo my post meant =int(rand()*100)+1

 

[joeu2004]

yes but how can we make it generate integers (not fraction ) and the sum up of all random values is constant value lets say 20 random numbers and sum is 100

I just realized that you entered your follow-up question in the wrong part of the posting -- the part that "quotes" a previous response. Please do not type between the quote and /quote tags.

Since your question has already been addressed in this thread, please explain (with formulas and concrete examples) what you have done to apply the answers that have been provided, and why those answers to do not work for you.

Even better: start a new thread, referring to this thread and adding your question, with formulas and concrete examples.

 

[oldbrewer]

so you want 20 numbers to add to 100 ie average integer value of 5 so 19 x 1 and 81 fits

I would run a macro that aborts if the running total exceeds 100