The #CALC! error
A recent error, specific to Excel 365's array formulas. Nine times out of ten, it is a FILTER that found nothing.
What it means
#CALC! means the array calculation engine could not produce a result: an empty array, or an array of arrays.
Where it comes from
- FILTER finds no row and has no third argument.
- An empty array passed to a function expecting at least one value.
- A LAMBDA function returning an array where a single value is expected.
Fixing it
- Give FILTER its third argument:
=FILTER(A2:C100,B2:B100="North","No rows").
Tools that help
- Explain a formula: says what FILTER returns when nothing matches.
Frequently asked questions
- What is the difference between #CALC! and #N/A?
- #N/A comes from a lookup that cannot find a value; #CALC! from an array calculation that produces no result, such as a FILTER with no match.