Display random numbers whose sum equal to specified number

[mabubakerali]

Hi, i was having an issue related to my job so i just explain that straight,
I have written a number in cell a1 lets say its 200000, now i want excel to display random numbers in cell b1,b5,b10 and the sum of them must be equal to the number specified in a1 which is 200000..

Is there any way to do that since the workload is big and it would be of great help

 

[AlanY]

try

Book1
	A	B
1	200000	6191
2
3
4
5		88256
6
7
8
9	check
10	200000	105553
Sheet2

Cell Formulas
Range	Formula
B1	=RANDBETWEEN(1,A1-3)
B5	=RANDBETWEEN(1,(A1-B1)-2)
B10	=A1-B1-B5

 

[Joe4]

Here is one way:

In B1, enter this formula:
=RANDBETWEEN(0,A1)

In B5, enter this formula:
=RANDBETWEEN(0,A1-B1)

In B10, enter this formula:
=A1-B1-B5


EDIT: Doh! A little too slow, as Alan and I had the same idea, but he beat me to it.
Note that since you want it to sum up to exactly a specific value, only the first two numbers are random, as the third must be the difference to add up to your value.

 

[mabubakerali]

Thanks man, i appreciate your help

try

	A	B
check

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

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

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

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

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

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

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

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

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

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

[TD="align: center"]10[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]105553[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=RANDBETWEEN(1,A1-3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B5[/TH]
[TD="align: left"]=RANDBETWEEN(1,(A1-B1)-2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B10[/TH]
[TD="align: left"]=A1-B1-B5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mabubakerali]

Thanks bro, you really helped me..