Formula help! IF OR

[SarahLouise]

Morning! 

I'm trying to figure out a formula but struggling to get it to work and wondered if anyone could help please?

Status column is a single select column with status numbers from 0-499 which i want to add information into a new column from a formula showing "1. called off and not yet delivered"

Status 500 needs to say "8. Not listed in dropdown - detail in Comments" in the same formula column.

This is as far as i have got, can anyone point me in the right direction please? 

Thank you! 

IF(

  OR(

    {Status} <500, "1. called off and not yet delivered")

{Status} = 500, "8. Not listed in dropdown - detail in Comments")

[Jack_Manuel]

Hi,

IF() takes three things (arguments), separated by commas. The first argument is the test - check if this condition is true or false. The second argument is what to do if the test is true. The third argument is what to do if the test is false.

Assuming the Status field can definitely only be the numbers 0 to 500, you don't actually need anything other than an IF().

IF(
  {Status} < 500,
  "1. called off and not yet delivered",
  "8. Not listed in dropdown - detail in Comments"
)

So this is saying, check if {Status} is less than 500 (not less than or equal to), if it is then print "1. called off and not yet delivered", if it isn't (it must be 500), print "8. Not listed in dropdown - detail in Comments"

Note if you add another status above 500, like 501, it will still print "8. Not listed in dropdown - detail in Comments". If you want to account for more than two possibilities you'll need a (slightly) more complicated formula, which I'd be happy to help with. 

[SarahLouise]

Hi Jack 

Thats great thank you! 

Just realised i need to exclude blanks in this, is it possible ?

[Jack_Manuel]

Yep, you can just wrap the whole thing another IF() that first checks if there's something in {Status}, if there is, run the old IF(), if there's not, print BLANK().

IF(
  {Status},
  IF(
    {Status} < 500,
    "1. called off and not yet delivered",
    "8. Not listed in dropdown - detail in Comments"
  ),
  BLANK()
)

[Alexey_Gusev]

Note that you don't need to type  

IF({Status}!=BLANK()... ,

you can just use simple syntax 

IF({Status},

another nice thing you can omit 'result if false', it is blank by default.

IF(
  {Status},
  IF(
    {Status} < 500,
    "1. called off and not yet delivered",
    "8. Not listed in dropdown - detail in Comments"
  )
)

[Jack_Manuel]

True but I was trying to be more explicit for teaching purposes and readability.