Advanced Rounding Help Required!!!

[Kenty7]

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!

 

[Damon Ostrander]

Hi Kenty7,

So, why should blue be 300? Is it because 300 is the carton quantity? And what what is the case quantity of 4 mean? Are there a certain number of cartons in a case? And must a certain number of cases be ordered?

It appears to me your formula needs the cells that contain suggested exact requirement of each color as input, but you don't mention where these values are stored. And your formula should return five values: the number of cartons of each color to be ordered. Am I understanding this correctly (I'm not sure because you only show one non-array formula)?

Damon

 

[Kenty7]

Hi Damon,

The reason it needs to be 300 is because the actual order required is 250, but the minimum I can order is 300 per colour. What the formula I have does is kick back 744, which would leave me spending more money than I actually need too and holding more stock than I actually need.

To try and clarify, I have one supplier who makes all the colours of pen, they say I can order no less than 1200 pens in total and I can split my order across all the colours, but I must also order no less than 300 of any colour of pen (so they can manage their manufacturing costs). So I could order 900 Blue pens and 300 red, or 300 each of red, blue, green and black etc.

The carton size is how many pens go into a carton, so I need the final number rounded by this amount. So there are 4 pens in a carton, so 300 pens is 75 complete cartons and I can only order in complete cartons also. So If the actual amount of pens I needed in blue was 301 pens, I would round that order up to 304 (76 complete cartons of 4 pens).

On the last part, I'm honestly not sure what you're asking, I have used array formulae on occasion but I have a very basic understanding.

I'm happy with the formula that generates the exact requirement I have and at present the rounding part is saying take the MOQ of 1200, divide that by the total number of pens required (The exact amount), then multiply the individual requirement by that amount to get the percentage of the MOQ that a particular colour requires. Then it applies the MOQ of 300 if required (As you can never order less than 300 of a colour) and the carton rounding.

It's really difficult to explain and I can't post a demo of the sheet so I've put in the cell references and whats in them so you could rebuild it yourself to see if you get a chance, I hope I've made myself clear, thanks for looking at this for me.

Column A - Description of product
A1 - Blue Pen
A2 - Red Pen
A3 - Black Pen
A4 - Green Pen
A5 - Orange Pen

Column B - Exact amount required
B1 - 250
B2 - 100
B3 - 50
B4 - 5
B5 - 0

Column C - Rounding formula
C1 - =IF(B2<=0,1-1,IF(SUM(B$2:B$6)<=0,1-1,IF(SUM(B$2:B$6)<120,1-1,IF(AND(SUM(B$2:B$6)>120,SUM(B$2:B$6)<1200),MAX($D2,(CEILING((1200/SUM(B$2:B$6)*B2),$E2))),IF(B2>=(($D2/100)*10),MAX($D2,CEILING(B2,$E2)),1-1)))))
C2 - =IF(B3<=0,1-1,IF(SUM(B$2:B$6)<=0,1-1,IF(SUM(B$2:B$6)<120,1-1,IF(AND(SUM(B$2:B$6)>120,SUM(B$2:B$6)<1200),MAX($D3,(CEILING((1200/SUM(B$2:B$6)*B3),$E3))),IF(B3>=(($D3/100)*10),MAX($D3,CEILING(B3,$E3)),1-1)))))
C3 - =IF(B4<=0,1-1,IF(SUM(B$2:B$6)<=0,1-1,IF(SUM(B$2:B$6)<120,1-1,IF(AND(SUM(B$2:B$6)>120,SUM(B$2:B$6)<1200),MAX($D4,(CEILING((1200/SUM(B$2:B$6)*B4),$E4))),IF(B4>=(($D4/100)*10),MAX($D4,CEILING(B4,$E4)),1-1)))))
C4 - =IF(B5<=0,1-1,IF(SUM(B$2:B$6)<=0,1-1,IF(SUM(B$2:B$6)<120,1-1,IF(AND(SUM(B$2:B$6)>120,SUM(B$2:B$6)<1200),MAX($D5,(CEILING((1200/SUM(B$2:B$6)*B5),$E5))),IF(B5>=(($D5/100)*10),MAX($D5,CEILING(B5,$E5)),1-1)))))
C5 - =IF(B6<=0,1-1,IF(SUM(B$2:B$6)<=0,1-1,IF(SUM(B$2:B$6)<120,1-1,IF(AND(SUM(B$2:B$6)>120,SUM(B$2:B$6)<1200),MAX($D6,(CEILING((1200/SUM(B$2:B$6)*B6),$E6))),IF(B6>=(($D6/100)*10),MAX($D6,CEILING(B6,$E6)),1-1)))))

Column D - Minimum Order Quantity
D1 - 300
D2 - 300
D3 - 300
D4 - 300
D5 - 300

Column E - Carton Quantity
E1 - 4
E2 - 4
E3 - 4
E4 - 4
E5 - ​4

 

[Damon Ostrander]

Hi Kenty,

Here is a solution for you. This is a User-Defined function (UDF) that you can use as if it were a built-in worksheet function. It is an array function, so you must enter it in multiple cells in a column. You do this by selecting the cells (in your example C1:C5), then enter this formula using Ctrl-Shift-Enter.

Here is the code:

Option Base 1

