IF(condition, true_result, false_result)

The IF function is probably the simplest and most powerful calculated function. It allows you to test for a condition and return whether the test was successful or not.


condition – A statement that results in a TRUE or FALSE result.

true_result – A TEXT, NUMBER or DATE.

false_result – A TEXT, NUMBER or DATE.

Data Type Returned

The IF function can return a NUMBER, TEXT, DATE or ARRAY value, depending on the true_result and false_result returned value stated.


Assume the item has numeric attributes called “Budget” and “Cost”, and Date attributes called “plannedEnd” and “baselineEnd”.

Calculating if the item is over budget –­

IF(Cost>Budget, “Over”, “On-budget”)

Using nesting to return more values

IF(Cost<Budget*1.1), IF(Cost<(Budget*0.9), “Under”, “Within Tolerance”), “Over”)

Convert the return types to numbers and use a list to convert to text and colour –

IF(Cost<(Budget*1.1), IF(Cost<(Budget*0.9),0,1),2)

Return values –

<= 0 – “Under”

<=1 - “Within Tolerance”

>1 – “Over”