formula packaging optimisation

Occasional Visitor



I have a project to optimize the calculation of the necessary packaging product A is made available in 1000kg, 200kg, 2.5kg


If I have 1346kg of product for sale I want to optimize the packaging (1x1000kg + 1x200 + 59x2.5kg)=> total 1347.5kg


My formula is as follows I type my number of kg in box B2 say 1346


A5: 1000 B5: =INT(C3/A5) ===>1

A6: 200 B6: =INT(MOD(C3,A5)/A6) ===>1

A7: 2.5 B7: =IF(INT(MOD(MOD(C3,A5),A6)/A7)>=0,INT(MOD(MOD(C3,A5),A6)/A7)+1,INT(MOD (MOD(C3,A5),A6)/A7)) ===>59

now we have just integrated a new packaging of 50kg that I have to place between 200kg and 2.5kg and .... it becomes a hassle for me for the formula.


It may not look classy but could a good soul please help me on this point?


Thanks in advance

1 Reply

@yan1983 I don't think you need all these complicated formulae. Please have a look and see if this works for you. The formula in B5 was copied down to B8. Though, the one in B8 needed to be changed to use ROUNDUP rather than INT. Easy to adjust if you need more layers in between.