The If - Then - Else function is another special case that can be used for numeric, text, and currency formulas. The If - Then - Else clauses should all be text-based for text formulas, based on numbers for numeric formulas, and based on currency for currency formulas. In some cases, a numeric output is accepted for text-based If -Then - Else formulas, and vice-versa.

The following table shows examples of the If -Then -Else formula based on numbers and based on text:

 Formula Example Description If If([num1]>[num2],[num1],[num2]) This is an If-Then-Else test for greater than. Also works for less than (<), less than or equal to (<=), greater than or equal to (>=), or equals (=). This works for both text and numeric formula types.Example returns the max of the two numbers. If If([text1].Contains([text2]),[text1],[text2]) This If-Then-Else test only works as a text formula. The “If” clause must be a text method with a true or false output like Equals, StartsWith, etc. Example returns text1 if text1 contains text2. Otherwise, text2 is returned.

If -Then -Else statements can also be nested as shown in the following example.

If([num1]>[num2], If([text1].Contains([text2]),[text1],[text2]), [text1])

Note that this will only work as a text formula. If you change the outputs to numbers as in the following example:

If([num1]>[num2],If([text1].Contains([text2]),[num1],[num2]),[num1])

The formula will work both as a text, numeric, and currency formula. All outputs must either be text, numeric, or currency for the formula to work.