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!

Tags:

Best Answer

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    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

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭
    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

  • Thanks so much, That worked perfectly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!