#CALC! errors occur when Excel's calculation engine encounters a scenario it does not currently support. Here's how to address specific #CALC! errors:

Excel can't calculate an array within an array. The nested array error occurs when you try to input an array formula that contains an array. To resolve the error, try removing the second array.

For example, =MUNIT({1,2}) is asking Excel to return a 1x1 array, and a 2x2 array, which isn't currently supported. =MUNIT(2) would calculate as expected.

Nested array #CALC! error

Arrays can only contain numbers, strings, errors, Booleans, or linked data types. Range references aren't supported. In this example, =OFFSET(A1,0,0,{2,3}) will cause an error.

#CALC! error - Array Contains Ranges

To resolve the error, remove the range reference. In this case, =OFFSET(A1,0,0,2,3) would calculate correctly.

Excel can't return an empty set. Empty array errors occur when an array formula returns an empty set. For example, =FILTER(C3:D5,D3:D5<100) will return an error because there are no values less than 100 in our data set.

#CALC! error - Empty Array

To resolve the error, either change the criterion, or add the if_empty argument to the FILTER function. In this case, =FILTER(C3:D5,D3:D5<100,0) would return a 0 if there are no items in the array.

Custom functions that refer to more than 10,000 cells cannot be calculated in Excel for the web, and will produce this #CALC! error instead. To fix, open the file in a desktop version of Excel. For more information, see Create custom functions in Excel.

This function performs an asynchronous operation but has unexpectedly failed. Try again later.

A LAMBDA function behaves a little differently than other Excel functions. You can't just enter it into a cell. You must call the function by adding parentheses to the end of your formula and passing the values to your lambda function. For example:

  • Returns the #CALC error:     =LAMBDA(x, x+1) 

  • Returns a result of 2:     =LAMBDA(x, x+1)(1)

For more information, see LAMBDA function.

The error message and drop-down list for the Lambda error

This error occurs when Excel's calculation engine encounters an unspecified calculation error with an array. To resolve it, try rewriting your formula. If you have a nested formula, you can try using the Evaluate Formula tool to identify where the #CALC! error is occurring in your formula.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Dynamic arrays and spilled array behavior

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

What affected your experience?

Thank you for your feedback!

×