Solved

# Random Number Generator with Conditions

Posted on 2011-10-18
279 Views
EE Professionals,

I'm creating a demonstration macro of a model where I need to generate two columns of numbers randomly but with certain conditions.  Each column contains 15 cells (15 rows), that need to be populated when the subroutine is run.  The numbers in the first column must fall between 1 and 10 and always be less then the numbers in the second column. So in Column 1 you may have a randomly generated 6 which means in Column 2 the number would have to be 6, 7, 8, 9 or 10.

B.
0
Question by:Bright01

LVL 37

Expert Comment

Could the second column not just have

=(your current formula) +A1  ( Assumes this is in B1, adjust accordingly)

Added to the end of the formula? Therefore just adding the value in the first column onto the random generated value, thus is ALWAYS greater than the value in the first column?
0

LVL 37

Expert Comment

OR

calculate value for A1, enter it. Then have B1...

=RANDBETWEEN(A1,uppervalue)
0

LVL 59

Accepted Solution

See the following ... I have assumed the upper limit for column B, (intHigher) as 20 ... it MUST be different to the 1 ... 10 limitation on column A as column B is always higher but tweak if necessary within the premise that inthigher is always greater than inthigh

Outputs are made to the active sheet as it stands.

Chris
``````Sub twoRand()
Dim num As Integer
Dim intLow As Integer
Dim intHigh As Integer
Dim intHigher As Integer
Dim arr(1 To 15, 1 To 2) As Integer
Dim itm As Integer

Randomize
intLow = 1
intHigh = 10
intHigher = 20
For itm = 1 To 15
arr(itm, 1) = Int((intHigh - intLow + 1) * Rnd + intLow)
arr(itm, 2) = Int((intHigher - arr(itm, 1) + 2) * Rnd + arr(itm, 1) + 1)
Next
ActiveSheet.Range("a1").Resize(15, 2) = arr

End Sub
``````
0

Author Comment

Neilsr,

Thanks for the note, but I don't follow your logic.  I have no formula; I'm looking for a macro and a formula to drive the random sequencing.

Chris,

I used your formula and it works; however, when I generate the random list, it gives me values in column B that are greater then 10.  Here is the code I modified.  Can you see how I can limit and force a random sample higher then the values in Col. A but = or less then 10?

Thanks,

B.

Sub twoRand()
Dim num As Integer
Dim intLow As Integer
Dim intHigh As Integer
Dim intHigher As Integer
Dim arr(1 To 15, 1 To 2) As Integer
Dim itm As Integer

Randomize
intLow = 1
intHigh = 10
intHigher = 10
For itm = 1 To 10
arr(itm, 1) = Int((intHigh - intLow + 1) * Rnd + intLow)
arr(itm, 2) = Int((intHigher - arr(itm, 1) + 2) * Rnd + arr(itm, 1) + 1)
Next
ActiveSheet.Range("a1").Resize(15, 2) = arr

End Sub
0

Author Closing Comment

Chris,

I got it to work by playing around with  it.  Much thanks for the assistance on it.  Works great!

B.
0

LVL 59

Expert Comment

For info you fell foul of your description.  Your latter comment indicates that column B is also limited to 10 whereas the description stated column B is always greater than column A.

Since column A as stated ranges from 1 to 10 then it followed that column B would be greater than 10!  Assuming that column B is greater than or equal to column A then the line changes from:

arr(itm, 2) = Int((intHigher - arr(itm, 1) + 2) * Rnd + arr(itm, 1) + 1)
to

arr(itm, 2) = Int((intHigher - arr(itm, 1) + 1) * Rnd + arr(itm, 1))

With inthigher refined as 10, making the easiest edit.

Chris

Chris
0

Author Comment

Chris,

Not sure I understand your comment.  In my original description I said,  "The numbers in the first column must fall between 1 and 10 and always be less then the numbers in the second column."  This would mean that the highest number in column 1 could only be 9 leaving room for 10 in column 2 and satisfying the description.  Perhaps I didn't word it well.  My bad.

Anyway, thanks to your efforts, it works and works well.

Again, much thanks.

B.
0

LVL 59

Expert Comment

Happy to accept I misunderstood I took 1 to 10 to be the range 1 ... 10 whereas it sounds as though maybe it should have been 2 ... 9, often clarity of function is the hardest thing to impart.

I did see the potential issue at the outset so when I made my first post I qualified my assumption about column 1 being 1 ... 10 ... nonetheless whichever route you went to modify the code to your needs - well done for successfully making the change.

Chris
0

## Featured Post

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.