Outlier Detection: Formula breaks in “Interquartile Range” (IQR) calculations.

Formula breaks in Interquartile Range (IQR) calculations usually manifest as #NUM! errors, #VALUE! errors, or silent logical failures where every data point is incorrectly flagged as an outlier. These errors stem from passing small sample sizes into QUARTILE.EXC, failing to account for zero-spread distributions ($IQR = 0$), or embedding text and boolean blanks within the quartile range arguments.

Quick Answer

To resolve #NUM! errors in IQR formulas, replace QUARTILE.EXC with QUARTILE.INC (or PERCENTILE.INC), which works on small datasets without out-of-bounds errors. To prevent silent logic breaks where zero-spread data flags false outliers, verify that $Q_3 – Q_1 > 0$ before computing upper and lower fences ($Q \pm 1.5 \times IQR$).

---

The Anatomy of an IQR Outlier Model

The standard Tukey boxplot method identifies outliers by computing:

1. First Quartile ($Q_1$): 25th percentile
2. Third Quartile ($Q_3$): 75th percentile
3. Interquartile Range ($IQR$): $Q_3 – Q_1$
4. Lower Fence: $Q_1 – (1.5 \times IQR)$
5. Upper Fence: $Q_3 + (1.5 \times IQR)$

Any observation below the Lower Fence or above the Upper Fence is classified as an outlier.

       Lower Fence                     Q1       Median       Q3                     Upper Fence
            │                          │          │          │                           │
  [Outlier] ┼───────┬──────────────────┌──────────┴──────────┐───────────────────┬───────┼ [Outlier]
                    └── Lower Whisker ─┤     Interquartile   ├─ Upper Whisker ───┘
                                       │      Range (IQR)    │
                                       └─────────────────────┘

---

Common Breakdown Points and Fixes

1. The QUARTILE.EXC #NUM! Crash

Excel offers two quartile algorithms: QUARTILE.INC (inclusive) and QUARTILE.EXC (exclusive).

• QUARTILE.EXC calculates percentiles strictly between 0 and 1 non-inclusively ($\frac{k}{N+1}$).
• If your dataset has fewer than 4 values, QUARTILE.EXC(range, 1) cannot compute the 25th percentile and returns #NUM!.
• Even on larger sets, if you dynamically pass quartile multipliers outside the valid range, it terminates the calculation.

The Fix: Use QUARTILE.INC for standard business and operational outlier tracking:

=QUARTILE.INC(A2:A100, 1)  'Calculates Q1 safely on any N >= 2
=QUARTILE.INC(A2:A100, 3)  'Calculates Q3 safely on any N >= 2

2. The Zero-IQR Silent Failure Trap

When more than 50% of your data points share the exact same value (common in customer churn counts, error logs, or sensor readings with baseline zeroes):

• $Q_1$ and $Q_3$ evaluate to the same number.
• As a result, $IQR = Q_3 – Q_1 = 0$.
• The lower fence and upper fence both equal the median value ($Q_1 \pm 0$).

When this happens, any value slightly different from the median is flagged as an outlier, even if it is a completely normal observation (e.g., in a dataset of [0, 0, 0, 0, 0, 1], the value 1 is flagged, but so is any subtle variance).

The Diagnostic Check:

=IF(QUARTILE.INC(A2:A100, 3) = QUARTILE.INC(A2:A100, 1), "Zero Dispersion - IQR Invalid", "IQR Valid")

When IQR = 0, standard Tukey fences cannot be used. Switch to a mean absolute deviation (MAD) or standard z-score approach. If you encounter zero-variance crashes in standardized scores, see Z-Score Errors: Fixing the “Zero Variance” crash in standardized data sets..

3. #VALUE! Caused by Hidden Empty Strings

If your data is imported from an ERP or uses formulas like =IF(B2>0, B2, ""), the cell containing "" looks empty but is technically text.

• QUARTILE.INC silently ignores genuine blank cells.
• However, if downstream formulas compare data directly against fences using mathematical operations without checking data types, Excel can throw #VALUE!.

Verify numerical integrity in your target column:

=COUNT(A2:A100) = COUNTA(A2:A100)

If FALSE, you have text or hidden empty strings mixed into your numeric data.

---

Step-by-Step: Constructing a Break-Proof Outlier Detector

Use this self-contained formula pattern in Column B (adjacent to numeric data in A2:A100) to flag outliers without breaking on small samples or non-numeric cells:

=LET(
    data, $A$2:$A$100,
    current_val, A2,
    clean_n, COUNT(data),
    q1, QUARTILE.INC(data, 1),
    q3, QUARTILE.INC(data, 3),
    iqr, q3 - q1,
    lower_fence, q1 - (1.5 * iqr),
    upper_fence, q3 + (1.5 * iqr),
    IF(OR(NOT(ISNUMBER(current_val)), clean_n < 4), "Invalid Data",
        IF(iqr = 0, "Zero IQR - Manual Review",
            IF(OR(current_val < lower_fence, current_val > upper_fence), "Outlier", "Normal")
        )
    )
)

[Diagnostic Check: Outlier Formula Output]
       │
       ├── "Invalid Data"             ──> Cell is blank, text, or N < 4.
       ├── "Zero IQR - Manual Review" ──> >50% identical values; use MAD/Z-score.
       ├── "Outlier"                  ──> Value lies beyond 1.5 * IQR fences.
       └── "Normal"                   ──> Value falls within expected spread.

For handling ties and ranked groupings in your distributions, see Rank & Percentile: RANK.EQ vs. RANK.AVG—Handling duplicate value “Ties.”. For frequency distribution errors during binning, refer to Histogram Binning: Why Excel “Misses” data points in the Frequency function..