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]

I don't understand. Here is one way to interpret, people, days and sessions. But that only covers one day whereas you said the example covered 3 people for 3 days and 3 sessions. Can you clarify?

Book1
	A	B
1	7	Day 1, person 1, session 1
2	7	Day 1, person 2, session 1
3	6	Day 1, person 3, session 1
4	7	Day 1, person 1, session 2
5	6	Day 1, person 2, session 2
6	7	Day 1, person 3, session 2
7	6	Day 1, person 1, session 3
8	7	Day 1, person 2, session 3
9	7	Day 1, person 3, session 3
Sheet2

Perhaps give 2 more examples of expected results for these, with explanation of the logic for the results?

Amount 15, 2 people, 4 days, 3 sessions

Amount 20, 3 people, 5 days, 2 sessions

 

[kgss1207]

I don't understand. Here is one way to interpret, people, days and sessions. But that only covers one day whereas you said the example covered 3 people for 3 days and 3 sessions. Can you clarify?

Book1
	A	B
1	7	Day 1, person 1, session 1
2	7	Day 1, person 2, session 1
3	6	Day 1, person 3, session 1
4	7	Day 1, person 1, session 2
5	6	Day 1, person 2, session 2
6	7	Day 1, person 3, session 2
7	6	Day 1, person 1, session 3
8	7	Day 1, person 2, session 3
9	7	Day 1, person 3, session 3
Sheet2

Perhaps give 2 more examples of expected results for these, with explanation of the logic for the results?

Amount 15, 2 people, 4 days, 3 sessions

Amount 20, 3 people, 5 days, 2 sessions

 

[kgss1207]

Please find below the same eg :2
P1S1 7 6 7 7 6
P2S1 7 7 6 7 7
P3S1 6 7 7 6 7
P1S1 7 7 6 7 7
P2S1 6 7 7 6 7
P3S1 7 6 7 7 6

 

[kgss1207]

Please find below the same eg :2
P1S1 7 6 7 7 6
P2S1 7 7 6 7 7
P3S1 6 7 7 6 7
P1S1 7 7 6 7 7
P2S1 6 7 7 6 7
P3S1 7 6 7 7 6

Example 1 working
P1S1 8 7 8 7
P2S1 7 8 7 8
P1S2 7 8 7 8
P2S2 8 7 8 7
P1S3 8 7 8 7
P2S3 7 8 7 8

 

[Peter_SSs]

Sub Distribute_Amount_v2()
  Dim orig As Variant, tmp As Variant, result As Variant
  Dim Amt As Double
  Dim People As Long, Days As Long, Sessions As Long, Ea As Long, Extra As Long, i As Long, j As Long, k As Long, pos As Long
 
  Amt = Range("B1").Value
  People = Range("B2").Value
  Days = Range("B3").Value
  Sessions = Range("B4").Value
  ReDim orig(0 To People - 1)
  ReDim result(1 To People * Sessions, 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
    For k = 1 To Sessions
      tmp = orig
      For i = 1 To People
        pos = Int(Rnd * (UBound(tmp) + 1))
        result((k - 1) * People + i, j) = tmp(pos)
        tmp(pos) = "x"
        tmp = Filter(tmp, "x", False)
      Next i
    Next k
  Next j
  Range("D1").Resize(People * Sessions, Days).Value = result
End Sub

 

[kgss1207]

Sir, Expected result that person get least value should get maximum in the next session.the above code gives the result as below for 2 people,3 days,2 sessions and the amount is 3.
2 1 1
1 2 2
2 2 2
1 1 1

It should be evenly distribute between them in the end.result should come as below
2 2 2
1 1 1
1 1 1
2 2 2
At last both of them get 9 each.same way it as bifurcated for different scenarios.
Can i get this through VBA.

 

[Peter_SSs]

If results are random as you requested, then there is no guarantee that results will turn out exactly even.
For example, if you toss a coin then there is a 50% chance of a 'head' and 50% chance of a 'tail'.
However, if you toss a coin ten times there is no guarantee that you will end up wit 5 'heads' and 5 'tails'

I don't think that I can do any better than what I have already done.