IF Statement

[jarett]

I am trying to have two logical test in my IF statement and running into a mind block. I've got the first thought process working where if two columns are <= give result, but I also want to check another column D if it is > column F produce blank value. So in my example on line 360 the value in column H OrderQty should be blank because column D is greater than column F even though my first statement is true. The other cells that produced results in OrderQty are correct.

Reorder_pts.xlsx
	A	B	C	D	E	F	G	H
1	ItemCode	QuantityOnHand	QuantityOnSalesOrder	QuantityOnPurchaseOrder	ReorderPointQty	MinimumOrderQty	LastPhysicalCountDate	OrderQty
325	CVFRD7GY-2XL REG	10	11	26	20	24	#######################
326	CVFRD7GY-2XL TAL	9	7	12	12	12	#######################
327	CVFRD7GY-3XL REG	19	8	0	12	12	#######################
328	CVFRD7GY-3XL TAL	15	5	12	12	12	#######################
329	CVFRD7GY-4XL REG	20	1	0	6	12	#######################
330	CVFRD7GY-4XL TAL	22	2	0	6	12	#######################
331	CVFRD7GY-5XL REG	0	7	13	6	6	#######################
332	CVFRD7KH-MED REG	10	1	12	12	12	#######################	12
333	CVFRD7KH-MED TAL	13	0	0	6	12	#######################
334	CVFRD7KH-SM REG	14	0	0	3	6	#######################
335	CVFRD7KH-XL REG	9	1	0	12	12	7/7/2023	12
336	CVFRD7KH-XL TAL	38	0	0	6	12	#######################
337	CVFRD7NY-2XL REG	24	4	0	20	24	#######################
338	CVFRD7NY-2XL TAL	17	2	0	12	12	#######################
339	CVFRD7NY-3XL REG	15	3	0	12	12	#######################
340	CVFRD7NY-3XL TAL	21	0	0	12	12	#######################
341	CVFRD7NY-4XL REG	22	0	0	6	12	#######################
342	CVFRD7NY-4XL TAL	31	0	0	6	12	#######################
343	CVFRD7NY-5XL REG	15	0	0	6	6	#######################
344	CVFRD7NY-LAR REG	6	7	26	20	24	#######################	24
TEST_REORDER

Cell Formulas
Range	Formula
H325:H344	H325	=IF([@QuantityOnSalesOrder]+[@QuantityOnHand]<= [@ReorderPointQty],[@MinimumOrderQty],IF([@QuantityOnPurchaseOrder]>=[@MinimumOrderQty],"",""))

 

[Bosquedeguate]

Please provide a copy of the data in Row 360 (the one that is causing the problem).

The is definitely an error in your IF statement (the second IF clause, is returning nothing in both the True and False case.

Your current Function for Col H is

=IF([@QuantityOnSalesOrder]+[@QuantityOnHand]<= [@ReorderPointQty],[@MinimumOrderQty],IF([@QuantityOnPurchaseOrder]>=[@MinimumOrderQty],"",""))

If the above logic is valid, I think you could do the following:
A few questions ...
1) is the Sales Order quantity items that are targeted to leave, i.e. they have been sold? If so, shouldn't this quantity be subtracted from the Qty on Hand?

Anyway, again using your existing IF ...
=IF([@QuantityOnSalesOrder]+[@QuantityOnHand]<= [@ReorderPointQty],[@MinimumOrderQty],IF([@QuantityOnPurchaseOrder]>=[@MinimumOrderQty],"",[@MinimumOrderQty]))

But, if this logic is correct would the following logic provide a correct result?
Logic: If QtyOnHand + QtySalesOrder + QtyPurchaseOrder >= QtyReorderPoint Then MinOrderQty Else nothing ("")

=IF([@QuantityOnSalesOrder]+[@QuantityOnHand] + [@QuantityOnPurchaseOrder] <= [@ReorderPointQty],[@MinimumOrderQty], "")

 

[jarett]

Please provide a copy of the data in Row 360 (the one that is causing the problem).

The is definitely an error in your IF statement (the second IF clause, is returning nothing in both the True and False case.

Your current Function for Col H is

=IF([@QuantityOnSalesOrder]+[@QuantityOnHand]<= [@ReorderPointQty],[@MinimumOrderQty],IF([@QuantityOnPurchaseOrder]>=[@MinimumOrderQty],"",""))

If the above logic is valid, I think you could do the following:
A few questions ...
1) is the Sales Order quantity items that are targeted to leave, i.e. they have been sold? If so, shouldn't this quantity be subtracted from the Qty on Hand?

Anyway, again using your existing IF ...
=IF([@QuantityOnSalesOrder]+[@QuantityOnHand]<= [@ReorderPointQty],[@MinimumOrderQty],IF([@QuantityOnPurchaseOrder]>=[@MinimumOrderQty],"",[@MinimumOrderQty]))

But, if this logic is correct would the following logic provide a correct result?
Logic: If QtyOnHand + QtySalesOrder + QtyPurchaseOrder >= QtyReorderPoint Then MinOrderQty Else nothing ("")

=IF([@QuantityOnSalesOrder]+[@QuantityOnHand] + [@QuantityOnPurchaseOrder] <= [@ReorderPointQty],[@MinimumOrderQty], "")

