batching volumes / help with IF and or INT?? help?!!

[vpcm24]

I have a series of volumes of vaccine that I need to order regulary ranging from 40ml to 1750ml the vaccines come in bottles of 250ml, 100ml and 50ml.
The quantities are listed in column H3 to H36 and the bottle sizes are listed J2 to L2.
What formula can I put in so that it automatically calculates how many of each bottle I will require? Also taking into consideration that if the quantity is 40ml I will nedd a 50ml bottle or if 80ml I will need a 100ml bottle etc??
Please help!!

 

[konew1]

Assuming J2 has the 250ml size
Put these into J3:L3 and copy down
j3=INT($H3/J$2)
K3=INT(($H3-$J3*$J$2)/K$2)
L3=ROUNDUP(($H3-$J3*$J$2-$K3*K$2)/L$2,0)

You might need provide more info. This solution returns for 210ml returns 2 of 100ml plus 1 of 50ml.
I suspect you would rather get 1 of 250ml.

 

[konew1]

This is better
j3=INT(ROUNDUP($H3/50,0)*50/J$2)
K3=INT((ROUNDUP($H3/50,0)*50-$J3*$J$2)/K$2)
L3=ROUNDUP((ROUNDUP($H3/50,0)*50-$J3*$J$2-$K3*K$2)/L$2,0)

 

[vpcm24]

Wow - that's awesome - thanks SO much for your help, works perfectly! thank you

 

[konew1]

Thanks for the feedback. I just re-read the formula and while it will work it is unnecessarily complex

L3=ROUNDUP(($H3-$J3*$J$2-$K3*K$2)/L$2,0)
That is the one I gave you the first time for L3

Cheers