generate 5 random numbers with specific range and whose sum is constant excel

[Decimo]

Hi!
please help me with my problem......

I want to generate 5 random number cells with specific conditions.
1. their sum is base on a particular cell.
2. they have specific range.

like in a1 i encode 150 and a2:a6 will generate a random whole number that will sum to a1
and a2:a6 are also range from 0 to 30 only. only excel formulas no macro or VB

 

[JoeMo]

Don't think you can do that with formulas. Five random whole numbers in the range 0-30 will sum to 150 only if each of the five numbers is 30, which hardly makes them seem random.

 

[paulwarren]

not quite the original request, but generate 4 random numbers A6= A1-Sum(A2:A5) As Joe noted though, your ranges need to be wide enough (0 to A1-4)

 

[petertenthije]

As mentioned, cell A1 contains number 150. In cells A2:A6 you want random numbers that add up to the value in cell A1.

A1 =150
A2 =RANDBETWEEN(0;A1-5)
A3 =RANDBETWEEN(0;$A$1-A2-4)
A4 =RANDBETWEEN(0;$A$1-SUM(A2:A3)-3)
A5 =RANDBETWEEN(0;$A$1-SUM(A2:A4)-2)
A6 =150-SUM(A2:A5)

That does not account for your requirement that the value has to be between 0 and 30. But as JoeMo already mentioned, with that requirement in place every value would be 30.

 

[Decimo]

thank you so much guys! it really helps even if theres a slight correction in my post but seeing the formula replied gives me the idea now

you save the day and my job!
thanks and may God bless you!!

 

[Decimo]

can i ask you guys one more time....

if A1 is a data validation of grade level from 1st grade to 6th grade and A2 is also a data validation of the names of classes base on the grade level show on A1.
like...
A1=3rd grade, then A2 will show data validation of list of names of classes in 3rd grade only. then if A1=6th grade, then A2 will show data validation of names of classes in 6th grade only...

my question is that can i do this on excel only, no macro or vb again?

again....thanks for your understanding regarding my case :D

 

[petertenthije]

That's possible. Before i go answer one question: how is your data sorted? Do you have all names in one column, and their class in the next column? Or one column with names for each class?

Btw, it's getting late so my reply will be tomorrow, assuming noone else already responded.

 

[Decimo]

its like this:
IF A1= 1st grade
then A2=A, B, C, D ,E only

THEN if A1= 2nd grade
then A2 =Apple, mango, orange, banana, only

the first options above in A2 will be change base on A1

and yes the 2 said cells are base on a table list thank you!!!!

 

[Decimo]

i rephrase my problem: lets say that A1:A5 are random numbers range up to 30 only and these cells are sum up on A6 which is <150

so if put 130 on A6, A1:A5 will generate a random value<=30 and when add up it is equals to A6 which is 130 for example..

tnx for the post earlier it gives me other ideas to learn more..... :D

 

[petertenthije]

I am going to assume that you have two worksheets in your workbook. Sheet1 is your analysis that will include the validated drop-down box in cell A2. Sheet2 is the file that contains the source table with names and grades.

Here's the layout needed for your source data in Sheet2. In column B you type the names, and in column C the grades. You do not need to have them sorted in any particular way. In column A you type a formula to identify which of the grades in column C match the value you are looking for ('Sheet1'!$A$2). This formula will assign a unique number to each row that matches the grade you are looking for.
In column D you type a simple VLOOKUP formula to list the values you are looking for. This will be the column that your validation will work on. Make sure that each row with data in column B and C also contains the formulas in column A and column D.

=if($C1='Sheet1'!$A$2;1;0)	A	1st grade	=iferror(vlookup(row();$A:$B;2;false);"")
=if($C2='Sheet1'!$A$2;$C1+1;$C1)	B	1st grade	=iferror(vlookup(row();$A:$B;2;false);"")
=if($C3='Sheet1'!$A$2;$C2+1;$C2)	C	1st grade	=iferror(vlookup(row();$A:$B;2;false);"")
=if($C4='Sheet1'!$A$2;$C3+1;$C3)	D	1st grade	=iferror(vlookup(row();$A:$B;2;false);"")
=if($C5='Sheet1'!$A$2;$C4+1;$C4)	E	1st grade	=iferror(vlookup(row();$A:$B;2;false);"")
=if($C6='Sheet1'!$A$2;$C5+1;$C5)	Apple	2nd grade	=iferror(vlookup(row();$A:$B;2;false);"")
etc	Mango	2nd grade	etc
etc	Orange	2nd grade	etc
etc	Banana	2nd grade	etc

<tbody>
</tbody>

Go to 'Sheet1'!A2 and open the validation field. Select the option "List" and copy the following formula.

=INDIRECT("'Sheet2'!$D$1:$D$"&MAX($A:$A))




That should do it.