Random value in table with fixed proportion/ratio

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

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.