Abstract

Suppose you have a supplier who will only sell items in certain quantities to make shipping manageable. So you have to buy a full truck load of items to begin with, and after that anywhere between a half and full load.

A full load is 10 items. So that’s the minimum order. After that you have to order at least another half load, so 5 more items. Between that and the next full load, they don’t care. They just don’t want to ship a truck that’s less than half full.

So the item quantities you can buy are: 10,15,16,17,18,19,20,25,26,27,28,29,30,35 …

Which formula will tell me for any number of required items the minimum shipping order?

Here is my general solution:

=ROUNDUP(IF(A6<=0,0,IF(A6<=$B$1*$B$2,$B$1*$B$2,$B$1*INT(A6/$B$1)+IF(MOD(A6,$B$1)>0,MAX(MOD(A6,$B$1),$B$1*$B$3),0))),0)

Download

Please read my Disclaimer.

Minimum_Truck_Load_Problem.xlsx [11 KB Excel file, open and use at your own risk]