CODEWITHSUNDEEP
vbaexcelrandom
kdhug886
kdhug886

Reputation: 47

How to generate a random number in Excel VBA

I wanted to write a function generate some numbers which represent

2 = White, 3 = Red, 4 = Green, 5 = Blue, 6 = Yellow

I know that if it is equal probability, then I can write

GenerateColor = Int(Rnd() * 5) + 2

However, I want to generate white half of the time and share the other half with the remaining 4 colors, how can I do? (It means 50% probability is white, 50% of probability is remaining 4 colors.)

Upvotes: 6

Views: 4763

Answers (4)

ian0411
ian0411

Reputation: 4265

You can also use a helper column with cumulative values like this:

The formula you use for result is:

=INDEX($B$2:$B$6,COUNTIF($D$2:$D$6,"<="&RAND())+1)

The formula in column D just a simple SUM function:

=SUM($C$2:C2)

This should also give you the result you are looking for and will be easier to change in the future.

Upvotes: 2

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5185

You can also do this without VBA:

=IF(RAND()<0.5,2,FLOOR(RAND()*4,1)+3)

This gives a 50% chance to return 2, and the other 50% is evenly distributed between 3 and 6.

Upvotes: 4

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

Give this a try:

Sub RanGen()
    With Application.WorksheetFunction
        x = .RandBetween(1, 2)
        If x = 2 Then
            MsgBox x
        Else
            x = .RandBetween(3, 6)
            MsgBox x
        End If
    End With
End Sub

Upvotes: 7

user4039065
user4039065

Reputation:

In a worksheet formula this would be similar to,

=choose(randbetween(1, 2), "white", choose(randbetween(1, 4), "red", "green", "blue", "yellow"))

You can code that in VBA using native VBA functions or simply add worksheetfunction in several places.

Upvotes: 5

Related Questions