If Formula Unparsable

Would anyone be able to help see what's wrong with this formula? I'm not sure how I'm screwing this up..

My formula works like this but if the status is anything other than partial I'd like the cell to say "N/A"

=IF([email protected] = "Partial", [email protected]) - [Received Quantity]@row

So I just tried this:

=IF([email protected] = "Partial", [email protected], "N/A") - [Received Quantity]@row


Ideally I'd like the formula to spit out the QTY column value if the status says anything other than Partial or Received.

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    Answer ✓

    @AnnieR Since you've given conflicting criteria,

    (if the status is anything other than partial I'd like the cell to say "N/A")

    (I'd like the formula to spit out the QTY column value if the status says anything other than Partial or Received.)

    I just went with what I think you want to achieve.

    =IF(OR([email protected] = "Partial", [email protected] = "Received"), [email protected] - [Received Quantity]@row, "NA")

    This says, if the Status is "Partial" or "Received", give me the QTY from this row minus the Received Quantity from this row; otherwise, just give me "N/A".

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

Answers

  • Kelly Drake
    Kelly Drake Overachievers

    I'm thinking the " - [Received Quantity]@row" part of the formula is what's throwing this.

    I would try this...

    =IF(OR([email protected] = "Partial", [email protected] = "Received"), [email protected] - [Received Quantity]@row, [email protected])


    This assumes you have columns named Status, QTY, Received Quantity exactly.

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY | senior project manager | global growth & concepts

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭
    Answer ✓

    @AnnieR Since you've given conflicting criteria,

    (if the status is anything other than partial I'd like the cell to say "N/A")

    (I'd like the formula to spit out the QTY column value if the status says anything other than Partial or Received.)

    I just went with what I think you want to achieve.

    =IF(OR([email protected] = "Partial", [email protected] = "Received"), [email protected] - [Received Quantity]@row, "NA")

    This says, if the Status is "Partial" or "Received", give me the QTY from this row minus the Received Quantity from this row; otherwise, just give me "N/A".

    Regards,

    Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

  • Thank you @Kelly Drake and @Jeff Reisman this was really helpful and I'm sorry for my incomplete question :)