Home

Random value in table with fixed proportion/ratio

%3CLINGO-SUB%20id%3D%22lingo-sub-890561%22%20slang%3D%22en-US%22%3ERandom%20value%20in%20table%20with%20fixed%20proportion%2Fratio%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-890561%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20making%20a%20pivot%20table%20aka%20database%20in%20excel.%20I%20have%20a%20column%20to%20fill%20in%20randomly%20with%20the%20values%20%22Yes%22%20or%20%22No%22.%20I%20do%20know%20how%20to%20fill%20in%20random%20values%2C%20however%20I%20need%20a%20fixed%20proportion%2Fratio%20being%2040%25%20%22Yes%22%20and%2060%25%20%22No%22.%20So%20in%20my%20database%20with%201000%20records%20I%20need%20400%20saying%20%22Yes%22%20and%20600%20saying%20%22No%22.%20But%20I%20do%20not%20want%20to%20give%20the%20first%20400%20a%20%22Yes%22%20and%20the%20remaining%20600%20a%20%22No%22%2C%20I%20want%20them%20randomly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-890561%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-890732%22%20slang%3D%22en-US%22%3ERe%3A%20Random%20value%20in%20table%20with%20fixed%20proportion%2Fratio%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-890732%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416239%22%20target%3D%22_blank%22%3E%40brooij_bdr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20may%20try%20something%20like%20this%20and%20tweak%20it%20if%20required.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20RandomYesNo()%0ADim%20y()%20%20%20%20%20%20%20%20%20As%20Variant%0ADim%20No%20%20%20%20%20%20%20%20%20%20As%20Object%0ADim%20it%20%20%20%20%20%20%20%20%20%20As%20Object%0ADim%20i%20%20%20%20%20%20%20%20%20%20%20As%20Long%0ADim%20lr%20%20%20%20%20%20%20%20%20%20As%20Long%0ADim%20NoCount%20%20%20%20%20As%20Long%0A%0A'Defining%20the%20No%20Ratio%20%25%2C%20change%20it%20if%20required%0AConst%20ratioNo%20%20%20As%20Double%20%3D%200.6%0A%0A'Finding%20last%20row%20with%20data%20in%20the%20data%20set%0A'The%20below%20line%20finds%20the%20last%20row%20with%20data%20in%20column%20A%0Alr%20%3D%20Cells(Rows.Count%2C%201).End(xlUp).Row%0A%0ANoCount%20%3D%20Round((lr%20-%201)%20*%20ratioNo%2C%200)%0A%0AReDim%20y(1%20To%20lr%20-%201%2C%201%20To%201)%0A%0ASet%20No%20%3D%20CreateObject(%22Scripting.Dictionary%22)%0A%0ADo%20While%20No.Count%20%26lt%3B%20NoCount%0A%20%20%20%20i%20%3D%20WorksheetFunction.RandBetween(2%2C%20lr)%0A%20%20%20%20If%20Not%20No.exists(i)%20Then%0A%20%20%20%20%20%20%20%20No(i)%20%3D%20%22%22%0A%20%20%20%20%20%20%20%20y(i%20-%201%2C%201)%20%3D%20%22No%22%0A%20%20%20%20End%20If%0ALoop%0A%0AFor%20i%20%3D%201%20To%20UBound(y%2C%201)%0A%20%20%20%20If%20y(i%2C%201)%20%3D%20%22%22%20Then%20y(i%2C%201)%20%3D%20%22Yes%22%0ANext%20i%0A%0A'Writing%20the%20Yes%2FNo%20in%20the%20column%20B%2C%20change%20it%20if%20required%0ARange(%22B2%22).Resize(UBound(y%2C%201)).Value%20%3D%20y%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%2C%20click%20the%20button%20called%20%22Generate%20Random%20Yes%2FNo%22%20on%20Sheet1%20to%20run%20the%20code%20and%20it%20will%20generate%20Random%20Yes%2FNo%20in%20column%20B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-890966%22%20slang%3D%22en-US%22%3ERe%3A%20Random%20value%20in%20table%20with%20fixed%20proportion%2Fratio%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-890966%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F416239%22%20target%3D%22_blank%22%3E%40brooij_bdr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20a%20solution%20of%20your%20requirement%20using%20excel%20functions%2C%20you%20can%20use%20attached%20file%20that%20will%20always%20generate%20desired%20proportion%20of%20'Yes%2FNo'%20in%20different%20positions.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
brooij_bdr
New Contributor

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 Replies

@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.

 

 

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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
33 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies