SOLVED

Sampling category wise percent

Copper 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 (Copper 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!!!
1 best response

Accepted Solutions
best response confirmed by shekharreddy (Copper 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...

View solution in original post