SOLVED

Sampling category wise percent

%3CLINGO-SUB%20id%3D%22lingo-sub-1559984%22%20slang%3D%22en-US%22%3ESampling%20category%20wise%20percent%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559984%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20few%20comments%20in%20my%20work%20and%20I%20need%20to%20take%20samples%20from%20those%20comments%20percentage%20wise.%3C%2FP%3E%3CP%3EExample%3A%20comments%3A%201.%20New%20Company%2C%202.%20Updated%20File%2C%203.%20Checked.%204.%20All%20Good%3C%2FP%3E%3CP%3EThe%20total%20comments%20are%201000(%20NOT%201000%2F4%20it%20may%20be%20any%20number).%20Now%20I%20want%20percentage%20wise%20samples%20like%201.%20New%20Company%20(30%25)%2C%202.%20Updated%20File%2C(20%25)%203.%20Checked(35%25).%204.%20All%20Good(15%25)%20total%20should%20be%20100%25.%20let's%20say%20New%20company's%20are%20100%20and%20i%20want%2030%25%20means%2030%20records%20so%20sample%20must%20contain%2030%20records%20from%20New%20company%20remaining%20also%20same.%3C%2FP%3E%3CP%3EI%20tried%20Data%20analysis-%26gt%3B%20sampling%20%2C%20Random%20number%20generation%20and%20Rand%20in%20excel%3C%2FP%3E%3CP%3EI%20am%20getting%20random%20samples%20but%20not%20in%20required%20percentage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20suggest.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1559984%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1559998%22%20slang%3D%22en-US%22%3ERe%3A%20Sampling%20category%20wise%20percent%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559998%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F605555%22%20target%3D%22_blank%22%3E%40shekharreddy%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecan%20you%20elaborate%20more%2C%20can%20you%20provide%20an%20example%20excel%20sheet%20(not%20the%20original)%20but%20something%20similar%20so%20we%20can%20understand%20your%20problem%20better.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello,

I have few comments in my work and I need to take samples from those comments percentage wise.

Example: comments: 1. New Company, 2. Updated File, 3. Checked. 4. All Good

The total comments are 1000( NOT 1000/4 it may be any number). Now I want percentage wise samples like 1. New Company (30%), 2. Updated File,(20%) 3. Checked(35%). 4. All Good(15%) total should be 100%. let's say New company's are 100 and i want 30% means 30 records so sample must contain 30 records from New company remaining also same.

I tried Data analysis-> sampling , Random number generation and Rand in excel

I am getting random samples but not in required percentage.

@mathetes 

@peteryac60 

 

Please suggest.

 

Thank you in advance!

 

 

 

 

 

11 Replies

@shekharreddy 

can you elaborate more, can you provide an example excel sheet (not the original) but something similar so we can understand your problem better.

Hello@Ramiz_Assaf

 

Please find that attached. I hope it will helpful to you !

 

Thank you!

@shekharreddy 

 

It is hard to automate without VBA

so please find a simple solution without programming or formulas

 

Thank you for the video@Ramiz_Assaf 

 

it included some manual work so could you please write a VBA code for this one.

 

Thank you in advance!

Best Response confirmed by shekharreddy (Occasional Contributor)
Solution

@shekharreddy , VBA not necessary. See attached with both the new Dynamic Array and old Legacy formula options.

 

Also, your Sample counts don't evaluate to whole numbers, so I had to round them. This makes the sample size 22 instead of 23. FYI...

you are Awesome!@TheAntony 

 

Thank you so much. This woks!

 

@shekharreddy Happy to help. Glad it met your requirements. Enjoy!

@shekharreddy 

I thought you needed the serial number from the original list. That is why I proposed my solution.

You needed something simpler.

 

Best of luck

 

Yeah, You are correct. I just realized.. I'm sorry it's my mistake.. could you please help me to get serial number from original series.. then only the sample works..

@shekharreddy , here you go...

This is perfect now- Thanks a lot!!!