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.

 

[SteynBS]

Anthony,

Looking at the blanks first, 100%

Looking at the statics: It does not use the rounding in column Z Row 35 order check must be 18 for example. The previous formula worked better on the static order profile. The 200 and 1000 order profile does not pull through correctly

The Dynamics we do not work with multiply by 1 which is 100%

The Dynamics we work with: C01 looks at column R, which is 100%, its just row 279 that pulls through less than the order qty. Row 279 should be 72

On SHP0 is 100%, there is only 1 line so I changed the value in Column P, it changes accordingly in Column C which is 100%
SP01 however does not bring back the value of Column Q if Column P is "0"

SP01 with a value in Column P does not return it for example 1401 must be 6 in Column C and row 2512 should be 28 in Column C (order in 7's due to the profile in Column P)

Is there anyway I can email you a copy or sample sheet?

I appologize for the frustration, I am really greatful for your assistance.

 

[Anthony47]

Are you sure that the "18" in Z35 is a number? Or is it a string?
Modify this portion

Block,IF(ISNUMBER(--ZVal),--ZVal,IF(etc etc

(ie --ZVal instead than ZVal)


Row 279 (and 237, 235): this is a rounding issue, that is still vaguely defined. What should those lines be instead than what is calculated?

Realted to other test results: do columns P-Q-R contains Numbers or String? (if you format those values as Numbers with two decimal how they look?) The formula looks for Numbers, but can be adapted for Strings

 

[SteynBS]

Are you sure that the "18" in Z35 is a number? Or is it a string?
Modify this portion

Block,IF(ISNUMBER(--ZVal),--ZVal,IF(etc etc

(ie --ZVal instead than ZVal)


Row 279 (and 237, 235): this is a rounding issue, that is still vaguely defined. What should those lines be instead than what is calculated?

Realted to other test results: do columns P-Q-R contains Numbers or String? (if you format those values as Numbers with two decimal how they look?) The formula looks for Numbers, but can be adapted for Strings

Z35 has not been changed or converted while I used power query to merge the data. The column contains cells with either numbers or letters with numbers, so I think using it as a string (convert to text) will be better.

Sorry Anthony, I do not understand how to modify the formula as indicated. --Zval instead of Zval. I do not understand the "--"

Regarding the rounding issue, C01 rounding profile looks at column R, so row 279 should order in 24's order qty is 60, so it should round to 72. (This is borderline as it is exactly in the middle, between 48 and 72)

Regarding P-Q-R it will always contain numbers.

I have converted to numbers with decimals, the value remains the same.

 

[Anthony47]

Sorry Anthony, I do not understand how to modify the formula as indicated. --Zval instead of Zval. I do not understand the "--"

You need to type -- (minus minus) before ZVal; this is a trick to convert numerical strings to numerical values

As far as line 279, this is related to the way Excel calculates Rounded values: >=0.5 means 1, <0.5 means 0, but a lot of indecision arises when you are near that threshold. And indeed my 32bits XL365, for B2=60, R2=24 and Z2=C01 returns 72
To "bias" the result to a higher option, lets use Order;B2+0.0001

Regarding P-Q-R it will always contain numbers.

I have converted to numbers with decimals, the value remains the same.

Well, if "the value remain the same" that should means they are Strings not Numbers: if they were numbers then 10 (for example) should become 10.00 (it is still 10 but shown with the 2 decimals as the formatting requires)
Thus let's extend using "--" along the formula, that becomes:

=LET(ZVal,Z2,Order,B2+0.0001,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)

 

[SteynBS]

You need to type -- (minus minus) before ZVal; this is a trick to convert numerical strings to numerical values

As far as line 279, this is related to the way Excel calculates Rounded values: >=0.5 means 1, <0.5 means 0, but a lot of indecision arises when you are near that threshold. And indeed my 32bits XL365, for B2=60, R2=24 and Z2=C01 returns 72
To "bias" the result to a higher option, lets use Order;B2+0.0001


Well, if "the value remain the same" that should means they are Strings not Numbers: if they were numbers then 10 (for example) should become 10.00 (it is still 10 but shown with the 2 decimals as the formatting requires)
Thus let's extend using "--" along the formula, that becomes:

=LET(ZVal,Z2,Order,B2+0.0001,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)

Aha, I got you, now I understand.

By the value remaining the same, you are correct, it does add the .00 decimal. the whole value remained the same. Sorry, misunderstanding there.

Everything is working, I will need to do some more testing on new sites, but the static order profile seems to work perfectly, the Dynamics we don't use multiplies by 1. The C01 takes the value in Column R. Its only the SP01 that seems a bit wonky

C01 works perfectly.

SP01 in Column Z must take the value from Column P first, so in this instance, 1546 as an example the order check qty must be 6, because the value in in P1546 is 6. Example 3119, The Order Check qty must be 8, as P3119 is 8.

Still with SP01, if the Vlaue in P is "0" it should take the value in Q. Example 206, Q206 is 24, so the Order Check value must be 144, mulitples of 24. Example 263. Q263 is 40, so Order Check must be 80, mulitples of 40.

If you can help me to sort out SP01, I believe the formula will be 100%, I think it is amazing what you can do with formulas sir. Thank you

 

[Anthony47]

SP01 was not mentioned in your message #5, Formula for returning and rounding values based on cell values.
Thus it defaults to 1
What should it do, instead than defaulting to 1?

 

[SteynBS]

Anthony,

I apologize, I was sure I did refer to C01,SHP0 and SP01 as Dynamic Order Profiles we use. I apologize for the misunderstanding,

SP01 should look in Column P first for a value, if Column P is "0" it should take Column Q's value like below. Column P is all 0, so it should take the value from Column Q.
Example: row 206, P206 is 0, so it should use the 24 in Q206(24) The value it should return in Order Check is 144, mulitples of 24. Example 208. Order Check there should be 192. Example 229. The Order Check qty should be 60 due to Q229 being 30.

Below we have a value in Column P, So SP01 should use Column P first, and if "0" use Column Q
Example Row 1453, The order Check must be 3, because P1453 is 3.
Example Row 2512, the Order Check must be 28, because in P2512 the value is 7(order in multiples of 7)
Example 3116, The Order Check must be 10 because P3116 is 10.

Sorry for the inconvenience.

 

[Anthony47]

To use P:Q for SP01:

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

Note that both SHP01 and SP01 would refer to P:Q; if SHP01 should instead default to 1, then use

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

 

[SteynBS]

To use P:Q for SP01:

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

Note that both SHP01 and SP01 would refer to P:Q; if SHP01 should instead default to 1, then use

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

Perfect sir thank you, Dynamics are 100%. sorry I just noticed the static ordering profile is not pulling through.

With the static ordering profile, the rounding value in Z should be used.

I have noticed on the SP01 if P and Q has both "0" it returns a divide error. I assume its because of both Columns having a ZERO.

Where are you from sir? You deserve a Bells, thank you very much for your assistance.

 

[Anthony47]

Grrrr...
In both the formulas of my previous message there is a regression:
-modify the portion IF(ISNUMBER(ZVal),ZVal,etc etc to IF(ISNUMBER(--ZVal),--ZVal,etc etc

Yes, #DIV/0! is due to missing information in the appropriate columns; correct the columns and the error will disappear; or use IFERROR to hide it (but I don't think it's wise to hide it)