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

 

 

Highlighted

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
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies