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

 

[hselek]

Sorry I could not edit my post but I could not explain it well.I am retyping again as it seems a little bit silly at the moment

 

[Eric W]

With only 2 numbers, each of which has a max value of 25, that must sum up to 50, there is only 1 possible combination: 25 & 25. If you punch F9 enough, you'll get that combination.

I had thought of adding a formula that says something like "Solution is not valid, press F9".

I also thought of changing the formulas so that they would always generate a valid solution. But the problem there is that a) the formulas would be much more complicated, and b) the further down the list you go, the less "random" the numbers would get. They'd start trending high or low, which may well invalidate the randomness of the results.

Final option would be to write a VBA function that generates the list for you. It would essentially do the same thing as these functions, but automatically recalculate in case of an invalid result.

Let me know if any of those options appeal.

 

[hselek]

I need number 1 and number 2 to add up in such a way that they target the total (E1)
The "total"(E1) can be 100 maximum at any given time(I will decide the total) and number 1 can be 25 and number 2 can be 25 maximum). In this example the total(E1) is 85 so, for example A2 can be 15 and B2 can be 20. (since I am going to change the total I would really be glad if I could specify a minimum for the number1 and number 2 too)
I hope I could explain myself this time.

Thanks a lot

 

[Eric W]

OK, I created some formulas that will always generate a valid set of numbers, with no repeated F9. But as I warned you, the formulas are more complicated.

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"]20[/TD]

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

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

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

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

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

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

