Controlling the SUM of RANDOM numbers
Posted by kobina on November 16, 2001 12:57 PM
Hello Helper,
I have generated random numbers(positive integers) in A1:E4,using the RAND()*10 function.I want to be able control the sum of these random numbers in the ranges.For example,how do I make the random numbers in A1:E1 sum up to 14; A3:E3 sum up to 14; E1:E4 sum up to 14; A1:A4 sum up to 14 and C1:C4 sum up to 14.Can you write the VBA code for me if it's not possible to do it in a regular way?
KOBINA
Posted by Barrie Davidson on November 16, 2001 2:00 PM
Kobina, try this code:
Sub GetRandomNumbers()
Dim RandomTotal As Long
Dim RandomNumber As Long
Range("A1").Select
RandomTotal = 0
Do Until ActiveCell.Row > 3
RandomNumber = Int((9 * Rnd) + 1)
If RandomTotal + RandomNumber > 14 Then RandomNumber = 0
ActiveCell.Value = RandomNumber
RandomTotal = RandomTotal + RandomNumber
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = 14 - RandomTotal
Range("C1").Select
RandomTotal = 0
Do Until ActiveCell.Row > 3
RandomNumber = Int((9 * Rnd) + 1)
If RandomTotal + RandomNumber > 14 Then RandomNumber = 0
ActiveCell.Value = RandomNumber
RandomTotal = RandomTotal + RandomNumber
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Value = 14 - RandomTotal
Range("A1").Select
Do Until ActiveCell.Row > 4
RandomTotal = ActiveCell.Value + ActiveCell.Offset(0, 2).Value
Do Until ActiveCell.Column > 4
RandomNumber = Int((9 * Rnd) + 1)
If RandomTotal + RandomNumber > 14 Then RandomNumber = 0
If ActiveCell = "" Then
ActiveCell.Value = RandomNumber
RandomTotal = RandomTotal + RandomNumber
End If
ActiveCell.Offset(0, 1).Select
Loop
ActiveCell.Value = 14 - RandomTotal
ActiveCell.Offset(1, -4).Select
Loop
End Sub
Hope this helps you out.
BarrieBarrie Davidson
Posted by Jaime on November 16, 2001 7:00 PM
Or u could try this.
Step1.Go to the TOOLS-OPTION_CALCULATION Check the Iterations Check Box and set iterations to 1000.
Step 2.Then type =IF($F1=14,A1,INT(RAND()*11)) this in cell A1 and drag it to E1. In F1 type 14 or any other number you want the rands to add up to.
Repeat step 2 for each row you want.
