Jul 31 2020 10:21 PM - edited Jul 31 2020 10:27 PM
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.
Please suggest.
Thank you in advance!
Jul 31 2020 10:44 PM
can you elaborate more, can you provide an example excel sheet (not the original) but something similar so we can understand your problem better.
Jul 31 2020 11:06 PM
Aug 01 2020 01:41 AM
It is hard to automate without VBA
so please find a simple solution without programming or formulas
Aug 01 2020 01:56 AM
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!
Aug 01 2020 03:49 AM
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...
Aug 01 2020 04:01 AM
Aug 01 2020 04:05 AM
@shekharreddy Happy to help. Glad it met your requirements. Enjoy!
Aug 01 2020 05:39 AM
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
Aug 01 2020 06:16 AM
Aug 01 2020 08:12 AM
@shekharreddy , here you go...
Aug 01 2020 03:49 AM
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...