Exceljet

Quick, clean, and to the point

How to fix the #CALC! error

Excel formula: How to fix the #CALC! error
Summary 

The #CALC! error occurs when a formula runs into an calculation error with an array. The #CALC! error is a "new" error in Excel, introduced with dynamic arrays. It will not appear in older versions of Excel.

Explanation 

With the introduction of Dynamic Arrays in Excel, formulas there is more emphasis on arrays. The #CALC! error occurs when a formula runs into an calculation error with an array. The #CALC! error is a "new" error in Excel, introduced with dynamic arrays. It will not appear in older versions of Excel.

Empty array

An empty array can trigger a #CALC! error, and this is the most common reason you may see a #CALC! error in a worksheet, especially when using the FILTER function. This is because FILTER returns a #CALC! error when no values meet criteria – in other words, FILTER returns an empty array.

For example, in the screen below, the FILTER function is set up to filter the source data in B5:D11. However, the formula is asking for all data in the group "x", which doesn't exist. The result is a #CALC! error:

#CALC error example 1 - before fix

=FILTER(B5:D11,B5:B11="x") // no such group, empty array

Fix #1 - adjust filter logic

One option to fix this error is is to adjust the filter criteria to return valid results. In the screen below, the formula has adjusted to filter on group "A", and the formula works normally:

#CALC error example 1 - fix 1

=FILTER(B5:D11,B5:B11="a") // group "a" exists; no empty array

Fix #2 - set is_empty argument

Another option is to provide a "not found" value to return when no results are returned. In the screen below FILTER returns "No results" instead of an error:

#CALC error example 1 - fix 2 - set is empty argument

=FILTER(B5:D11,B5:B11="x","No results") // message instead of error

Note: Microsoft documentation mentions other cases that may cause #CALC! errors, notably nested arrays, and unsupported arrays. However, I have not been able to reproduce the error with the examples provided. If you have examples of formulas that throw #CALC! errors, please let me know.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.