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],"",""))

 

[jarett]

unless the qty on PO is greater than the minorderqty line 360 falls under this scenario, therefore needed the result in H to be blank.

 

[jarett]

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], ""))

I made one change which I think gave me the correct results. =IF([@QuantityOnPurchaseOrder]>[@MinimumOrderQty],"",IF([@QuantityOnHand]-[@QuantityOnSalesOrder] <= [@ReorderPointQty],[@MinimumOrderQty], "")). The version you had was leaving off lines that were below the reorder point when you take the qty sold already out of the on hand. Geez a lot of what if for this one.

Reorder_pts.xlsx
	A	B	C	D	E	F	G	H
1	ItemCode	QuantityOnHand	QuantityOnSalesOrder	QuantityOnPurchaseOrder	ReorderPointQty	MinimumOrderQty	LastPhysicalCountDate	OrderQty
4	10006961-BROWN-10D	7	2	0	6	12	6/22/2023	12
16	10006961-BROWN-15D	0	0	0	1	2	6/22/2023	2
17	10006961-BROWN-15EE	0	0	0	1	2	6/22/2023	2
32	10012251-BEIGE/KHAKI-LGR	51	37	0	36	40	6/22/2023	40
34	10012251-BEIGE/KHAKI-MEDR	18	8	0	36	36	6/22/2023	36
TEST_REORDER

Cell Formulas
Range	Formula
H4,H16:H17,H32,H34	H4	=IF([@QuantityOnPurchaseOrder]>[@MinimumOrderQty],"",IF([@QuantityOnHand]-[@QuantityOnSalesOrder] <= [@ReorderPointQty],[@MinimumOrderQty], ""))

 

[Bosquedeguate]

Great!