Random value in table with fixed proportion/ratio

[brooij_bdr]

Hi,

I am making a pivot table aka database in excel. I have a column to fill in randomly with the values "Yes" or "No". I do know how to fill in random values, however I need a fixed proportion/ratio being 40% "Yes" and 60% "No". So in my database with 1000 records I need 400 saying "Yes" and 600 saying "No". But I do not want to give the first 400 a "Yes" and the remaining 600 a "No", I want them randomly.

Is there any way to do this?

Thanks in advance!

[#2]

@brooij_bdr 

You may try something like this and tweak it if required.

Sub RandomYesNo()
Dim y()         As Variant
Dim No          As Object
Dim it          As Object
Dim i           As Long
Dim lr          As Long
Dim NoCount     As Long

'Defining the No Ratio %, change it if required
Const ratioNo   As Double = 0.6

'Finding last row with data in the data set
'The below line finds the last row with data in column A
lr = Cells(Rows.Count, 1).End(xlUp).Row

NoCount = Round((lr - 1) * ratioNo, 0)

ReDim y(1 To lr - 1, 1 To 1)

Set No = CreateObject("Scripting.Dictionary")

Do While No.Count < NoCount
    i = WorksheetFunction.RandBetween(2, lr)
    If Not No.exists(i) Then
        No(i) = ""
        y(i - 1, 1) = "No"
    End If
Loop

For i = 1 To UBound(y, 1)
    If y(i, 1) = "" Then y(i, 1) = "Yes"
Next i

'Writing the Yes/No in the column B, change it if required
Range("B2").Resize(UBound(y, 1)).Value = y
End Sub

In the attached, click the button called "Generate Random Yes/No" on Sheet1 to run the code and it will generate Random Yes/No in column B.

[#3]

Hi @brooij_bdr 

If you want a solution of your requirement using excel functions, you can use attached file that will always generate desired proportion of 'Yes/No' in different positions. 

Thanks

Tauqeer