randomly select even/odd number from range 1

[skyline666]

hi,

Ive searched on this site and on internet with no luck . The problem I have is that I need to be able to randomly select 3 even and 3 odd numbers from a range. Im using Excels VBA, so im sure this is right place to ask! What I would like is just to be able to use the function in the spreadsheet (e.g: =RandNum(1,45), 1 being lowest number and 45 highest) in 6 cells, to get 3 even numbers and 3 odd numbers, unless it would be easier to have 2 different functions, 1 for even numbers and 1 for odd. I have code to select a random number from the range, but not able to select an even or odd number. Here is the code I have so far:

Public Function RandNum(Lowest As Long, Highest As Long)
' Generates a random whole number within a given range
   Randomize
   
   RandNum = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
End Function

Many thanks in advance,

Andrew

 

[ClulessChris]

Devide your random number by 2 and check for a decimal. if it got one it’s odd if not it’s even.
Randomise again or not as required.

Everybody is somebodys Nutter.

 

[PHV]

A starting point (typed, untested):

Public Function RandOdd(Lowest As Long, Highest As Long)
' Generates a random odd number within a given range
   Dim x
   Randomize
   x = Int(Rnd * (Highest - Lowest)) + Lowest
   RandOdd = x - (0 = (x Mod 2))
End Function
Public Function RandEven(Lowest As Long, Highest As Long)
' Generates a random even number within a given range
   Dim x
   Randomize
   x = Int(Rnd * (Highest - Lowest)) + Lowest
   RandEven = x + (x Mod 2)
End Function

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline666]

Thanks for the quick replies, I tried your code phv but I get a #NAME? error in the cell (for both even and odd). I am reasonably new to programming, which I probably should of said at start, so cant figure out where the error is. Also, what does the "(0 =" bit do in the odd function, I know that the "x Mod 2" is dividing by 2.

 

[PHV]

And you haven't problem with your RandNum function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline666]

Actually I do!, ive been messing around with different things that I did have it working at some stage, but it isn't now, i'll see if I can get the RandNum function working again.

 

[skyline666]

Ok, with macros enabled (using MS Excel 2003), it all works fine, thankyou so much for your help, one thing though what does the "0=" part do in the line:

RandOdd = x - (0 = (x Mod 2))

Many thanks again phv,

Andrew

 

[PHV]

(x Mod 2) equals 0 if x is even
(0 = 0) equals -1 (True)
Finally RandOdd=x+1 when x is even.
Knowing that False equals 0 you'll see that RandOdd=x+0 when x is odd.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline666]

Thanks again PHV, that makes sense now.

Andrew

 

[skyline666]

Hi,

Just as an addition to the code provided mostly provided by PHV, for anyone who is stuck with finding odd and even random numbers between a range, here is the full code that I have now:

Public Function RandomNumber(Lowest As Long, Highest As Long)
'Generates a random whole number within a given range
   
    Randomize
    RandomNumber = Int(Rnd * (Highest + 1 - Lowest)) + Lowest

End Function

Public Function RandOdd(Lowest As Long, Highest As Long)
'Generates a random odd number within a given range

Dim x As Integer
   
    If ((Highest - Lowest = 1) And (Highest Mod 2 = 0)) Then
        RandOdd = Lowest
   
    Else
        Randomize
        x = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
        RandOdd = x - (0 = (x Mod 2))
    End If

End Function

Public Function RandEven(Lowest As Long, Highest As Long)
'Generates a random even number within a given range

Dim x As Integer

    If ((Highest - Lowest = 1) And (Lowest Mod 2 = 0)) Then
        RandEven = Lowest
   
    Else
        Randomize
        x = Int(Rnd * (Highest + 1 - Lowest)) + Lowest
        RandEven = x + (x Mod 2)
    End If

End Function

The first function provides a random number between the range, the second an odd number and 3rd an even number. I made the if then else statements due to when two numbers were next to each other (9 and 10 for example), the RandOdd would return a value of 9 or 11, or for RandEven if 10 and 11 were used, RandEven would return 10 or 12 which is obviously wrong as its out of the range.

So the if statement checks if the two numbers are next to each other, and then checks if the number is even, and for the RandEven function assigns that number to RandEven, and for RandOdd, assigns the other number to RandOdd.

If you are also wondering how to use this in Excel, simply type =RandEven(1,45) (or whichever function/numbers) into a cell. You can specify different numbers in the function, or you could set two cells to these numbers, and then put the cell references for them into the function (for example you put 1 in B1 and 45 in C1, the function would read =RandEven(B1,C1) in cell A1)

Andrew