IF Statement

[CM246]

Hi,

I am looking for help with an if statement that is not working:

Planned Order:

IF(([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders] - [Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO]) < [Reorder point]) then ([Reorder point] - ([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders] - [Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO])) else [Sum_Month_3_Planned_Orders] endif

The formulas that are adding and subtracting are not working within the IF statement and it is giving everything is below the Reorder Point which is not true.

[Raj]

@CM246 

you can try this
IF(([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders]) - ([Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO])) < [Reorder point]) then ([Reorder point] - ([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders]) - ([Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO])) else [Sum_Month_3_Planned_Orders] endif

this should work

mark done if solved.

[CM246]

This has changed the outcome so it's definitely the placement of brackets that is the issue, however, it is still not working correctly. 

The flow is too large to share however this is the calculation using the below and the figures:

IF(([916] + [1084]) - ([583]+[1600])) < [2000]) then ([2000 - ([916] + [1084]) - ([583]+[1600])) else [0] endif

As you can see, the if statement is > the Reorder point and should not calculate the second half which it is and it gives -2183 as the result

[CM246]

Ok, so I changed the brackets to this and it seems to work on the first half but the second half it is not: then ([Reorder point] - ([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders]) - ([Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO]))

it is taking the reorder point and minus each individual figure i.e. Reorder point] - ([Month 4 Inventory Projection] + [Sum_Month_4_Planned_Orders] + [Sum_Month_5_Open_PO]) - [Sum_Month_5_Requirements]

2000 - (2264 + 0 + 0) - 583

So in this case my answer should be 2000 (reorder point) - 1681 = 319 should be my planned order

I hope this isn't too confusing and makes sense... 

[Raj]

@CM246 
can the paste the formula you are using now
and the values of 
[Month 2 Inventory Projection]
[Sum_Month_2_Planned_Orders]
[Sum_Month_3_Requirements]
[Sum_Month_3_Open_PO])
[Reorder point])

and required output will correct the formula

[Raj]

@CM246 
updated

IF(([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders]) - ([Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO]) < [Reorder point]) then [Reorder point] - (([Month 2 Inventory Projection] + [Sum_Month_2_Planned_Orders]) - ([Sum_Month_3_Requirements]+[Sum_Month_3_Open_PO])) else [Sum_Month_3_Planned_Orders] endif

mark done if solved.

[CM246]

This also did not work - it is giving -1016 for Month 2 planned orders using below numbers:

IF(([916] + [1084]) - ([583]+[1600]) < [2000]) then [2000] - (([916] + [1084] +[1600]) - ([583])) else [0] endif

So it seems it is adding 2000 + 583 (Reorder point + requirements) and then minus everything else

[BS_THE_ANALYST]

@CM246  this seems awful to debug.

Can you create some columns which represent pieces of your if statement. Then you'll see where issues are in a quick fashion.

All the best,

BS 

[CM246]

This is a sample of the fields I am pulling in from SAP. 

All planned orders are 0, however, I want to place a planned order where the previous month's Inventory projection + the previous month's planned order (generated from the formula I am discussing now) - current month requirements is less than reorder point. 

[BS_THE_ANALYST · Accepted solution]

@CM246 thanks for the sample fields but that was a miscommunication.

I'm suggesting a method for you to be able to debug this yourself in a coherent and quick way.

Might I suggest something like the following:

 

Break the IF statement up into several columns. This will allow you to determine which part(s) are causing the issue, right.

Hopefully that makes sense? 

All the best,

BS