IFS
IFS(condition, value, …)
Gives the value paired with the first condition that is TRUE, for a ladder such as critical, near-critical, OK. When no condition is TRUE the result is blank, so end with TRUE, "…" to give every other row a value. Conditions are tested in order, and a blank condition stops the search: the row is blank and the later conditions aren't tried.
Arguments
| Argument | What to give it |
|---|---|
| condition | Something TRUE or FALSE, like [Total float] <= 0d. |
| value | The result when its condition is the first TRUE one. Every value must be the same kind: all text, all numbers, and so on. |
Give them in pairs, as many as you need.
Examples
Formula=IFS([Total float] <= 0d, "Critical", [Total float] < 10d, "Near-critical", TRUE, "OK")
A float band for every task. The last pair, TRUE, "OK", catches every task the first two didn't.
=IFS([Status] = "Complete", "Done", [Critical], "Watch", TRUE, "OK")
Complete tasks first, so a finished critical task reads "Done", not "Watch". Order the pairs from the most specific to the least.
Formula on WBS rows=IFS(MIN(Tasks[Total float]) < 0d, "Negative float", MIN(Tasks[Total float]) <= 0d, "Critical", TRUE, "OK")
On a WBS row: the worst float of any task under the node, as a band.
Reach for something else when
| You want | Use |
|---|---|
| Just two outcomes | IF |
| A result for each value of one field, such as each status or phase | SWITCH |
Common questions
What does "IFS takes pairs: condition, value, condition, value, …" mean?
IFS has no separate default argument as SWITCH does. Write the default as one more pair whose condition is TRUE.
=IFS([Total float] <= 0d, "Critical", "OK")
Why is the result blank on some rows, even with a TRUE at the end?
A condition before the TRUE was blank on those rows, and a blank condition stops IFS there. [% complete] > 50 is blank on a task with no % complete. Give the blank a value inside the condition: COALESCE([% complete], 0) > 50.