IFERROR
IFERROR(value, fallback)
Gives the value, or the fallback on rows where working out the value failed, such as a division by zero on a milestone. It catches errors only: a blank isn't an error, so a blank value stays blank.
Arguments
| Argument | What to give it |
|---|---|
| value | A calculation that can fail on some rows. |
| fallback | What to give where it fails. The same kind of value: a number for a number, a date for a date. |
Examples
Formula=IFERROR([Remaining duration] / [Duration], 0)
The share of the task still to do, with 0 for milestones, whose zero duration would fail the division.
Formula=IFERROR(ONLY(Predecessors)[Task code], "several")
The code of the task's one predecessor, or "several" where it has none or more than one.
Formula=IFERROR(ONLY(Predecessors)[Finish date], [Start date])
The finish of the task's single predecessor, or the task's own start where there isn't exactly one.
Reach for something else when
| You want | Use |
|---|---|
| A fallback for blanks, not errors | COALESCE |
| To flag the failing rows, not replace them | ISERROR |
| To keep the calculation off the rows where it fails | IF, which works out only the branch taken |
Common questions
Why didn't IFERROR fill in the tasks with no % complete?
They are blank, and a blank isn't an error. Use COALESCE: COALESCE([% complete], 0).
What does "IFERROR gives Date in one place and Text in another" mean?
The fallback fills the same column as the value, so it must be the same kind. For a missing date use a date, or BLANK().
=IFERROR(ONLY(Predecessors)[Finish date], "none")
Why can't I write IFERROR(x / 0, …)?
The editor refuses a division by a written 0, since it would fail on every row: "This divides by 0, which fails on every row. Divide by something else, or use IFERROR(…, fallback)".