Something wrong with my maths

  VoG II 23:49 21 Jul 2003

  VoG II 23:51 21 Jul 2003

In Excel

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


TOTAL 225293

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.

  woodchip 23:53 21 Jul 2003

That's too much for my brain, of to bed you have killed me

  VoG II 23:55 21 Jul 2003

Sleep well, my friend.

  Megatyte 23:57 21 Jul 2003

Try =INT(200*E2/$E$45,0)


  VoG II 00:00 22 Jul 2003

Thank you. Should have said have already tried that. Same result. It must be an array formula or something but I just cannot get my head round it.

  powerless 00:02 22 Jul 2003


  Megatyte 00:04 22 Jul 2003

How about =(200*E2/$E$45,0) and then rounding the total?


  VoG II 00:07 22 Jul 2003

Nope sorry that doesn't work.

This is a difficult q

  Megatyte 00:24 22 Jul 2003

The problem arises when one(or more) of them returns a zero.


  Megatyte 00:27 22 Jul 2003

Ignore that last post. I was wrong :-(


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?