A specific scenario where IsMissing function will be useful is when we combine it with IfElse. IfElse allows us to define the condition and two possible returns depending on whether the condition is evaluated as TRUE or FALSE. It is important that both results need to be of the same type, so either both “if true” and “if false” have to return a string or both have to return numeric values.
The previous paragraph provides one example of this function. In this example, we include specific text depending on the value in a given row.
It is also possible to nest multiple IfElse instances in one expression. If we have more than two potential outcomes, IfElse can then be included as the “if false” part of the formula.
A common scenario may be to verify if a given value exists at all - this is where combining IfElse with IsMissing comes in handy. See how we can convert missing values to “no data” information. Note that the “if false” part of the statement uses FormattedValue rather than Value - since we have a string (“no data”) in the “if true” part, the other part also needs to be a string.
IfElse + IsMissing combination can also be helpful if we use a more compound formula that requires all of its parts to have some value. Let’s say that we need to calculate the difference between two years. It is only possible if value exists in both years. We may check if both periods exist and by using IfElse we can decide what should happen in the cell under the specific condition. See the example below - note how the formula fails for rows where one of the periods has no data unless we wrap it up in additional IsMissing checks.