Function OrderQty(ReqdQty As Range, MinQty As Range, CtnQty As Range, TOQ As Long) As Variant
   Dim NoToOrder()   As Integer
   Dim MOQfraction() As Single
   Dim i             As Integer
   
   ReDim NoToOrder(1 To ReqdQty.Count, 1)
   ReDim MOQfraction(1 To ReqdQty.Count)
   
   Dim TotalRqd      As Long
   
   TotalRqd = Application.WorksheetFunction.Sum(ReqdQty)
   For i = 1 To ReqdQty.Count
      NoToOrder(i, 1) = ReqdQty(i) * TOQ / TotalRqd
   Next i
   
   'do required round up by min quantity and carton quantity
   For i = 1 To ReqdQty.Count
      If NoToOrder(i, 1) > 0 Then
         If NoToOrder(i, 1) < MinQty(i) Then
            NoToOrder(i, 1) = MinQty(i)
         End If
         NoToOrder(i, 1) = RoundUp(NoToOrder(i, 1), CtnQty(i))
      End If
   Next i
   
   OrderQty = NoToOrder
   
End Function

Function RoundUp(m As Integer, n As Integer) As Long
   If m Mod n <> 0 Then
      RoundUp = (m \ n + 1) * n
   Else
      RoundUp = m
   End If
End Function

You should copy all this code and paste it into a standard macro module Code pane. To to this go to the Visual Basic Editor (keyboard Alt-TMV), insert a new macro module (Alt-IM), and paste).

The formula you should enter in C1:C5 is:

=OrderQty(C1:C5,D1:D5,E1:E5,B9)

and after you enter it as an array function it should appear in curly braces like this:

={OrderQty(C1:C5,D1:D5,E1:E5,B9)}

By the way, B9 is the cell where I placed the minimum total order quantity (1200).

I hope I correctly understood all your constraints, rounding up requirements, and your desire to scale all the order amounts proportionally among the colors to achieve the 1200.

Damon

 

[Kenty7]

Hi Damon,

Thank you for looking at this but I can't seem to get this to take, what I don't understand is how if I put this formula in column C, it creates an order? As it doesn't reference the actual requirement at all? Or is that somewhere in the UDF?

Long and short of it though is I don't seem able to replicate this in the sheet, mayhap my technical expertise is lacking! I can get the macro information in, that's no problem and pasting the formula as an array is fine but if I put that formula into column C all I get is a circular reference?

I did wonder if that shouldn't reference column B, the actual order? When I amend it to look there I get a result, but it's the same result my original rounding formula gives me so I'm stuck in the same spot?

Thanks,

Daniel

 

[Peter_SSs]

See if this is any use. It makes any overall adjustment on blue pens.
C2 is a stand-alone formula.
C3 is copied down.

Excel Workbook
	A	B	C	D	E	F
1		Wanted	Order No	MOQ	Carton Qty	Overall Min
2	Blue	250	300	300	4	1200
3	Red	100	300	300	4
4	Black	50	300	300	4
5	Green	5	300	300	4
6	Orange	0	0	300	4
Order

If there are circumstances where this does not do what you want, please give sample data, expected results and explanation of why those are the expected results.

 

[Kenty7]

Hello Peter,

Thank you for the suggestion, but that formula relies on the largest required amount being the first in the series, where this isn't actually guaranteed. E.g. If the order of requirement was reversed and I need to order 0 blue, 5 red, 50 black, 100 green and 250 orange, then your formula still suggests 300 Blue, even though I don't actually need any.

I need something that looks at the requirement and rounds up the actual requirement to either the MOQ (300) or the nearest rounded quantity (E.g. if the actual requirement is 398 I need the formula to round by the carton quantity which would be 400).

Daniel

 

[Peter_SSs]

I'm not sure what the logic is? If you only wanted 5 red, 50 black and 3 orange, what would the order be? You would clearly need a whole lot of something you don't actually want - why not blue? What would you want and why?

As I asked before give some sample data (a few) AND the expected results AND the reasoning.

 

[Damon Ostrander]

Hi again Peter,

So sorry. That first argument should be referring to column B (not C) so the formula should be

=OrderQty(B1:B5,D1:D5,E1:E5,B9)

Sounds like you did everything right. Mea culpa!

Damon

 

[Marcelo Branco]

Maybe something like this


[TABLE="class: grid"]
<TBODY>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

Wanted​

[/TD]
[TD]

Carton Qty​

[/TD]
[TD]

Adjusted​

[/TD]
[TD]

Order No​

[/TD]
[TD][/TD]
[TD]

Total Adjusted​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Blue​

[/TD]
[TD]

150​

[/TD]
[TD]

4​

[/TD]
[TD]

300​

[/TD]
[TD]

300​

[/TD]
[TD][/TD]
[TD]

1200​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Red​

[/TD]
[TD]

401​

[/TD]
[TD]

4​

[/TD]
[TD]

404​

[/TD]
[TD]

404​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Black​

[/TD]
[TD]

0​

[/TD]
[TD]

4​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Green​

[/TD]
[TD]

0​

[/TD]
[TD]

4​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

Orange​

[/TD]
[TD]

301​

[/TD]
[TD]

4​

[/TD]
[TD]

304​

[/TD]
[TD]

496​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


Formula in D2 copied down
=IF(B2=0,0,MAX(300,CEILING(B2,C2)))

Formula in G2
=MAX(SUM($D$2:$D$6),1200)

Formula in E2 copied down
=IF(B2=0,0,IF(SUM(B$2:B2)=SUM($B$2:$B$6),MAX(D2,$G$2-SUM(E$1:E1)),D2))

Hope this helps

M.