Formula for returning and rounding values based on cell values.

[SteynBS]

Good day guys,

I have a difficult formula that I need for a project. Not sure what to name the formula or the project.

I have order qty's in Column B that has to order in the order qtys' provided by a supplier. But the order qty's differ from supplier to supplier.

For now the variables. C01 qty profile is in Column R, SP01 qty profile is in Column Q and SHP0 qty profile is in Column P. but if the Profile is SP01 it has to look for the profile qty in Column P first. If there is a qty that should be used. If Column P is = to zero the qty in Column Q is needed.

Row 106 as example. The Profile needed is C01. The order qty is 2, which is less than what the suppliers needs. We need to order min of 12 or multiple of 12's so this is less than 12 so the Order Check should be 12. If the order qty was 15 it should order in multiples of 12 so in other words the order check must be 24.

Row 157 as Example. SHP0 is the required profile. The profile qty is 2, the order is 2 so the Order Check should remain 2. If the order Qty was 3, the order check should have been 4.

If Column P,Q,R is "0" or blank the order check should be the same as the order qty. There are other Order Profile such as SPH08 etc. they can be ignored, same value as in Column B should be in Column C.

Here is another set of data that is in the same table as above with different Order Profile

The order Check (C) should be equal to the Value in column Z if the order value is less than in Column Z, but if the order is more than the Value in Column Z, we have to order in multiples of it.

Row 268 as example. Order qty "B" is 1, the profile indicates no less than 10 units "z" so we should order 10. If "B" was 15 we should order 20

Row 284 as example. Order qty is 3064, the profile indicates units of 1000. So we have to order 4000. in this instance I would round it to 3000 as 64 units nothing, but consider row 285 I would round it up to 3000.

Is there any formula we can use to assist us with this. Its more than 30 000 rows of data, to do it 1 by 1 will take us an eternity. Thank you in advance.

 

[Anthony47]

Didn't understand what you said about first table.
Based on the layout of second table, I would use in C2

=ROUNDUP(B2/Z2,0)*Z2

At the moment there is no any tolerance taken into consideration, so this would return 4000 on line 284; which tolerance should be considered?
For example, using

=ROUNDUP(ROUND(B284/Z284,1),0)*Z284

would return 3000 up to an order of 3049

 

[SteynBS]

Didn't understand what you said about first table.
Based on the layout of second table, I would use in C2

=ROUNDUP(B2/Z2,0)*Z2

At the moment there is no any tolerance taken into consideration, so this would return 4000 on line 284; which tolerance should be considered?
For example, using

=ROUNDUP(ROUND(B284/Z284,1),0)*Z284

would return 3000 up to an order of

Good day,

Thank you for your feedback.

On your second point. I think following logic on 500. So anything less than 500 should round down to the nearest 1000. if more than 500 should round up to the next 1000 if it makes sense. In other words at 3499 it rounds down to 3000. If 3501 it should round up to 4000.

On your first point. Both pictures or tables are from the same table, only with different ordering profile.

So the ordering profile is given in column z. If it is plain numbers as in the second picture it is considered a static rounding. So the formula should take Column B and Column Z only in to consideration. As the solution you have provided will help with that, but.

As in the 1st picture. the ordering profile is concidered Dynamic. Ordering profile "C01" in Column Z looks at Column R. For example row 106. The order qty is 2, but the rounding is 12 (Column R). Therefor we should order in 12's if the order qty is less than 12, we order 12, but is it is 18 we order 24.

Order Profile SHP0 look only at Column P, Ordering profile SP01 look at column P first, but if blank it looks at Column Q.

Example row 206. Ordering profile is SP01. The profile is 0 in Column P, therefore we look at Column Q which is 24. The ordering qty is 141. so we should order in mutliples of 24. so we are going to order 144.

I hope it makes more sense now?

 

[Anthony47]

Could you explain in detail the logic of column Z?
Which are the possible values, in addition to plain numbers, and what to do for each of them?

 

[SteynBS]

Could you explain in detail the logic of column Z?
Which are the possible values, in addition to plain numbers, and what to do for each of them?

We have 2 groups of ordering profiles. 1) Static is a plain number such as 1, 2, 6, 12, 100, 1000, or any of that. If that is the case, column Z only will be used for the rounding. The order check will look at Column Z, if the static is 12, and the order is 6, it has to be rounded to 12, if the order is 18, it should be rounded to 24.

2) Dynamic - The only Dynamics we use are the C01, SHP0, and SHP01, there are others such as SHP08, and SHP09, but those can be ignored and can be considered as rounding of 1.

C01 refers to Column R. The order qty should be rounded to whatever is in column R.
SHP0 refers to Column P, The order qty should be rounded to whatever is in column P.
SHP01 refers to both Columns P and Q. The order qty should be rounded to whatever is in Column P, but if P is empty, the order qty should be rounded to whatever is in Column Q

 

