COALESCE
COALESCE(a, b, …)
Gives the first of its values that isn't blank, such as the % complete or 0 when none is entered. It fills blanks only: an error isn't skipped, so if the value it reaches fails, the result is that error. When every value is blank, the result is blank.
Arguments
| Argument | What to give it |
|---|---|
| a, b, … | Two or more values to try in order, all the same kind: all dates, all text, and so on. A bare number among durations counts as working days. |
Examples
Formula=COALESCE([% complete], 0)
The task's % complete, with 0 for tasks that have none entered, so they add up and compare like the rest.
Formula=COALESCE([Phase], "Unassigned")
The task's phase, or "Unassigned" where it has none, so grouping by the column doesn't leave a blank group.
Formula=COALESCE(MAX(Predecessors[Finish date]), DATADATE())
The latest predecessor finish, or the data date for a task with no predecessors.
Formula on WBS rows=COALESCE(MIN(Tasks[Total float]), 0d)
On a WBS row: the lowest float under the node, and 0d for a node with no tasks.
Reach for something else when
| You want | Use |
|---|---|
| A fallback when a value fails, not when it is blank | IFERROR |
| To know whether a value is blank | ISBLANK |
| Different values on a condition | IF |
Common questions
What does "COALESCE gives Text in one place and Number in another" mean?
Every value fills the same column, so they must be the same kind. Give text a text fallback: COALESCE([Phase], "None"), not COALESCE([Phase], 0).
=COALESCE([Phase], 0)
Why didn't COALESCE replace an error?
COALESCE fills blanks, and a failed value isn't blank. Wrap the value in IFERROR as well: COALESCE(IFERROR(ONLY(Predecessors)[Finish date], BLANK()), [Start date]).