Formula for rounding up to the MOQ then

[sjuhl99]

I am trying to write a formula where the PO Quantity will look at the MOQ first the round up by the pallet quantity to get my desired order quantity. As of now it is rounding higher by the MOQ, so in the example below it is suggesting 6,000 however I only need the MOQ of 3,000 + an extra pallet of 750. My current formula is '=IF(N8="N",0,CEILING(BM8-Z8,J8)), If PO required =Y then calculate the difference between Available Inventory vs our Target stock settings then using the multiple of the MOQ's. So trying to get incremental increases based on the pallet quantity rather than lopping on a whole MOQ. Please help.

Pallet Quantity (I8)	MOQ (J8)	Vendor	Vendor Item #	VLT (Weeks)	PO Required (N8)	PO Qty
750​	3,000​	PREMMFG	43710	4	Y	6,000​

​	​					​
​	​					​

 

[Jeffrey Mahoney]

How about this:
=IF(N9="N",0,CEILING(J9/I9,1)*I9+I9)

 

[sjuhl99]

It works but appears not be taking into account the correct amount to start with, meaning my (Target Stock setting - Current Inventory) so the BM8-Z8. This is calculated first to ensure the PO requirement is rounding up to meet the Target stock but rounding up currently in full MOQ quantities.

 

[Jeffrey Mahoney]

Data in BM8-Z8 wasn't provided.

 

[sjuhl99]

You are correct, sorry about that, so its taking where the quantity available + on order (Z8) < the MRP Min (BM8) and puts a Y in N8 where that is true. Then it takes the difference between BM8-Z8 and round that to the nearest MOQ (J8) to get back to the MRP Min but will keep using the MOQ value rather than individual pallet quantities to get back to the MRP min, if that makes sense.

Pallet Quantity (I8)	MOQ (J8)	PO Required (N8)	PO Qty (08)	Qty_Available + OO (Z8)	MRP Min (BM8)
750​	3,000​	Y	6,000​	5,337​	9,900​