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

 

[SteveO59L]

If you had 6 random numbers between 50 and 150, even if they were all 50, they would never sum to 290.

 

[SIXTH SENSE]

could number be repeated or each number should be unique from each other?

 

[SteveO59L]

Enter the formula =randbetween(50,150) in cells G11:G16, and =sum(g11:g16) in cell G18.

This will generate 6 random numbers between 50 and 150, and their sum.

If you target value is in D18, then the following will refresh the random values until the values in D18 and G18 are equal.

Do Until Range("g18").Value = Range("d18").Value
Calculate
Loop


End


However, you would need some form of error trapping to make sure that your target value is never less than (6 * 50) 300, and never more than (6 * 150) 900, otherwise you would go into and endless loop as you cant reconcile between the two numbers

 

[steve case]

Generate six Random numbers in Range A1:A6 [=rand()]

Cell A9 enter your target sum total

In Cell A8 a formula to devide the target sum by the sum of the six random numbers:
=(A9/(SUM(A1:A6)))

Range B1:B6 multiply each of the six random numbers by contents of Cell A8
=A1*$A$8

Cell B8 Run a check:
=SUM(B1:B7)

 

[kachaloo]

Generate six Random numbers in Range A1:A6 [=rand()]

Cell A9 enter your target sum total

In Cell A8 a formula to devide the target sum by the sum of the six random numbers:
=(A9/(SUM(A1:A6)))

Range B1:B6 multiply each of the six random numbers by contents of Cell A8
=A1*$A$8

Cell B8 Run a check:
=SUM(B1:B7)

You are genius....
I have been looking for this and everybody said it is impossible.....

 

[Smailor]

Just wow. Truly helpful. Was searching for solution for hours.

 

[hselek]

Generate six Random numbers in Range A1:A6 [=rand()]

Cell A9 enter your target sum total

In Cell A8 a formula to devide the target sum by the sum of the six random numbers:
=(A9/(SUM(A1:A6)))

Range B1:B6 multiply each of the six random numbers by contents of Cell A8
=A1*$A$8

Cell B8 Run a check:
=SUM(B1:B7)

What do we do if we need those numbers to be an integer ? Please help...

 

[Eric W]

You could do something like this:

Excel 2010

	A	B	C	D
Target	Min	Max	Number of random numbers

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A4[/TH]
[TD="align: left"]=RANDBETWEEN(B2,C2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]=IF(ROW()=$D$2+3,$A$2-SUM(OFFSET($A$4,0,0,$D$2-1)),IF(ROW()>$D$2+3,"",RANDBETWEEN($B$2,$C$2)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=IF(OR($C$2*D$2<$A$2,$B$2*$D$2>$A$2),"Can't be done!","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the values in A2:D2 that you want. Put the formula in A4. Then put the formula in A5 and copy down as far as you need. The last number in the list (A10 in this example) is a calculated number designed to make the list add up. It could theoretically be less than the minimum or higher than the maximum. If that happens, just press F9 to recalculate until you get a valid number. The formula in B4 is just a check to see if the values in A2:D2 are viable.

Let me know if this helps.

 

[hselek]

You could do something like this:

Excel 2010

	A	B	C	D
Target	Min	Max	Number of random numbers

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A4[/TH]
[TD="align: left"]=RANDBETWEEN(B2,C2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]=IF(ROW()=$D$2+3,$A$2-SUM(OFFSET($A$4,0,0,$D$2-1)),IF(ROW()>$D$2+3,"",RANDBETWEEN($B$2,$C$2)))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B4[/TH]
[TD="align: left"]=IF(OR($C$2*D$2<$A$2,$B$2*$D$2>$A$2),"Can't be done!","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the values in A2:D2 that you want. Put the formula in A4. Then put the formula in A5 and copy down as far as you need. The last number in the list (A10 in this example) is a calculated number designed to make the list add up. It could theoretically be less than the minimum or higher than the maximum. If that happens, just press F9 to recalculate until you get a valid number. The formula in B4 is just a check to see if the values in A2:D2 are viable.

Let me know if this helps.

Thanks for your answer

What I need in my particular situation is 2 numbers both of which does not exceed 25 but also not less than 10 to target 50 when added to each other. But in this example the generated numbers can exceed my maximum.
I would be really glad if you could help.
Thanks again