Something wrong with my maths

  VoG II 23:49 21 Jul 03
Locked

  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

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

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)

AH

  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

Jazzypop?

  Megatyte 00:04 22 Jul 03

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

AH

  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.

AH

  Megatyte 00:27 22 Jul 03

Ignore that last post. I was wrong :-(

AH

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

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

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

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…