Excel function to divide a whole number into whole parts, according to a multi-part ratio

Question

Suppose I have a number of objects N, and I want to separate them into three or more buckets according to a known ratio, let's say x : y : z.

The value N does not necessarily divide perfectly along this ratio (x+y+z does not necessarily go into N); but two things are important:

• having the ratio that actually ends up in the buckets as close to x:y:z as possible, and also
• making sure that the buckets end up containing numbers that add up correctly to N

And I'd like to write an excel function to do this.

I could probably write an iterative process that puts balls into buckets one-at-a-time, using the bucket that will get the result as close to the real ratio as possible, but I'd like to know if there's a direct calculation I could use for this.

Any ideas? :)

Answer 1

First thing is you should divide $x,y,z$ by their greatest common divisor.

You could put $x \left\lfloor \frac{N}{x+y+z} \right\rfloor$ items in the first bucket, $y\left\lfloor \frac{N}{x+y+z} \right\rfloor$ items in the second bucket, and $z \left\lfloor \frac{N}{x+y+z} \right\rfloor$ items in the third bucket.

After that, you have less than $x+y+z$ items remaining to distribute which is the tricky part. Do you have any ideas about how you would want to define how two three-way ratios are "close" to one another?

Answer 2

Roughly you can use

=ROUND(N*x/(x+y+z),0)
=ROUND(N*y/(x+y+z),0)
=ROUND(N*z/(x+y+z),0)

This may not be the absolute best you can do, but it's fairly simple.

In some cases like splitting $100$ evenly into thirds this won't add up the total since it will return $33, 33, 33$. To remedy this, you can make your last one your plug:

=N-ROUND(N*x/(x+y+z),0)-ROUND(N*y/(x+y+z),0)

Using this, I don't think you should never be off by more than one object from the optimal solution.