Dynamic Arrays
Fix #CALC! & #SPILL! Dynamic Array Errors (Empty FILTER Guard)
Handle an empty FILTER result without confusing zero totals with undefined averages, and distinguish empty results from blocked spill output.
An empty result, invalid input and blocked output are different problems
If no row satisfies the include mask, FILTER without an if_empty argument returns #CALC! in this example. A text fallback is useful for a display list. It is a placeholder value, not an actual empty array, so downstream counts and calculations must account for it.
A zero fallback is appropriate for the sum of no qualifying values. It is not an average of an empty set: AVERAGE(FILTER(...,0)) would report a misleading zero. The separate average formula below checks for matches and reports No qualifying values instead.
The if_empty argument does not catch source errors, an invalid include mask or blocked spill output. Use numeric amounts and threshold; the input check below identifies missing or nonnumeric entries before running the list formula.
For a blocked spill, inspect the intended output cells and merged ranges. Move the formula to an empty area or move obstructing data after preserving it; do not blindly clear cells. Formula-only checks here verify values and array dimensions, not visual spill placement in a populated workbook.
| Formula Pattern (Threshold D2 = $60,000) | Result When 0 Rows Match | Dashboard Safety |
|---|---|---|
| FILTER(A2:A5, B2:B5 >= D2) | #CALC! error! | Breaks downstream charts & totals |
| FILTER(A2:A5, B2:B5 >= D2, "No Qualifying Deals") | "No Qualifying Deals" | Clean human-readable empty state |
| SUM(FILTER(B2:B5, B2:B5 >= D2, 0)) | 0 | Numeric-safe $0 total! |
Empty-Safe Text List with FILTER [if_empty]
Supplies "No Qualifying Deals" as argument 3 to replace #CALC! whenever zero rows pass B2:B5 >= D2.
=FILTER(A2:A5, B2:B5 >= D2, "No Qualifying Deals")Empty-Safe Numeric Aggregation SUM(FILTER(..., 0))
Passes numeric 0 as [if_empty] so SUM returns 0 instead of #CALC! when no deals qualify.
=SUM(FILTER(B2:B5, B2:B5 >= D2, 0))Validate numeric inputs
Check the four amounts and threshold before using the display-list formula. This example requires all four amounts.
=IF(OR(COUNT(B2:B5)<>4,NOT(ISNUMBER(D2))),"Invalid inputs","Ready")Do not invent an average for an empty result
Return an explicit empty-state label when no row qualifies. The denominator is the number of matched numeric values.
=IF(OR(COUNT(B2:B5)<>4,NOT(ISNUMBER(D2))),"Invalid inputs",IF(COUNTIF(B2:B5,">="&D2)=0,"No qualifying values",AVERAGE(FILTER(B2:B5,B2:B5>=D2,0))))Try the example
Use the original values or change them to test your understanding.