• 667K Members
• 9,986 Online
• 822K Conversations

Highlighted
New Contributor

# Random value in table with fixed proportion/ratio

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?

2 Replies

# Re: Random value in table with fixed proportion/ratio

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.

Highlighted

# Re: Random value in table with fixed proportion/ratio

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