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(Status@row = "Partial", QTY@row) - [Received Quantity]@row

So I just tried this:

=IF(Status@row = "Partial", QTY@row, "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(Status@row = "Partial", Status@row = "Received"), QTY@row - [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

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

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Kelly Drake
    Kelly Drake Overachievers Alumni

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

    I would try this...

    =IF(OR(Status@row = "Partial", Status@row = "Received"), QTY@row - [Received Quantity]@row, QTY@row)


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

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • 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(Status@row = "Partial", Status@row = "Received"), QTY@row - [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

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

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • AnnieR
    AnnieR ✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!