SOLVED

# Sampling category wise percent

Occasional Contributor

# Sampling category wise percent

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.

11 Replies

# Re: Sampling category wise percent

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

# Re: Sampling category wise percent

Hello@Ramiz_Assaf

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

Thank you!

# Re: Sampling category wise percent

It is hard to automate without VBA

so please find a simple solution without programming or formulas

# Re: Sampling category wise percent

Thank you for the video@Ramiz_Assaf

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

Best Response confirmed by shekharreddy (Occasional Contributor)
Solution

# Re: Sampling category wise percent

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

# Re: Sampling category wise percent

you are Awesome!@TheAntony

Thank you so much. This woks!

# Re: Sampling category wise percent

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

# Re: Sampling category wise percent

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

# Re: Sampling category wise percent

@shekharreddy , here you go...

# Re: Sampling category wise percent

This is perfect now- Thanks a lot!!!