Hi,
Trying to come up with a single formula [not VBA] to calculate how many MOQ (Minimum Order Quantity) and SPQ (Standard Pack Quantity) I need to order to satisfy demand on a row. The MOQ is usually a multiple of th SPQ i.e. 4 x 500 piece SPQs = 1 x MOQ = 2,000 pieces.
Example ("/" used to seperate column data for below example)
Demand / MOQs / SPQs / Total Order
<=2,000 / 1 / 0 / 2,000
2,001 / 1 / 1 / 2,500
2,501 / 1 / 2 / 3,000
I am trying to figure out how to do it in a single column but I'm struggling with a really long winded formula at the moment and thought I'd put it out there !
Column A = MOQ
Column B = SPQ
Column C = Demand
Column D = Order Quantity ([a x MOQ] + [b x SPQ])
I can do it but need additional colums to calculate a & b used in Columd D first !
It needs to be a formula so I can easily recalculate once I change Demand in column C.
Any pearls of wisdom would be greatly appreciated.
Regards,
Ken
Trying to come up with a single formula [not VBA] to calculate how many MOQ (Minimum Order Quantity) and SPQ (Standard Pack Quantity) I need to order to satisfy demand on a row. The MOQ is usually a multiple of th SPQ i.e. 4 x 500 piece SPQs = 1 x MOQ = 2,000 pieces.
Example ("/" used to seperate column data for below example)
Demand / MOQs / SPQs / Total Order
<=2,000 / 1 / 0 / 2,000
2,001 / 1 / 1 / 2,500
2,501 / 1 / 2 / 3,000
I am trying to figure out how to do it in a single column but I'm struggling with a really long winded formula at the moment and thought I'd put it out there !
Column A = MOQ
Column B = SPQ
Column C = Demand
Column D = Order Quantity ([a x MOQ] + [b x SPQ])
I can do it but need additional colums to calculate a & b used in Columd D first !
It needs to be a formula so I can easily recalculate once I change Demand in column C.
Any pearls of wisdom would be greatly appreciated.
Regards,
Ken