Something wrong with my maths

  VoG II 23:49 21 Jul 03

  VoG II 23:51 21 Jul 03

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 03

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

  VoG II 23:55 21 Jul 03

Sleep well, my friend.

  Megatyte 23:57 21 Jul 03

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


  VoG II 00:00 22 Jul 03

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 03


  Megatyte 00:04 22 Jul 03

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


  VoG II 00:07 22 Jul 03

Nope sorry that doesn't work.

This is a difficult q

  Megatyte 00:24 22 Jul 03

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


  Megatyte 00:27 22 Jul 03

Ignore that last post. I was wrong :-(


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

Huawei P10 review

1995-2015: How technology has changed the world in 20 years

How VR is being used to simulate space

New iPad, iPhone SE & Red iPhone 7 on sale now