have a list of 42 types of product. I know the number of each of those types that exist and the grand total. I want to sample these products pro rata to their numbers, and I want the total number sampled to equal exactly 200. For example if one type of product represented 10% of the total I would want to sample 20 of that type. Types that are few would not be sampled at all.
I have used the formula
to give me a round number to sample (E45 contains the total number of all types).
The problem is that when I sum those rounded numbers to be sampled I end up with 196, not 200. If I use the ROUNDUP function, I end up with more that 200.
Any ideas - I am sure that the answer is simple but not to me!
Type A 24329
Type B 444
Type C 1104
I want to sample exactly 200 out of the 225293 available. I want the number of each type sampled to be pro rata to its occurrence. Thus for Type A the number to sample would be 200*24329/225293
However, that gives a fractional number whereas in reality the number of samples has to be an integer.
For infrequently occurring types (like Type B) I expect these to "drop out" and not be sampled.
I hope that I have explained a little better.