1. Calculation functions
  2. All Functions
  3. Logical Functions
  4. IF THEN ELSE

Tests a Boolean argument and returns one of two results based on whether it is true or false.

For example, you can use the IF THEN ELSE function to ensure that calculations only apply to values that meet certain criteria.

Syntax

IF Boolean argument THEN Result 1 ELSE Result 2

Arguments

ArgumentData typeDescription
Boolean argumentBoolean

The Boolean argument that determines whether the function returns Result 1 or Result 2.

If TRUE, the function returns Result 1. If FALSE, the function returns Result 2.

Result 1Number, Boolean, date, time period, list, text

The value to return if the Boolean argument resolves to TRUE.

Must be the same data type as Result 2.

Result 2Number, Boolean, date, time period, list, text

The value to return if the Boolean argument resolves to FALSE.

Must be the same data type as Result 1.

The IF THEN ELSE function returns a result of the same data type as the Result 1 and Result 2 arguments.

Syntax example

IF Time Settings.'Current Version' THEN Revenue ELSE 0

Additional information

Alternative syntax

You can also use the syntax used for some other spreadsheet applications. For example:

IF(a > b, x, y)

This formula is equivalent to the formula below:

IF a > b THEN x ELSE y

Boolean results

If you need to return a Boolean result based on certain criteria, it's not always necessary to use the IF THEN ELSE function. Instead you can use operators to create a statement and Anaplan returns a value of TRUE or FALSE based on the cell value. For example, you could use:

a > b

This formula is equivalent to the formula below:

IF a > b THEN TRUE ELSE FALSE 

Multiple instances of IF THEN ELSE

You can use IF THEN ELSE multiple times in a formula. However, try to avoid this where possible as such formulas can be difficult to maintain. If your formula requires 10 or more instances of IF THEN ELSE, consider using the LOOKUP function.

Constraints

Match the data type of Result arguments

Both the Result 1 and Result 2 arguments must have the same data type. The target line item must also have the same data type.

Excel equivalent

IF

Examples

General examples of all data types

This example uses two modules. The first module, Values module, contains several line items that contain two values of each data type:

Number 1100
Number 2200
Boolean 1
Boolean 2
Date 11/1/2021
Date 22/2/2021
Time period 1Jan 21
Time period 2Feb 21
List 1London
List 2Paris
Text 1Good
Text 2Bad

The Result 1 and Result 2 arguments use the values above in the formulas in the second module below. The second module also contains a line item used for the Boolean argument of the IF THEN ELSE function.


Scenario 1Scenario 2
Boolean argument

Number example

IF Boolean argument THEN Values module.'Number 1' ELSE Values module.'Number 2'

100200

Boolean example

IF Boolean argument THEN Values module.'Boolean 1' ELSE Values module.'Boolean 2'


Date example

IF Boolean argument THEN Values module.'Date 1' ELSE Values module.'Date 2'

1/1/20212/2/2021

Time period example

IF Boolean argument THEN Values module.'Time period 1' ELSE Values module.'Time period 2'

Jan 21Feb 21

List example

IF Boolean argument THEN Values module.'List 1' ELSE Values module.'List 2'

LondonParis

Text example

IF Boolean argument THEN Values module.'Text 1' ELSE Values module.'Text 2'

GoodBad

Examples using operators

You can use operators such as AND, NOT, or OR in the Boolean argument to create more detailed conditions for the IF THEN ELSE function.


Region ARegion BRegion C
Value 111010
Value 210110
IF Value 1 AND Value 2 >= 10 THEN "A" ELSE "B"BBA
IF Value 1 NOT Value 2 >= 10 THEN "A" ELSE "B"BAA
IF Value 1 OR Value 2 >= 10 THEN "A" ELSE "B"AAA

Examples using the ISBLANK and ISNOTBLANK functions

The ISBLANK and ISNOTBLANK functions return a Boolean value of true if a cell is blank or not blank respectively. You can use these functions in the Boolean argument to easily determine whether to apply a formula or not, based on whether another cell contains a value.


Patrice PlannerBrianna BuilderAnthony Analyst
Base Salary80,00095,000110,000
Enrolled in health plan?
YesYes

Adjusted salary after health plan deductions

IF ISNOTBLANK(Enrolled in health plan?) THEN (Base Salary * 0.96) ELSE Base Salary

80,00091,200105,600