How to use IF statements with logical operators for inventory


First challenge…I am trying to show when a number is input to the RECEIVED column, that it subtracts itself from the ORDERED column and puts the result in the BACKORDERED column (however if the RECEIVED column is blank, I need the BACKORDERED column to be ‘0’ or blank as well).

Second challenge…If the RECEIVED number equals the ORDERED column then the STATUS column should show ‘complete’. If the RECEIVED number is less than the ORDERED column, the STATUS column should show ‘in progress’.

If I was to say this in one sentence it would be “If received is not 0 or blank, subtract it from ordered and show result in backordered AND if received equals ordered change status to complete OR if received is less than ordered change status to in progress”

Any help would be much appreciated! :grinning:

Welcome to the community, @J_E! :smiley: First off, formulas cannot change the contents of other fields. A formula only creates output in its own field. You could turn {Status} into another formula, but there’s no way of coloring its contents aside from using colored emojis.

In that light, the formula for {Backordered} could be this:

IF(Received & "", Ordered - Received)

And a formula for {Status} could be this:

IF(Received & "", IF(Received = Ordered, "✅ Complete", "🟡 In Progress"))

Screen Shot 2021-08-13 at 10.22.58 AM

1 Like

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.