Mastering the IF Function

The IF Function is one of the most used functions when writing formulas. It is crucial to have a thorough understanding of its potential. The IF function requires either 2 or 3 parameters.

The 1st Parameter: the Condition

The first parameter is the condition that the function will evaluate. Typically we want to use the logical operators: =, >, <,> = and <= as well as a handful of other functions like CONTAINS(), SEARCH(), SEARCHCI() AND SWITCH().

Here a few examples:




The 2nd Parameter: the Returned Value if the condition is TRUE

The second parameter is the value we want the function to return if the condition is met.

1:{Text}=IF({Status}="Done"),"Completed Task")

1:{Text}=IF(CONTAINS("Option1",{Dropdown}),"Option1 is selected")

1:{Text}=IF(SEARCHCI("order",{Text}),"'order' was found")

The 3rd Parameter: the Returned Value if the condition is FALSE (OPT.)

The last parameter specifies the value we want returned if the condition is NOT met.

1:{Text}=IF({Status}="Done","Completed","Working on it")

1:{Text}=IF(CONTAINS("Option1",{Dropdown}),"Option1 selected","Option1 not selected")

1:{Text}=IF(SEARCHCI("order",{Text}),"'order' was found","'order' was not found.")

What if no 3rd parameter is specified?

The 3rd parameter is optional. If we do not specify it, no outcome will occur if the condition is not met, as if the line was skipped.

In this example below, the Text column will remain unaltered, as no 3rd parameter. It means if it already contains text, it will contain the same text after this line is run.

1:{Text}=IF({Status}="Done"),"Completed Task")

You might have expected that the line would empty the Text field. That is not the case. If we want to empty the target Text column, we need to explicitely use the CLEAR function as 3rd parameter.

1:{Text}=IF({Status}="Done"),"Completed Task",_CLEAR()_)

For the sake of clarity, we recommend skipping the 3rd parameter

Because Advanced Formula Booster gives us the ability to use multiple lines, we suggest you avoid using the 3rd parameter, unless you are very comfortable with long lines and multiple parameters. Instead of this:

1:{Text}=IF({Status}="Done"),"Completed Task","Still working on it")

We recommend this:

1:{Text}=IF({Status}="Done"),"Completed Task")

2:{Text}=IF({Status}<>"Done"),"Still working on it")