CODEWITHSUNDEEP
excelexcel-formula
noswear
noswear

Reputation: 321

How to unequally distribute random numbers in MS Excel?

Whenever I try to use RANDBETWEEN(Value1,Value2), it almost equally distribute the numbers randomly.
How to generate random number in an unequal manner?
Example -

enter image description here

The above randbetween formula distributed both "Yes" & "No" equally. And I want more of "Yes" than "No"

Upvotes: 0

Views: 2080

Answers (5)

Gary's Student
Gary's Student

Reputation: 96753

To get twice as many "Yes" as "No":

=CHOOSE(RANDBETWEEN(1,3),"Yes","Yes","No")

Upvotes: 1

Plutian
Plutian

Reputation: 2309

You can skew your randbetween values in your favour with the following: =IF(RANDBETWEEN(1,10)>2,"YES","NO") You can change the >2 bit to any number between 1 and 10 to determine how much you want to go either side.

Upvotes: 3

Carl Kirstein
Carl Kirstein

Reputation: 61

Use inverse functions to get different distributions. The function below shows how I implemented multiple inverse functions into one

  • Dist = the distribution type
  • a,b,c = parameters of the distribution like minimum, mode, maximum
  • Prob = rand()

If you pass multiple random values (between 0 and 1) the result from the function will end up with the shape of the distribution you've selected.

Function DistInv(Dist, a, b, c, Prob) As Single
    If Dist = "Single" Then
        ' this is a single value to be used
        DistInv = a

    ElseIf Dist = "Binomial" Then
        ' binomial is like a coin flip. Only has a value of 1 or 0. 'a' determines the cut off point
        If Abs(Prob) > a Then
            DistInv = 0
        Else
            DistInv = 1
        End If

    ElseIf Dist = "Random" Then
        ' uniform distribution between 0% and 100%
        DistInv = Prob

    ElseIf Dist = "Rand Between" Then
        ' uniform distribution between the given parameters
        DistInv = Prob * (b - a) + a

    ElseIf Dist = "Triangular" Then
        ' Triangular distribution with a = lowest value, b = most likely value and c = highest value

        a1 = 1 / ((b - a) * (c - a))
        b1 = -2 * a / ((b - a) * (c - a))
        C1 = a ^ 2 / ((b - a) * (c - a))

        a2 = -1 / ((c - b) * (c - a))
        b2 = 2 * c / ((c - b) * (c - a))
        C2 = ((c - b) * (c - a) - c ^ 2) / ((c - b) * (c - a))

        DistInv = ((-4 * a1 * C1 + 4 * a1 * Prob + b1 ^ 2) ^ (1 / 2) - b1) / (2 * a1)

        If DistInv > b Then
            DistInv = ((-4 * a2 * C2 + 4 * a2 * Prob + b2 ^ 2) ^ (1 / 2) - b2) / (2 * a2)
        End If

    ElseIf Dist = "Norm Between" Then
        ' normal distribution between the given parameters
        DistInv = WorksheetFunction.NormInv(Prob, (a + b) / 2, (b - a) / 3.29)

    ElseIf Dist = "Norm Mean Dev" Then
        ' Normal distribution with the average.norm and standard deviation
        DistInv = WorksheetFunction.NormInv(Prob, a, b)

    ElseIf Dist = "Weibull" Then
        ' Weibull distribution of probability
        '
        ' inverse of Cumulative Weibull Function
        ' for a cumulative Weibull distribution F = 1- exp(-((x-c)/b)^a)
        ' where a is the shape parameter
        '       b is the scale parameter and
        '       c is the offset
        '
        ' then solving for x
        '
        ' x = c + b * (-log(1-Prob))^a

        DistInv = c + b * (-Log(1 - Prob)) ^ (1 / a)

    End If

End Function

Upvotes: 2

David García Bodego
David García Bodego

Reputation: 1090

Try ROUND(RANDBETWEEN(RAND(),2),0)... there will be more values in the 1-2 interval than 0-1

Upvotes: 0

Barry
Barry

Reputation: 63

If you want more Yes than No, make the formula in the Yes cell RANDBETWEEN(Value1,Value2)+RANDBETWEEN(Value3,Value4)

Upvotes: 0

Related Questions