IF
IF(condition, then, [else])
Chooses one of two values by a TRUE/FALSE condition, such as labelling critical tasks. Only the branch taken is worked out, so a calculation that would fail on some tasks is safe in a branch those tasks never take. Leave out the else and the other rows get a blank, not FALSE as in Excel; and when the condition itself is blank, the result is blank and neither branch runs.
Arguments
| Argument | What to give it |
|---|---|
| condition | Something TRUE or FALSE: a comparison like [Total float] < 5d, or a yes/no field like [Critical]. A blank condition gives a blank. |
| then | The value for rows where the condition is TRUE. |
| else | Optional. The value for rows where it is FALSE. Leave it out for a blank. It must be the same kind of value as then: both text, both dates, and so on. |
Examples
Formula=IF([Critical], "Critical", "OK")
A label for every task: "Critical" on the critical path, "OK" elsewhere.
Formula=IF([Duration] > 0d, [Remaining duration] / [Duration], 0)
The share of the task still to do, and 0 for milestones. The division is only worked out on tasks with a duration, so milestones never divide by zero.
Formula=IF([Critical], [Finish date])
The finish date of critical tasks, and a blank on every other task, so a sort or a filter on the column only sees the critical ones.
Formula on WBS rows=IF(EVERY(Tasks[Status] = "Complete"), "Done", "Open")
On a WBS row: "Done" once every task under the node is complete.
Reach for something else when
| You want | Use |
|---|---|
| More than two outcomes, such as critical, near-critical and OK | IFS |
| A result for each of a few fixed values, such as each status | SWITCH |
| A fallback when a value is blank | COALESCE |
| A fallback when a value fails | IFERROR |
Common questions
What does "IF's condition must be TRUE/FALSE" mean?
A number or a duration isn't TRUE or FALSE, so the editor refuses it. Compare it with something instead: IF([Total float] > 0d, …).
=IF([Total float], "Has float", "No float")
What does "IF gives Text in one place and Number in another" mean?
Both branches fill one column, so they must give the same kind of value. For "nothing" in a text column write "", or leave out the else for a blank.
=IF([Critical], "Critical", 0)
Why is the result blank on some rows?
The condition was blank there. [% complete] > 50 is blank on a task with no % complete, and IF passes that on rather than treating it as FALSE. Give the blank a value first, IF(COALESCE([% complete], 0) > 50, …), or test it with ISBLANK. See blanks and errors.