Random Number from select range

[wayne0881]

Hi All,

I am looking at creating a random number draw with no repeats

in cell A1 i have =RANDBETWEEN(MIN($B$1,$B$2),MAX($B$1,$B$2))
in cell B1 and B2 I have my range. (which for this exercise is between 1-4)

I have a button in C1 which has VBA Application.calculate which basically picks a random between the range.

In C5 i would like another button which will again calculate a random number between the range but omit the value in A1 and return the value in A5 - this will then make sure there are no repeats drawn. (any suggestions on the VBA behind the button for cell C5)

Thanks in advance

 

[Yongle]

The formula in A1 is volatile and will change EVERY time any other cell changes
- I do not think you want a formula in A1

How about

random number in A1

Sub Button1_Click()
    [A1] = Evaluate("RANDBETWEEN(MIN($B$1,$B$2),MAX($B$1,$B$2))")
End Sub

a different random number in A5

Sub Button2_Click()
    Dim v As Integer
    Do
        v = Evaluate("RANDBETWEEN(MIN($B$1,$B$2),MAX($B$1,$B$2))")
        If [A1] <> v Then
            [A5] = v
            Exit Do
        End If
    Loop
End Sub

 

[wayne0881]

The formula in A1 is volatile and will change EVERY time any other cell changes
- I do not think you want a formula in A1

How about

random number in A1

Sub Button1_Click()
    [A1] = Evaluate("RANDBETWEEN(MIN($B$1,$B$2),MAX($B$1,$B$2))")
End Sub

a different random number in A5

Sub Button2_Click()
    Dim v As Integer
    Do
        v = Evaluate("RANDBETWEEN(MIN($B$1,$B$2),MAX($B$1,$B$2))")
        If [A1] <> v Then
            [A5] = v
            Exit Do
        End If
    Loop
End Sub

ok great - I will give this a try

 

[Eric W]

You can leave your A1 formula in if you like, and use this formula in A5:

=AGGREGATE(15,6,ROW(INDIRECT(B1&":"&B2))/(COUNTIF(A1,ROW(INDIRECT(B1&":"&B2)))=0),RANDBETWEEN(1,ABS(B2-B1)-1))

That guarantees a random number between B1 and B2, excluding A1. An extension of that can be used to list all the numbers from B1 to B2 in random order.

 

[wayne0881]

The formula in A1 is volatile and will change EVERY time any other cell changes
- I do not think you want a formula in A1

How about

random number in A1

Sub Button1_Click()
    [A1] = Evaluate("RANDBETWEEN(MIN($B$1,$B$2),MAX($B$1,$B$2))")
End Sub

a different random number in A5

Sub Button2_Click()
    Dim v As Integer
    Do
        v = Evaluate("RANDBETWEEN(MIN($B$1,$B$2),MAX($B$1,$B$2))")
        If [A1] <> v Then
            [A5] = v
            Exit Do
        End If
    Loop
End Sub

If I wanted to add a third button to omit A1 and also A5 in A6 do I just add another if statement underneath?

 

[wayne0881]

You can leave your A1 formula in if you like, and use this formula in A5:

=AGGREGATE(15,6,ROW(INDIRECT(B1&":"&B2))/(COUNTIF(A1,ROW(INDIRECT(B1&":"&B2)))=0),RANDBETWEEN(1,ABS(B2-B1)-1))

That guarantees a random number between B1 and B2, excluding A1. An extension of that can be used to list all the numbers from B1 to B2 in random order.

Thanks I will try this also

 

[Eric W]

I don't know what you have in A2:A4, but if they don't contain numbers in the range defined by B1:B1, this is how to extend the formula. Put this formula in A5:

=AGGREGATE(15,6,ROW(INDIRECT(B$1&":"&B$2))/(COUNTIF($A$1:$A4,ROW(INDIRECT(B$1&":"&B$2)))=0),RANDBETWEEN(1,ABS(B$2-B$1)-ROWS(A$5:A5)+1))

and copy it down the column as far as you want.

 

[wayne0881]

Thanks