Sorry I had filtered after I typed the question, I did try the last formula you suggested and it returned all "TRUE" values. The qty on SO are sold but they are not taken out of the on hand yet, so I think my thought process/formula is wrong there, it should be =IF([@QuantityOnHand]-[@QuantityOnSalesOrder]<= [@ReorderPointQty],[@MinimumOrderQty],"") and line 360 is as follows

Reorder_pts.xlsx
	A	B	C	D	E	F	G	H
360	CVFRD7NY-LAR REG	6	8	27	20	24	#######################	24
361	CVFRD7NY-LAR TAL	39	0	0	12	12	#######################
362	CVFRD7NY-MED REG	41	3	20	12	12	#######################
363	CVFRD7NY-MED TAL	30	0	0	6	12	#######################
364	CVFRD7NY-SM REG	10	0	0	6	12	#######################
365	CVFRD7NY-XL REG	51	11	8	20	24	#######################
366	CVFRD7NY-XL TAL	25	1	6	12	12	#######################
367	FRT-HJE GRY 2XL	52	17	10	36	36	6/1/2023	36
TEST_REORDER

Cell Formulas
Range	Formula
H360:H367	H360	=IF([@QuantityOnHand]-[@QuantityOnSalesOrder]<= [@ReorderPointQty],[@MinimumOrderQty],"")

where column H should still be blank, but line 367 is correct now

 

[Bosquedeguate]

So does it work if we change the formula to:
=IF([@QuantityOnHand] - [@QuantityOnSalesOrder] + [@QuantityOnPurchaseOrder] <= [@ReorderPointQty],[@MinimumOrderQty], "")

IF QtyOnHand - QtySold + QtyOrdered <= QtyReorderPoint Then Show QtyMinimumOrder; Else Nothing ("")

Right?

 

[jarett]

So does it work if we change the formula to:
=IF([@QuantityOnHand] - [@QuantityOnSalesOrder] + [@QuantityOnPurchaseOrder] <= [@ReorderPointQty],[@MinimumOrderQty], "")

IF QtyOnHand - QtySold + QtyOrdered <= QtyReorderPoint Then Show QtyMinimumOrder; Else Nothing ("")

Right?

No, I do not want to take into account the qty on PO unless the qty on PO is greater than the minorderqty, reasoning is because we might order 1 or 2 pieces if we run out of stock for individual orders, so those items would already be so say "allocated". Only if the qty on PO is larger than the minorderqty would I know if we have inventory coming in for "stock".

 

[Bosquedeguate]

Is the =IF equation working correctly?

If not please provide sample data that does not give the correct result.

 

[Bosquedeguate]

Why do you expect cell H360 to be blank?
You have 6 on hand, 8 on sales order; that is -2 in your IF function
Which is less than your RE-order quantity threshold.

So, I would expect to see 24 (order amount) in H360.

But, if we include the qty ordered it, would be blank.

 

[jarett]

Why do you expect cell H360 to be blank?
You have 6 on hand, 8 on sales order; that is -2 in your IF function
Which is less than your RE-order quantity threshold.

So, I would expect to see 24 (order amount) in H360.

But, if we include the qty ordered it, would be blank.

Yes because the qty on PO is larger than the minorderqty so that is why it should be blank, we have already placed an order for 24 so I do not want to prompt to order another 24. The sample data with the 24 on line 360 above shows the wrong result.

 

[Bosquedeguate]

No, I do not want to take into account the qty on PO unless the qty on PO is greater than the minorderqty, reasoning is because we might order 1 or 2 pieces if we run out of stock for individual orders, so those items would already be so say "allocated". Only if the qty on PO is larger than the minorderqty would I know if we have inventory coming in for "stock".

 

[Bosquedeguate]

OK - I think I have it. You need to check the QtyOrdered>QtyOrderMin first, then test what is OnHand,QtySold see formula below. Please test and let me know.

GetLastRow.xlsm
	A	B	C	D	E	F	G	H
12	CVFRD7GY-4XL TAL	22	2	0	6	12	#######################
13	CVFRD7GY-5XL REG	0	7	13	6	6	#######################
14	CVFRD7KH-MED REG	10	1	12	12	12	#######################	12
15	CVFRD7KH-MED TAL	13	0	0	6	12	#######################
16	CVFRD7KH-SM REG	14	0	0	3	6	#######################
17	CVFRD7KH-XL REG	9	1	0	12	12	7/7/2023	12
18	CVFRD7KH-XL TAL	38	0	0	6	12	#######################
19	CVFRD7NY-2XL REG	24	4	0	20	24	#######################
20	CVFRD7NY-2XL TAL	17	2	0	12	12	#######################
21	CVFRD7NY-3XL REG	15	3	0	12	12	#######################
22	CVFRD7NY-3XL TAL	21	0	0	12	12	#######################
23	CVFRD7NY-4XL REG	22	0	0	6	12	#######################
24	CVFRD7NY-4XL TAL	31	0	0	6	12	#######################
25	CVFRD7NY-5XL REG	15	0	0	6	6	#######################
26	CVFRD7NY-LAR REG	6	7	26	20	24	#######################
Sheet3

Cell Formulas
Range	Formula
H12:H26	H12	=IF([@QuantityOnPurchaseOrder]>[@MinimumOrderQty],"",IF([@QuantityOnSalesOrder]+[@QuantityOnHand] <= [@ReorderPointQty],[@MinimumOrderQty], ""))