[TD="align: center"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/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(MAX($B$2,$A$2-(($D$2-ROWS($A$4:$A4))*$C$2)),MIN($C$2,$A$2-(($D$2-ROWS($A$4:$A4))*$B$2)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]=IF(ROW()=$D$2+3,$A$2-SUM($A$4:$A4),IF(ROW()>$D$2+3,"",RANDBETWEEN(MAX($B$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$C$2)),MIN($C$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$B$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]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]=SUM(A4:A20)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Here's your 2-number example:
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"]50[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/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"]25[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

From your example in post #13:
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"]35[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/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"]18[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

Since your numbers 3 and 4 are fixed, subtract their total (50) from the overall total (85) to get the value (35) that numbers 1 and 2 must sum to.

 

[hselek]

OK, I created some formulas that will always generate a valid set of numbers, with no repeated F9. But as I warned you, the formulas are more complicated.

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"]20[/TD]

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

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

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

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

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

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

[TD="align: center"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/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(MAX($B$2,$A$2-(($D$2-ROWS($A$4:$A4))*$C$2)),MIN($C$2,$A$2-(($D$2-ROWS($A$4:$A4))*$B$2)))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]=IF(ROW()=$D$2+3,$A$2-SUM($A$4:$A4),IF(ROW()>$D$2+3,"",RANDBETWEEN(MAX($B$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$C$2)),MIN($C$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$B$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]
[TR]
[TH="bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]=SUM(A4:A20)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Here's your 2-number example:
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"]50[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/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"]25[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

From your example in post #13:
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"]35[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/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"]18[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

Since your numbers 3 and 4 are fixed, subtract their total (50) from the overall total (85) to get the value (35) that numbers 1 and 2 must sum to.

Thanks a lot. It really really helped me..

 

[masooque]

Respected Sir,
i have read your post on generate random numbers for a specific total.
I'm a high school teacher required same thing in a columns.... i have tried but not success..
will you help me for this.. i need random numbers in the columns..
i have attached my screen shot....
i want to put total marks in a total coulmn... and want it will convert in five random number exactly as i put total in a column...
Sir... I'm very thankful to you.... and if possible plzzz give your algorithm too.... so i can understand as well help my students for this...
Thanksss again

[TABLE="width: 384"]
<colgroup><col style="width:48pt" span="6" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]Mark1[/TD]
[TD="width: 64"]Mark2[/TD]
[TD="width: 64"]Mark3[/TD]
[TD="width: 64"]Mark4[/TD]
[TD="width: 64"]Mark5[/TD]
[TD="width: 64"]Total[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]23[/TD]
[/TR]
</tbody>[/TABLE]

 

[masooque]

OK, I created some formulas that will always generate a valid set of numbers, with no repeated F9. But as I warned you, the formulas are more complicated.

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"]20[/TD]

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

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

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

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

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

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

[TD="align: center"]10[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]100[/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(MAX($B$2,$A$2-(($D$2-ROWS($A$4:$A4))*$C$2)),MIN($C$2,$A$2-(($D$2-ROWS($A$4:$A4))*$B$2)))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A5[/TH]
[TD="align: left"]=IF(ROW()=$D$2+3,$A$2-SUM($A$4:$A4),IF(ROW()>$D$2+3,"",RANDBETWEEN(MAX($B$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$C$2)),MIN($C$2,$A$2-(SUM($A$4:$A4)+($D$2-ROWS($A$4:$A5))*$B$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]
[TR]
[TH="bgcolor: #DAE7F5"]B10[/TH]
[TD="align: left"]=SUM(A4:A20)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Here's your 2-number example:
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"]50[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/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"]25[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

From your example in post #13:
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"]35[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/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"]18[/TD]

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

Since your numbers 3 and 4 are fixed, subtract their total (50) from the overall total (85) to get the value (35) that numbers 1 and 2 must sum to.

Respected Sir,
i have read your post on generate random numbers for a specific total.
I'm a high school teacher required same thing in a columns.... i have tried but not success..
will you help me for this.. i need random numbers in the columns..
i have attached my screen shot....
i want to put total marks in a total coulmn... and want it will convert in five random number exactly as i put total in a column...
Sir... I'm very thankful to you.... and if possible plzzz give your algorithm too.... so i can understand as well help my students for this...
Thanksss again

[TABLE="class: cms_table, width: 384"]
<tbody>[TR]
[TD="width: 64"]Mark1[/TD]
[TD="width: 64"]Mark2[/TD]
[TD="width: 64"]Mark3[/TD]
[TD="width: 64"]Mark4[/TD]
[TD="width: 64"]Mark5[/TD]
[TD="width: 64"]Total[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]34[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]39[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]43[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]23[/TD]
[/TR]
</tbody>[/TABLE]
And I'm sorry to Admin... i'm new to this forum so i don't know how to del my first post which is without quote of this important thing... please forgive me....

 

[Eric W]

Here is a version that works horizontally, with 5 values as in your example:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
Mark1	Mark2	Mark3	Mark4	Mark5	Total

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Min[/TD]
[TD="bgcolor: #FAFAFA"]Max[/TD]
[TD="bgcolor: #FAFAFA"]Number of random numbers[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet15​

[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"]G3[/TH]
[TD="align: left"]=RANDBETWEEN(MAX($B$2,L3-(($D$2-COLUMNS($G3:G3))*$C$2)),MIN($C$2,L3-(($D$2-COLUMNS($G3:G3))*$B$2)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=IF(COLUMN()=$D$2+COLUMN($G$3)-1,$L3-SUM($G3:G3),IF(COLUMN()>$D$2+COLUMN($G$3)-1,"",RANDBETWEEN(MAX($B$2,$L3-(SUM($G3:G3)+($D$2-COLUMNS($G3:H3))*$C$2)),MIN($C$2,$L3-(SUM($G3:G3)+($D$2-COLUMNS($G3:H3))*$B$2)))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Paste the G3 formula in G3. Then paste the H3 formula in H3, then copy H3 across to K3. Then copy G3:K3 and paste them down the column as far as you need. The #NUM! values in row 6 indicate that there is no possible solution.

As far as the algorithm, here's a quick rundown:

If you are selecting 5 numbers that must add up to a certain value, and you have already picked 4 of them, then the fifth number MUST be the target sum, minus the sum of the other four. Part of the H3 formula does just that, it looks to see if it's in the right column, and if so, does the subtraction.

Now for the previous 4 numbers, we have defined low and high values, B2 and C2. However, the more numbers we pick, the more constraints we have on the maximum and minimum values. For example, look at row 3. The first number picked was 11. Now we want to pick the second number. The total is 34, minus 11, leaves us with 23. Now let's assume that the 3rd, 4th and 5th numbers are all as low as possible, which is 5. 23 - 5 - 5 - 5 = 8. So although the theoretical maximum number for the second number is 25, at this point in the selection process, the actual maximum is 8. We do this using MIN(25, 8). Using a similar process assuming that the 3rd, 4th and 5th numbers are as high as possible (25), we find that the minimum value is MAX(5, -8), or 5. So we do a RANDBETWEEN(5, 8).

And we just repeat. The G3 formula calculates a RANDBETWEEN based on the number of values, the max and min values, and the target number. H3 to J3 perform the same thing, only they include the sum of the previously selected numbers so far. And the K3 formula is just the target minus the sum of the numbers chosen.

In principle, not too hard, but it is kind of tricky to incorporate into formulas. Take a look and let me know if you can't figure out the pieces.

Out of curiosity, where are you located, and what subject are you teaching?

Good luck!

 

[masooque]

Here is a version that works horizontally, with 5 values as in your example:

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M
Mark1	Mark2	Mark3	Mark4	Mark5	Total

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Min[/TD]
[TD="bgcolor: #FAFAFA"]Max[/TD]
[TD="bgcolor: #FAFAFA"]Number of random numbers[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]#NUM![/TD]
[TD="align: right"]23[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet15​

[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"]G3[/TH]
[TD="align: left"]=RANDBETWEEN(MAX($B$2,L3-(($D$2-COLUMNS($G3:G3))*$C$2)),MIN($C$2,L3-(($D$2-COLUMNS($G3:G3))*$B$2)))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"]=IF(COLUMN()=$D$2+COLUMN($G$3)-1,$L3-SUM($G3:G3),IF(COLUMN()>$D$2+COLUMN($G$3)-1,"",RANDBETWEEN(MAX($B$2,$L3-(SUM($G3:G3)+($D$2-COLUMNS($G3:H3))*$C$2)),MIN($C$2,$L3-(SUM($G3:G3)+($D$2-COLUMNS($G3:H3))*$B$2)))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Paste the G3 formula in G3. Then paste the H3 formula in H3, then copy H3 across to K3. Then copy G3:K3 and paste them down the column as far as you need. The #NUM! values in row 6 indicate that there is no possible solution.

As far as the algorithm, here's a quick rundown:

If you are selecting 5 numbers that must add up to a certain value, and you have already picked 4 of them, then the fifth number MUST be the target sum, minus the sum of the other four. Part of the H3 formula does just that, it looks to see if it's in the right column, and if so, does the subtraction.

Now for the previous 4 numbers, we have defined low and high values, B2 and C2. However, the more numbers we pick, the more constraints we have on the maximum and minimum values. For example, look at row 3. The first number picked was 11. Now we want to pick the second number. The total is 34, minus 11, leaves us with 23. Now let's assume that the 3rd, 4th and 5th numbers are all as low as possible, which is 5. 23 - 5 - 5 - 5 = 8. So although the theoretical maximum number for the second number is 25, at this point in the selection process, the actual maximum is 8. We do this using MIN(25, 8). Using a similar process assuming that the 3rd, 4th and 5th numbers are as high as possible (25), we find that the minimum value is MAX(5, -8), or 5. So we do a RANDBETWEEN(5, 8).

And we just repeat. The G3 formula calculates a RANDBETWEEN based on the number of values, the max and min values, and the target number. H3 to J3 perform the same thing, only they include the sum of the previously selected numbers so far. And the K3 formula is just the target minus the sum of the numbers chosen.

In principle, not too hard, but it is kind of tricky to incorporate into formulas. Take a look and let me know if you can't figure out the pieces.

Out of curiosity, where are you located, and what subject are you teaching?

Good luck!

Respected Mr. Eric Sir,
Thanksssss sooo much for your kind help.... Thankssss sooo much...
I'm a high school teacher teaching maths, sci and computer science.... my school located in Ahmedabad, India...
and thanks a lot for algorithm too... i'll quite helpful to me and my students..... I have personel message you in this forum but i think it is not reached to you..
(because it allows only on post for me.... i thik so)..
do you have FB? i really like to add you there..
thankss again
and nice meet you sir

 

[Eric W]

I'm glad I could help. I'm one of the few people left in the world who's not on Facebook. If you have a question, send me a message here and I'll get back to you.