IFERROR
Returns a value, or a replacement value when the calculation fails with an error.
IFERROR(<value>, <value_if_error>)Returns: The first value if it works, otherwise the replacement value (a scalar).
What it does
IFERROR tries a calculation and, if the calculation fails with an error, returns a replacement value instead. It is handy when a conversion can fail, such as turning text into a number, and you would rather show a clear fallback than an error on the page. It catches only errors. A blank or a zero is not an error and passes through unchanged.
Syntax and arguments
- value: the calculation to try.
- value_if_error: the value to return if the calculation raises an error.
Example
A bank export stores amounts as text, and a few rows contain notes such as "pending" in the amount column. The reconciliation measure should count the amounts it can read and show a clear fallback for the rest.
Clean Amount =
IFERROR(
VALUE(BankLines[AmountText]),
0
)
Amounts that convert are returned as numbers. A note such as "pending" fails the conversion, so the row returns 0 and does not break the total. Count the failed rows separately if the reviewer needs to see them.
Example: a second, different use
A ratio that fails when the input is broken, falling back to a blank rather than an error:
Rate Value =
IFERROR(
VALUE(Rates[RateText]) / 100,
0
)
A rate stored as text, such as "8.25" for 8.25 percent, is converted to a number and divided by 100. Text that cannot be read falls back to 0. For a division of two measures, use DIVIDE instead, because DIVIDE handles zero and blank cleanly.
Things to watch
- IFERROR hides real mistakes. A wrong column name or a broken relationship can disappear behind the fallback. Test the formula without IFERROR first.
- It is slower than a plain test, because the engine must check for an error on every row.
- A blank is not an error. IFERROR does not change a blank into the fallback.
- For division, use DIVIDE, which handles zero and blank without catching other errors.
- Choose the fallback with care. A zero for a failed conversion is a number that can flow into totals without anyone noticing.
In business terms
IFERROR is useful when cleaning imported data: bank files, marketplace exports and legacy ledgers often contain text where numbers are expected. Use it to keep a close pack running, and keep a count of the rows that were replaced so the team can fix them.