need help with an IF function
Hello community,
I am trying to come up with a cell formula that is a simple difference function, but I need it to be triggered by the number in another cell. It is for an inventory/ordering sheet. Someone will count the inventory and type in the "QTY ON HAND". there is also a column for "MINIMUM QTY NEEDED" which is preset and static. I just want to take the "DESIRED QTY" minus "QTY ONHAND", but I only want to trigger this function if the "QTY ON HAND" is lower than the "MINIMUM NEEDED"
I came up with this sentance, but it is not properly expressed so I'd love some help cleaning it up.
=IF([QTY ON HAND]1<[MINIMUM NEEDED QTY ON HAND (OR REORDER POINT)]1), ([DESIRED QUANTITY ON HAND]1-[QTY ON HAND]1)
I'm not sure if it should be IF OR SUMIF, or something else completely
Let me know if I need to clarify further
Thanks so much!
Best Answer
-
No, you pretty much have it
=IF([QTY ON HAND]@row < [MINIMUM NEEDED QTY ON HAND (OR REORDER POINT)]@row, [DESIRED QUANTITY ON HAND]@row - [QTY ON HAND]@row, "")
This would show a blank if it was not triggered
=IF([QTY ON HAND]@row < [MINIMUM NEEDED QTY ON HAND (OR REORDER POINT)]@row, [DESIRED QUANTITY ON HAND]@row - [QTY ON HAND]@row, 0)
This would show a zero if it were not triggered
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
Answers
-
No, you pretty much have it
=IF([QTY ON HAND]@row < [MINIMUM NEEDED QTY ON HAND (OR REORDER POINT)]@row, [DESIRED QUANTITY ON HAND]@row - [QTY ON HAND]@row, "")
This would show a blank if it was not triggered
=IF([QTY ON HAND]@row < [MINIMUM NEEDED QTY ON HAND (OR REORDER POINT)]@row, [DESIRED QUANTITY ON HAND]@row - [QTY ON HAND]@row, 0)
This would show a zero if it were not triggered
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
Thanks so much, That worked perfectly
Help Article Resources
Categories
Check out the Formula Handbook template!