Candies in the jar

[Robert Mika]

Hi All,
I have 3 jars:

The capacity of the first is 20(small) , of the second 48(medium) and the last one 300(large).

How can I split 301, 349 and 369 candies into those jars?
In the first case the formula shoudl say:
1 small and 1 large,
in the second case 1 small, 1 medium, 1 large.
in the last case 2 large.

 

[GlennUK]

At what level of candies do you switch from multiple medium jars to one large jar?

 

[JackDanIce]

Bit confused, you say you have 3 jars (so assume they're of size large, medium and small), but when you have 369 candies, you say they can only fit into 2 large jars. Do you have an unlimited quantity of jars and respective sizes? If so, why not just fit everything into a variable quantity of large sized jars, using the MOD function to calculate how many are required?

 

[gsistek]

Correct me if I'm wrong, but I believe the goal is to minimize "empty space" in the jars. ie. while 368 would fit in two large jars, it would fit in one of each with zero "empty space". Is this along the right lines?

 

[JackDanIce]

368 would, but he says he has 369 candies, so eat 1 candy and then you can maximise space.. otherwise if unlimited number of jars, then 2 large jars would be the solution.

 

[Robert Mika]

Hi All,
Thanks for input

Sorry for a confusion.
There will be no 3 jars but 3 diffrent capacity of jars.

The cut off for medium is 144 which is 3.
From that point till 300 it will be 1 large.
Then from 301 - 1 small 1 large
329 - 1 large ,1 medium

 

[West Man]

Assuming the number of candies is in A3, For large jars in A5, try: =INT(A3/300)
In B5 for med jars try: =INT((A3-(A5*300))/48)
And in C5 for small jars try: =ROUNDUP((A3-(A5*300)-(B5*48))/20,0)

 

[Robert Mika]

Hi West Man,
Thanks for input but I'm getting
1
-1868
and
-4598...
What I'm doing wrong?

 

[West Man]

I have assumed that the number of candies is in A3, the formula for large jars is in A5, for medium jars in B5 and the formula for small jars is in C5.

I may have misunderstood your requirements because my formulas will give for 301 - 1 large and one small; for 349 1 large 1 medium and 1 small. However for 369 it gives 1 large, 1 medium and 2 small.

 

[GlennUK]

My automatic calcs also give 1 large, 1 medium and 2 small ... what rules make it go to 2 large exactly?