Random number generator

[kgss1207]

Hi all,

I want a formula to fill the division of two numbers in specified rows and columns.let's say 3 divided by 2 has to fill in n rows and n columns..it should start from the least number of previous columns and the total sum should not exceed.
1 0
1 1
0 1
1 1
0 1
1 0

 

[Peter_SSs]

Welcome to the MrExcel board!

More than 20 people have looked at your question but nobody has answered. I suspect that is because, like me, they actually have no idea what you are asking.
Perhaps you could try to explain again and maybe give another example or two as well?

 

[kgss1207]

Thank you for the reply.
Let's have a situation 20 dollars has distribute to 3 people for 4 days.then it will be distributed in a way
7 7 6 7
7 6 7 7
6 7 7 6
If you see column total limits to 20.can i get a formula to automatically distribute like above way.inputs will be amount,people,days.

 

[Peter_SSs]

Thanks for the clarification. Here is a macro that i think does what you want.
Initial information is in A1:B3 as shown below and the code has produced what you see in columns D:H

Sub Distribute_Amount()
  Dim orig As Variant, tmp As Variant, result As Variant
  Dim Amt As Double
  Dim People As Long, Days As Long, Ea As Long, Extra As Long, i As Long, j As Long, pos As Long
 
  Amt = Range("B1").Value
  People = Range("B2").Value
  Days = Range("B3").Value
  ReDim orig(0 To People - 1)
  ReDim result(1 To People, 1 To Days)
  Ea = Int(Amt / People)
  Extra = Amt - (Ea * People)
  For i = 1 To People
    orig(i - 1) = Ea + IIf(i <= Extra, 1, 0)
  Next i
  Randomize
  For j = 1 To Days
    tmp = orig
    For i = 1 To People
      pos = Int(Rnd * (UBound(tmp) + 1))
      result(i, j) = tmp(pos)
      tmp(pos) = "x"
      tmp = Filter(tmp, "x", False)
    Next i
  Next j
  Range("D1").Resize(People, Days).Value = result
End Sub

Book1
	A	B	C	D	E	F	G	H	I
1	Amount	20		3	4	3	3	3
2	People	6		3	3	3	3	4
3	Days	5		4	3	4	3	3
4				3	3	3	4	3
5				4	3	4	3	4
6				3	4	3	4	3
7
Sheet1

 

[kgss1207]

Thank you sir..

What i want

 

[Peter_SSs]

Thank you sir..

What i want

You're welcome. Thanks for the confirmation.

 

[kgss1207]

Can we add one more field time band sessions in number but the bifurcation should be happen as below..
20 dollars divided between 3 person for 3days,3 sessions.also columns wise distribution not working correctly.
7
7
6
7
6
7
6
7
7

 

[kgss1207]

You're welcome. Thanks for the confirmation.

Can we add one more field called in time bands in number but the bifurcation should be happen as below..
20 dollars divided between 3 person for 3days,3 sessions.also row wise distribution not working correctly for the above code.
7
7
6
7
6
7
6
7
7

 

[Peter_SSs]

Does that example show what you want for 3 people for 3 days, 3 sessions or does it show 1 person for 1 day 3 sessions or does it show 3 people 1 day 3 sessions or something else?

That is, your requirement is not clear to me.

 

[kgss1207]

3 people,3 days,3 sessions.Bifurcation should happen in such way it should evenly split to the 3 persons for each day and overall.Input amount,should be same for all sessions.