Formula Error Messages

A formula will return an error when Smartsheet expects different elements from the formula than what are provided. Below is a list of formula error messages, their causes, and the resolutions you can implement to fix the errors.

Additional Resources

This article provides information on the errors you might see when creating formulas in Smartsheet. Check out our other articles for details on creating formulas and using functions:

Error messages:

#BLOCKED #INVALID DATA TYPE
#BOOLEAN EXPECTED #NA RESULT
#CIRCULAR REFERENCE #NESTED CRITERIA
#DATE EXPECTED #NUMBER EXPECTED
#DIVIDE BY ZERO #OVERFLOW
#ERROR #REF
#IMBALANCED #UNPARSEABLE
#INCORRECT ARGUMENT SET  

#BLOCKED

Cause

The calculation is blocked because at least one of the cells referenced by the formula has an error.

Resolution

Determine which cell referenced by this formula contains an error, which will be more descriptive of the problem.


#BOOLEAN EXPECTED

Cause

The formula is in a specific type of column (Date, Number, Symbol) and the returned value is of a different type.

Resolution

Either move the formula to a different column, or convert the result to the appropriate type. Formula results may be converted to text values by adding an empty string. For example =TODAY() + "" will allow you to enter today's date into a text column.


#CIRCULAR REFERENCE

Cause

The formula references itself. The circular reference may be direct where the reference is in the formula text itself, or indirect where this formula references a cell which then references back to this cell.

Resolution

Determine which reference is circular. Indirect references can be many levels deep. Sometimes it is easiest to make a copy of the formula and remove cell references until the error is eliminated. This process of elimination will help you see which reference is ultimately circular.


#DATE EXPECTED

Cause

The formula is in a specific type of column (Date, Number, Symbol) and the returned value is of a different type.

Resolution

Either move the formula to a different column, or convert the result to the appropriate type. Formula results may be converted to text values by adding an empty string. For example =TODAY() + "" will allow you to enter today's date into a text column.


#DIVIDE BY ZERO

Cause

Your formula attempted to divide an amount by zero (e.g. 4 / 0 provides a divide by zero error).

Resolution

Use an IFERROR to return a different value in case the formula were to divide by zero. For example,

=IFERROR(Value4 <> 0, 100/Value4, "")


#ERROR

Cause

You cannot have a formula in a contact list column.

Resolution

Move your formula to a different column, or change the column type to text/number.


#IMBALANCED

Cause

The parentheses in your formula do not agree. The number of open parentheses is not the same as the number of closed parentheses.

Resolution

Check your formula for extraneous parentheses—add additional parenthesis or remove them as needed.


#INCORRECT ARGUMENT SET

Cause

This error is presented under the following circumstances:

  1. For functions that take two ranges: The range sizes don’t match for the function.
  2. The function is missing an argument.
  3. There is an extra function in the argument.

Resolution

Correct the range size or arguments, adding or removing arguments in the formula.


#INVALID DATA TYPE

Cause

The formula contains or references an incompatible data type, such as =INT(“Hello”)

Resolution

Make sure the formula references the correct data type.


#NA RESULT

Cause

MATCH function didn’t find the value.

Resolution

Double-check the MATCH function’s value (first argument) and ensure the value is typed correctly and in the lookup range.


#NESTED CRITERIA

Cause

You’ve nested criteria in a SUMIF or COUNTIF function.

Resolution

Use SUMIFS or COUNTIFS instead, as you can include multiple criteria without nesting.


#NUMBER EXPECTED

Cause

The formula is in a specific type of column (Date, Number, Symbol) and the returned value is of a different type.

Resolution

Either move the formula to a different column, or convert the result to the appropriate type. Formula results may be converted to text values by adding an empty string. For example

=TODAY() + "" will allow you to enter today's date into a text column.


#OVERFLOW

Cause

The formula encountered or computed a value which exceeds the supported numeric range. For numeric values the range is -9007199254740992 through 9007199254740992. For WORKDAY calculations, maximum number of days is one million.

Resolution

In most cases, this error is caused by inaccurate or incomplete cell references. Check your formula and make necessary changes.


#REF

Cause

The formula references a cell which does not exist, possibly due to a row/column deletion.

Resolution

Your formulas will need to be updated manually.


#UNPARSEABLE

Cause

The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons, such as misspelling, incomplete operators, using the wrong case for a column name, or using single quotes instead of double quotes.

Resolution

Ensure that all column names are spelled correctly in cell references, operators are being used correctly, and any text strings in the formula are surrounded by double quotes (“ “).