RANDBETWEEN() excluding values

[Jon von der Heyden]

Hi,

Does anybody know how to do a RANDBETWEEN(1,100) but have it exclude numbers that already exist within a defined range?

Thanks,
Jon

 

[Damon Ostrander]

Hi noj,

I don't know of any easy way to do this using RANDBETWEEN, so here is a custom user-defined function to make it easy:

Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude As Range) As Long
Dim R As Long
Dim C As Range
Do
R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))
For Each C In Exclude
If R = C Then Exit For
Next C
Loop Until C Is Nothing

RandBetweenInt = R
Application.Volatile
End Function


To use this function, enter the cell formula

=RandBetweenInt(1,100,C1:C20)

where C1:C20 is the range containing the numbers you want to exclude.

To install this UDF in your workbook, go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM) and paste this code into the Code pane.

Damon

 

[lhousesoccer]

Hi - I want to do a similar thing, but the numbers I want to exclude are not within a set range, but rather determined by a previous column of randomly generated numbers.

I want to generate 2 random numbers bewteen 1 and 5, with 13 repetitions. The seond number can't be the as the first.

So in Column A1 I would put =randbetween(1,5). In B1, I want to generate another random number between 1 and 5, but exclude the result in A1.

Basically, I'm trying to randomly pick TWO days of the week, weekdays only Mon-Fri. I want to do this for 13 weeks.

Any ideas?

thanks!

 

[barry houdini]

With =RANDBETWEEN(1,5) in A1 use this formula in B1

=SMALL(IF(A1={1,2,3,4,5},"",{1,2,3,4,5}),RANDBETWEEN(1,4))

 

[lhousesoccer]

Excellent Barry! It works perfectly. I can't pretend to understand the formula's components in B1, but it worked and did not replicate any numbers generated in A. Thanks!

 

[barry houdini]

Whichever number you have in A1 the IF function generates an array which contains the other 4, so if A1 = 2 this part

IF(A1={1,2,3,4,5},"",{1,2,3,4,5})

generates this array {1,"",3,4,5}

Then SMALL gives you the nth smallest out of that array [where n is generated by RANDBETWEEN(1,4) ]

SMALL could be replaced by LARGE, it makes no difference which of those you use....

 

[lhousesoccer]

Thanks for the explanation. Makes much more sense now!

 

[sheetspread]

With =RANDBETWEEN(1,5) in A1 use this formula in B1

=SMALL(IF(A1={1,2,3,4,5},"",{1,2,3,4,5}),RANDBETWEEN(1,4))

Does that mean combining the A1 formula with B1's in a single cell is not possible?

 

[barry houdini]

Does that mean combining the A1 formula with B1's in a single cell is not possible?

Not sure what you mean - can you clarify?

 

[sheetspread]

Actually you can, I should have tested it before asking (and read lhousesoccer's #3 post more carefully):

=SMALL(IF(RANDBETWEEN(1,5)={1,2,3,4,5},"",{1,2,3,4,5}),RANDBETWEEN(1,3))

works fine. He wanted to show the extra column.

I was looking for a single randbetween type formula that excluded numbers, but this one seems to do it:

e.g.

=CHOOSE(INT(RAND()*7)+1,1,3,4,6,8,9,10)

for 1 to 10 excluding 2,5,7