Catch Every Error Before It Catches You: Using IFERROR to Clean Up Variance Analysis in CRE Models
In commercial real estate (CRE) variance analysis, a single broken formula can turn a clean performance summary into a wall of error codes. IFERROR is the function that converts those noisy errors into clear, intentional signals for the underwriter—without disrupting the rest of the model.
This article explains:
The exact IFERROR syntax and how it behaves.
How to use IFERROR in a CRE variance analysis model (FY 2025 vs FY 2024).
Practical outputs like "N/A", "Check", "New Line Item", and 0.
The key risk: IFERROR wraps all error types into the same result, which can hide serious issues.
What IFERROR does
The IFERROR function evaluates a calculation and, if that calculation returns an error, displays a value you specify instead of the error.
Typical Excel errors it traps include:
In a CRE variance analysis model comparing FY 2025 vs FY 2024 for a property, IFERROR keeps the variance and variance‑percentage columns readable even when inputs are incomplete, zero, or mislinked.
Exact IFERROR syntax
The syntax is:
IFERROR(value, value_if_error)
Where:
value is the formula or expression you want to evaluate.
value_if_error is the result to return if value evaluates to any error.
Example structure in a variance model:
Base variance formula for a line item (e.g., Net Rental Income):
=FY2025 - FY2024
Variance percentage formula:
=(FY2025 - FY2024) / FY2024
Wrapped with IFERROR to control the output for invalid cases:
=IFERROR((FY2025 - FY2024) / FY2024, "N/A")
If the percentage calculation fails (for example, because FY 2024 is zero or the cell reference is broken), the result shown is "N/A" instead of an error code.
Using IFERROR in a CRE variance analysis model
Consider a variance table for a US commercial property, each row has:
FY 2024 (Actual)
FY 2025 (Actual)
Variance ($)
Variance (%)
Example: “Other Income (Parking, Storage, Laundry)” with a zero base year
A common situation in CRE is a line item that exists in the current year but not in the prior year.
For instance:
FY 2024 Other Income = 0
FY 2025 Other Income = 52,000
The natural Variance % formula:
Variance %=(52,000 - 0) / 0
will produce #DIV/0! because the denominator is zero. In the variance table, that appears as an error instead of a meaningful signal.
Option 1: Show “N/A” when the base is zero
=IFERROR((FY2025 - FY2024) / FY2024, "N/A")
Now the Variance % cell for Other Income shows "N/A" instead of #DIV/0!.This communicates that a percentage change is not meaningful when the base year is zero.
Option 2: Flag as “New Line Item”
You may want to highlight that Other Income is completely new in FY 2025 (e.g., newly introduced parking or storage fees). Combine a check on FY 2024 with IFERROR:
=IFERROR((FY2025 - FY2024) / FY2024, "New Line Item")
Using intuitive messages like “N/A”, “Check”, and “New Line Item”
The value_if_error argument can be:
Text (e.g., "N/A", "Check", "New Line Item")
A number (e.g., 0)
A blank ("")
Choosing the right output is part of designing a model that communicates clearly.
“N/A” – not applicable or not meaningful
Use "N/A" when the calculation is structurally valid, but the result isn’t meaningful, such as:
Percentage variance when the prior year is zero.
Ratios where one element is intentionally missing.
“Check” – prompt a manual review
Use "Check" when any error should trigger investigation rather than be silently ignored:
Unexpected zero in prior year for a recurring line (e.g., Real Estate Taxes, Property Insurance).
Suspicious missing values in Net Rental Income or Expense Reimbursements.
Any error now becomes a visible flag for the underwriter to review the inputs, mapping, or assumptions for that line.
“New Line Item” – highlight new revenue or expense streams
Use "New Line Item" when the base year is zero and you want to emphasize that the change reflects a new initiative, not just growth:
This is particularly useful for items like:
Newly introduced parking fees
Storage or laundry income added mid‑hold period
Newly contracted services included in operating expenses
Using 0 to stabilize roll‑ups and charts
Sometimes the goal is not to signal an error but to prevent one problematic calculation from breaking aggregates or visualizations.
If one row shows #DIV/0!, the chart or summary formula may fail.
In those situations, using 0 can make sense:
=IFERROR((FY2025 - FY2024) / FY2024, 0)
Implications:
The model continues to calculate, and charts remain functional.
The line with an invalid percentage contributes 0 to averages or totals, which may or may not be appropriate, depending on the context.
Because 0 looks like a real numeric result, it should be used carefully, especially for key performance indicators such as:
Variance % for NOI
Margin and ratio metrics (e.g., operating expense ratio)
Metrics feeding lender covenants or investment committees
Key risk and best practice
IFERROR wraps all errors into the same result: divide‑by‑zero, broken links, bad ranges, or typos all become "N/A", "Check", "New Line Item", or 0. That can hide serious logic or data issues.
To use it safely:
First build and test formulas without IFERROR.
Add IFERROR mainly in presentation layers (Variance %, summaries, charts).
Use distinct messages so the underwriter can tell missing data from true new items or potential mistakes.
Net Net
IFERROR, used thoughtfully, turns a CRE variance analysis model from a grid of cryptic error codes into a clear communication tool for underwriters and investment committees. The key is balancing cleaner outputs with transparency about where the math genuinely fails, so errors become prompts for review—not invisible assumptions.
Comments