Hello forumites!
I've a bit of a conundrum with a spreadsheet my team use for ordering product. We have some suppliers who give us a minimum order quantity, but within that, state that we can achieve that MOQ by ordering across a range of 5 products, with each product order needing to hit a minimum of 300 units.
So for example, I can order pens, but the supplier requires me to order 1200 pens minimum. But they will let me order 300 Blue, 300 Red, 300 Black and 300 Green pens to achieve this.
The trouble I'm having is that the sheet I use suggests an exact requirement, e.g. 250 Blue, 100 Red, 50 Black and 5 green and 0 Orange and I need this rounded up by MOQ and Carton quantity whilst also considering the 1200 MOQ and the fact it can be split across the range.
I can get it to round this by both the MOQ of 1200 and the case quantity of 4 using the below, but this result often leaves me with suggested orders of a higher than required amount. So using the stated example of 250 Blue, 100 Red, 50 Black and 5 green and 0 Orange and the below formula, it suggests an order of 744 Blue, 300 Red, 300 Black and 300 Green with no Orange. It's the Blue that causes me issues! That should be 300!
D6 through D10 = Actual order amount of individual product (So the SUM function tells me the total requirement of all colours of pen).
F6 = MOQ
G6 = Carton Qty
=IF(D6<=0,1-1,IF(SUM(D$6:D$10)<=0,1-1,IF(SUM(D$6:D$10)<120,1-1,IF(AND(SUM(D$6:D$10)>120,SUM(D$6:D$10)<1200),MAX($F6,(CEILING((1200/SUM(D$6:D$10)*D6),$G6))),IF(D6>=(($F6/100)*10),MAX($F6,CEILING(D6,$G6)),1-1)))))
Can anyone help?! It's quite complicated so I hope I've made my needs clear!
I've a bit of a conundrum with a spreadsheet my team use for ordering product. We have some suppliers who give us a minimum order quantity, but within that, state that we can achieve that MOQ by ordering across a range of 5 products, with each product order needing to hit a minimum of 300 units.
So for example, I can order pens, but the supplier requires me to order 1200 pens minimum. But they will let me order 300 Blue, 300 Red, 300 Black and 300 Green pens to achieve this.
The trouble I'm having is that the sheet I use suggests an exact requirement, e.g. 250 Blue, 100 Red, 50 Black and 5 green and 0 Orange and I need this rounded up by MOQ and Carton quantity whilst also considering the 1200 MOQ and the fact it can be split across the range.
I can get it to round this by both the MOQ of 1200 and the case quantity of 4 using the below, but this result often leaves me with suggested orders of a higher than required amount. So using the stated example of 250 Blue, 100 Red, 50 Black and 5 green and 0 Orange and the below formula, it suggests an order of 744 Blue, 300 Red, 300 Black and 300 Green with no Orange. It's the Blue that causes me issues! That should be 300!
D6 through D10 = Actual order amount of individual product (So the SUM function tells me the total requirement of all colours of pen).
F6 = MOQ
G6 = Carton Qty
=IF(D6<=0,1-1,IF(SUM(D$6:D$10)<=0,1-1,IF(SUM(D$6:D$10)<120,1-1,IF(AND(SUM(D$6:D$10)>120,SUM(D$6:D$10)<1200),MAX($F6,(CEILING((1200/SUM(D$6:D$10)*D6),$G6))),IF(D6>=(($F6/100)*10),MAX($F6,CEILING(D6,$G6)),1-1)))))
Can anyone help?! It's quite complicated so I hope I've made my needs clear!
