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.