[Anthony47]

Maybe, in C2:

=LET(ZVal,Z2,Order,B2,Block,IF(ISNUMBER(ZVal),ZVal,IF(ZVal="C01",R2,IF(RIGHT(ZVal,2)="P0",P2,MAX(P2:R2)))),IF(Block>=1000,MAX(1,ROUND(Order/Block,0))*Block,ROUNDUP(Order/Block,0)*Block))

Then copy down

 

[SteynBS]

Maybe, in C2:

=LET(ZVal,Z2,Order,B2,Block,IF(ISNUMBER(ZVal),ZVal,IF(ZVal="C01",R2,IF(RIGHT(ZVal,2)="P0",P2,MAX(P2:R2)))),IF(Block>=1000,MAX(1,ROUND(Order/Block,0))*Block,ROUNDUP(Order/Block,0)*Block))

Then copy down

Thank you for your assistance.

By looking at this, when I filter for the C01 - it all works perfectly.

There is only one SHP0 - it also pulls through correctly

SP01 -Except for the #DIV/0!. I assume it is because there is a zero value in Column P and Q
The other work well

Still on SP01, I just filtered for values in Column P, It seems its pulling through Column R.
Row 1401 Should be 6, because the is a value in Column P,
Row 2512 should order 28, because the value in Column P is 7, should order in multiples of 7

The rounding profiles selected in Z should be ignored and be used as 1. The order qty should be used as is.
Row 95 should be 152, row 209 should be 155

Now the Static rounding (numeric only)
I see the row 284 and 285 works perfectly, but it seems the other rows look at Column R?
Row 415 should only order in 15 as the static Rounding in Z is 15, but it orders 180 (same value as in R)
row 2821 orders 200 where it only should order 10
row 5077 works perfectly

Again thank you again for all your trouble, I really do appreciate it.

 

[Anthony47]

You wrote:

2) Dynamic - The only Dynamics we use are the C01, SHP0, and SHP01, there are others such as SHP08, and SHP09, but those can be ignored and can be considered as rounding of 1.

C01 refers to Column R. The order qty should be rounded to whatever is in column R.
SHP0 refers to Column P, The order qty should be rounded to whatever is in column P.
SHP01 refers to both Columns P and Q. The order qty should be rounded to whatever is in Column P, but if P is empty, the order qty should be rounded to whatever is in Column Q

So SP01 and the likes is not in the list; before amending the formula, please specify exactly what we can found in column Z and its meaning

Also, I took into consideration rounding when the lot size is >=1000; should we round in whichever situation?

Edit: Not sure I got what you mean with "there are others such as SHP08, and SHP09, but those can be ignored and can be considered as rounding of 1", could you explain?

 

[SteynBS]

You wrote:


So SP01 and the likes is not in the list; before amending the formula, please specify exactly what we can found in column Z and its meaning

Also, I took into consideration rounding when the lot size is >=1000; should we round in whichever situation?

Edit: Not sure I got what you mean with "there are others such as SHP08, and SHP09, but those can be ignored and can be considered as rounding of 1", could you explain?

This is what we can get in column Z

Static which is the numeric values only and dynamic which is the letter and numeric combination.

Static is straight forward, if the rounding profile is 1,10,100,1000,12,15,20,200,72 the order qty should be rounded to what the value in Z is(if it is static) if it is less. If it is morem it should be rounded in multiples of the value in Z. so if the rounding profile is 20, it should order in 20,40,60 etc. and if it is 15 it should order in 15, 30,45,60 etc.

If the value is dynamic. We only work with C01, SHP0 and SP01. P01,SP06,SP08,VP01,VP02,VP06,VP08 and blank we consider to be rounding of 1 So regardless of the order qty its multiplied by 1.

C01 looks at the value in Column R
SHP0 looks at the value in Column P
SP01 look firstly at the value in Column P, if column P is blank it looks secondly and lastly at Column Q

Regarding the static roundings. If it is in values of 100,200,500,1000. I believe we should keep it to the closest full qty. In other word in 100, if the order qty is 149 it should round to 100, but more than 150 it should go to 200. Same with 200. If the order qty is less than 300 round down to 200, if its more than 300 round up to 400. 500, if the order qty is les than 750, round down to 500, if its more than 750, round up to 1000. 1000 if the order qty is less than 1500, round down to 1000. if the order qty is more than 1500, round up to 2000.

I hope it makes sense.

Thank you for your patience and assistance.

 

[Anthony47]

Try this penultimate version:

=LET(ZVal,Z2,Order,B2,Block,IF(ISNUMBER(ZVal),ZVal,IF(ZVal="C01",R2,IF(ZVal="SHP0",P2,IF(ZVal="SHP01",P2+Q2*(P2=0),1)))),MAX(1,ROUND(Order/Block,0))*Block)