optimise use of different pack size

[poonw]

Can excel optimise the use of different pack size available to fulfill an order.

e.g. An order requires minimum 120 units of a product while pack size of the product available is 50 units and 20units. obviously the most economical way is to use 6 x 20units to fulfill the order. Can excel understand this logic and don't output use of 3 x 50units?

Thanks.

[XOR LX]

Hi,

For the example you give, why is 6 x 20units more 'economical' than (2 x 50units) + (1 x 20units)? Or aren't you allowed to mix pack sizes?

Regards

[poonw]

Hi, this is for pharmaceutical manufacturing so it's good practice to limit raw materials to one pack size. of course if stock is not available then in that example 2 x 50units + 1 x 20units is also a viable option.

The thing is how can I ask Excel to output these usage figures for me.

Thanks

[XOR LX]

Thanks. And can the order quantity always be precisely met by some combination of the available pack sizes? Or can there be cases for which the order quantity can't be satisfied precisely, for example an order of 275 units given the two pack sizes you give?

It might be helpful if you supplied more examples together with expected results.

Regards

[poonw]

Hi, just updated the post with example spreadsheet. In there you will see the expected total number of individual pack sizes that I would like Excel to output.

Thanks.

[XOR LX]

Thanks a lot.

I removed the "mg" part from the entries in C2 and D2, just to make the formula-work less complex.

In C3:

=MOD(MATCH(1,FREQUENCY(0,{0,1,2,3,4,5,6,7,8,9}*C$2+{0;1;2;3;4;5;6;7;8;9}*D$2-$B3),0)-1,10)

In D3:

=INT((MATCH(1,FREQUENCY(0,{0,1,2,3,4,5,6,7,8,9}*C$2+{0;1;2;3;4;5;6;7;8;9}*D$2-$B3),0)-1)/10)

Both copied down.

The above assume a maximum of 9 of each pack size per order. If you don't think this will be sufficient let me know a feasible upper limit and I'll provide suitably updated versions of the above.

Regards

[poonw]

Thanks. that's exactly what I need.

If in the future I would like to increase the upper limit do I simply keep adding ,10,11,12 ... to the formula?