Conversation Re: Random value in table with fixed proportion/ratio in Excel
https://techcommunity.microsoft.com/t5/excel/random-value-in-table-with-fixed-proportion-ratio/m-p/890732#M41297
<P><LI-USER uid="416239"></LI-USER> </P><P>You may try something like this and tweak it if required.</P><P> </P><LI-CODE lang="markup">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</LI-CODE><P> </P><P>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.</P><P> </P><P> </P>Thu, 03 Oct 2019 08:52:23 GMTSubodh_Tiwari_sktneer2019-10-03T08:52:23ZRandom value in table with fixed proportion/ratio
https://techcommunity.microsoft.com/t5/excel/random-value-in-table-with-fixed-proportion-ratio/m-p/890561#M41294
<P>Hi,</P><P> </P><P>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.</P><P> </P><P>Is there any way to do this?</P><P> </P><P>Thanks in advance!</P>Thu, 03 Oct 2019 07:33:18 GMThttps://techcommunity.microsoft.com/t5/excel/random-value-in-table-with-fixed-proportion-ratio/m-p/890561#M41294brooij_bdr2019-10-03T07:33:18ZRe: Random value in table with fixed proportion/ratio
https://techcommunity.microsoft.com/t5/excel/random-value-in-table-with-fixed-proportion-ratio/m-p/890732#M41297
<P><LI-USER uid="416239"></LI-USER> </P><P>You may try something like this and tweak it if required.</P><P> </P><LI-CODE lang="markup">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</LI-CODE><P> </P><P>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.</P><P> </P><P> </P>Thu, 03 Oct 2019 08:52:23 GMThttps://techcommunity.microsoft.com/t5/excel/random-value-in-table-with-fixed-proportion-ratio/m-p/890732#M41297Subodh_Tiwari_sktneer2019-10-03T08:52:23ZRe: Random value in table with fixed proportion/ratio
https://techcommunity.microsoft.com/t5/excel/random-value-in-table-with-fixed-proportion-ratio/m-p/890966#M41306
<P>Hi <LI-USER uid="416239"></LI-USER> </P><P> </P><P>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. </P><P> </P><P>Thanks</P><P>Tauqeer</P>Thu, 03 Oct 2019 11:51:14 GMThttps://techcommunity.microsoft.com/t5/excel/random-value-in-table-with-fixed-proportion-ratio/m-p/890966#M41306tauqeeracma2019-10-03T11:51:14Z