Applies to
Capabilities
Who can use this capability
- Owner
- Admin
- Editor
Formula error messages
A formula returns an error when Smartsheet expects different elements from the formula than what are provided.
Who can use this?
Plans:
- Smartsheet
Permissions:
- Owner
- Admin
- Editor
Find out if this capability is included in Smartsheet Regions or Smartsheet Gov.
Users on an Enterprise plan can use AI to generate formulas. Learn more.
Below is a list of formula error messages, their causes, and the resolutions you can implement to fix the errors.
Editors with sharing permissions can create and edit formulas in unlocked cells on a sheet.
#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. Add an empty string to convert formula results to text values. For example =TODAY() + "" allows you to enter today's date into a text column.
#CALCULATING
Causes
The formula engine is still calculating on the backend.
- This can often mean you're experiencing a slowness error due to network conditions or heavy browser usage.
- It could also indicate that you're using a larger formula and/or the formula is referencing a cell that is referencing another cell via a formula or cell linking.
Resolution
Often this error corrects itself once the calculation is complete.
If the error doesn’t correct itself, slowness on a sheet can be influenced by several different factors. Some of the main factors:
- Browser speed—browser speed can be one of the main contributors of slowness when loading, navigating, and saving a sheet. In our tests, we have found Smartsheet to perform best using Google Chrome. If it's an option or when troubleshooting, try using this browser to see if it improves your response times.
- Advanced functionality—widespread use of formulas, cell-linking, and conditional formatting can contribute to slower load times. If your sheet starts to slow down, try disabling conditional formatting rules to see if this is contributing to the issue. Also, consider getting rid of formula columns or cell links that you might no longer need.
- Sheet size—as sheets get larger, performance may decrease. When appropriate, move rows to an archival sheet.
If you're utilizing cross-sheet references, you may also wish to confirm that the sheet isn’t approaching the 100,000 cell reference limit.
#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's easier to make a copy of the formula and remove cell references until the error is eliminated. This process of elimination helps you see which reference is ultimately circular.
#CONTACT EXPECTED
Cause
A formula in a Contact List field has returned a datatype other than text or contacts.
Resolution
Formulas in Contact List fields may only return text or contact datatypes. Adjust the formula to return one of those datatypes or move the formula into another type of field (Text/Number, Date, Dropdown List, Checkbox, or Symbol)
#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. Add an empty string to convert formula results to text values. For example =TODAY() + "" allows 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, "")
#IMBALANCED
Cause
The parentheses in your formula don't agree. The number of open parentheses isn't the same as the number of closed parentheses.
Resolution
Check your formula for extraneous parentheses—add additional parentheses or remove them as needed.
#INCORRECT ARGUMENT SET
Cause
This error is presented under the following circumstances:
- For functions that take two ranges: The range sizes don’t match for the function.
- The function is missing an argument.
- There is an extra function in the argument.
Resolution
Correct the range size or arguments by adding or removing arguments in the formula.
#INVALID COLUMN VALUE
Cause
The formula contains or references a data type that is inconsistent with the column type where it's inserted. For example, this MAX formula is placed in a Text/Number column and references other values in the Date column...
=MAX([Due Date]1, [Due Date]52)
...If the column that contains the formula is not a Date column type, the latest date in the range can't be returned. Learn more about the MAX Function.
Resolution
Make one of the following adjustments to your sheet:
- Type the formula in a different column, one where its type aligns with the type of value that you expect the formula to return.
- Right-click the column header at the top of the column and select Edit Column Properties. Change the column to a type that aligns with the type of value that you expect the formula to return.
- Add IFERROR to your function such that it returns a different value if it encounters the #INVALID COLUMN VALUE error, for example: =IFERROR(“Not a number!”, MAX([Due Date]1, [Due Date]5))
- Wrap your formula in a function that can convert it to the expected data type. For example: =WEEKNUMBER(MAX([Due Date]1, [Due Date]5) when placed in a Text/Number column returns a number that corresponds with the highest date’s week number out of a 52-week period. Learn more about WEEKNUMBER.
#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.
#INVALID REF
Cause
A reference name to another sheet (in curly braces) doesn’t exist as a reference to another sheet.
Resolution
Either change the name within the curly braces to be that of an already created reference to another sheet, or create a new reference to another sheet.
Learn more about referencing data from other sheets for more information on this process.
#INVALID OPERATION
Cause
Operators in a formula aren't supported in Smartsheet or are mistyped. For example, this IF statement that returns the text Low Stock if a cell contains 25 or less...
=IF(Quantity1 =< 25, "Low Stock")
...has the less than or equal to operator in the wrong order; the correct order being <= to make the formula =IF(Quantity1 <= 25, "Low Stock")
Resolution
Check all operators to make sure they're not mistyped and are supported by Smartsheet. See Create and edit Formulas for details on acceptable operators. The most common cause of this is typing <= (less than or equal to) and >= (greater than or equal to) operator combinations in the wrong order.
#INVALID VALUE
Cause
The formula contains a number outside of the range that a function's argument expects. For example, this FIND formula that finds the character H in the text string Hello has a starting position of 100 characters...
=FIND("H", "Hello", 100)
...Since the string Hello is 5 characters long, it isn't possible to have FIND start looking for the character H at 100 characters in. Only the numbers 1-5 can be accepted.
Resolution
Make sure that all number arguments are within the range that the function expects.
#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.
#NO MATCH
Causes
- The VLOOKUP or MATCH function didn't find a result.
- The number or string you are looking for is not within the range you selected when doing the search_type selected in the MATCH function, or when looking for the match_type selected in the VLOOKUP function.
Resolution
- For VLOOKUP: Adjust the match_type or lookup_table of the VLOOKUP function such that it finds a result.
- For MATCH: Adjust the range or search_type of the VLOOKUP function such that it finds a result.
#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() + "" allows 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, the maximum number of days is 1000000.
Resolution
In most cases, this error is caused by inaccurate or incomplete cell references. Check your formula and make necessary changes.
#PERMISSION ERROR
Cause
The formula references data from another sheet (in curly braces), but no one has sufficient sharing permissions to the sheet that contains the formula, as well as the source sheet containing the data being referenced.
Resolution
Make sure that at least one person is shared to both the sheet containing the formula with Editor-level permissions or greater, as well as being shared to the sheet containing the data with Viewer-level permissions or greater.
#REF
Cause
The formula references a cell which doesn't exist, possibly due to a row/column deletion.
Resolution
You need to update your formulas 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 (" ").
#UNSUPPORTED CROSS-SHEET FORMULA
Cause
The CHILDREN, PARENT, or ANCESTORS functions are referencing data from another sheet (in curly braces), which isn't supported.
Resolution
Remove the reference from that function. You can create a reference to another sheet and manually select the desired cells.
Still need help?
Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.
Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.