Formula Errors

Article Number:040497

There are two types of formula errors. One is the error displayed when you set a formula and the other is the error displayed when you enter records.

Errors Displayed When Setting a Formula

The following section describes the errors you might get when you enter and save a formula on the App Settings page.

Error Cause and Solution
You have entered a full-width symbol "***". Use a half-width symbol "***" or check that the entered value is correct. This error message appears on the formula setting screen when you entered a full-width symbol wrongly, instead of a half-width symbol.

For example, this error is displayed in the following formula because it contains a full-width quotation mark (”).
Example: IF=(Option=”

On the other hand, this error is not displayed in the following cases.
  • When full-width symbols are used in a correct way to write a formula.
    Example 1: IF=(Option="Breakfast(Buffet)",1500,0)
  • When you enter an opening parenthesis in full-width, there is a half-width quotation (") before the full-width parenthesis and you haven’t typed a closing quotation (") yet:
    Example 2: IF=(Option="Breakfast(
* In the above Example 1 and 2, the full-width parenthesis is used in the formula to write "(buffet)".
"***" needs correction. If the "***" operator is what you want to use, enter "***" instead. This error message appears when you enter into the formula input field a specific string that is recognized as a mistyped operator. If what the error message says applies to you, replace the mistyped operator with the correct operator indicated by the message.
For details on the operators that can be used in Kintone, refer to the following page.
Description of Operators
"***" needs correction. If the "***" operator is what you want to use, enter "***" instead.
The field code (***) in the formula does not exist. This error message appears due to any one of the following:
  • The formula contains a field code that does not exist.
    • Check the field code of the field you want the formula to reference, and make sure that the correct field code is specified in the formula.
  • The formula contains full-width symbols, such as full-width ampersand symbol & and four arithmetic symbols + - * /.
    • Make sure you use half-width symbols in your formula.
  • The formula contains the field code of the "Created datetime," "Updated datetime," "Created by," or "Updated by" field that is not placed on the app form.
    • Place the field on the app form first, and then specify its field code in the formula.
Specified field (***) is the field type that cannot be used in formulas. The following items cannot be used in formulas: Record number, Label, Rich text, Text area, Attachment, Link, User selection, Department selection, Group selection, Related records, Assignee, Status, and Category. This error message appears when the formula contains the field code of a field that cannot be referenced in formulas. Check which fields can be referenced in formulas.
Reference: Fields That Can/Cannot Be Referenced in Formulas
Field references cause an endless loop. This error message appears when you specify a "Calculated" field in a formula set in another "Calculated" field. The error message appears also when "Calculated" field A is set to reference "Calculated" field A which is set to reference "Calculated" field B.
Function *** requires *** arguments. This error message appears when the number of arguments specified for the YEN, DATE_FORMAT, NOT, IF, or CONTAINS function is not enough.
Function *** requires *** or more arguments. This error message appears when the number of arguments specified for the AND, OR, or SUM function is not enough.
Function ROUND (or ROUNDUP/ROUNDDOWN) requires 1 or 2 arguments. This error message appears when the number of arguments specified for the ROUND, ROUNDUP, or ROUNDDOWN function is not enough.
Function *** does not exist. This error message appears when you specified a function which cannot be used in Kintone.
Function *** accepts up to *** arguments. This error message appears when you specified more than acceptable number of arguments in the formula set for a "Calculated" field or "Text" field.
Invalid formula format. This error message appears when the formula cannot be solved. For example:
  • Parentheses are not paired.
  • An operator is placed in an incorrect position.
  • A line break is inserted in the middle of a string enclosed in double quotation marks ("").
  • A line break is inserted in the middle of a numeric value.
Relevant function is not used for the value of the array type. As for values of array types and their usage, refer to the help. This error message appears when the formula references a "Check box" or "Multi-choice" field or when a field in a table is referenced from a field outside the table.
In both cases, a formula refers to an "array type field".

To refer to a "Check box" or "Multi-choice" field in a formula, or to refer to a string type field ("Text," "Radio button," "Drop-down," or "Lookup" field whose source is text) in a table outside from the table, use the CONTAINS function.
Note that a formula which resides outside of a table cannot refer to a "Check box" or "Multi-choice" field in the table.

To refer to a numeric type field ("Calculated" field, "Number" field, or "Lookup" field whose source is a number) in a table outside from the table, use the SUM function.

For information on how to use SUM and CONTAINS functions, refer to the following pages.
CONTAINS Function: Calculation using Check Boxes and Multi-Choices
SUM/CONTAINS Functions: Calculations in Tables

Errors Displayed When Entering Records

This section explains errors that are displayed in the field where a formula is set, when entering records.

Error Description
#CONVERT! The value cannot be converted. This error message appears when the calculation result of a "Calculated" field is a string type value. The result value in the calculated field should be either number, date and time, date, time or hour.
Use a "Text" field if the result is a string type value. For details, refer to the following page.
When the Calculation Result in the Calculated Field Is a String (Cause and Solution for #CONVERT! error)
#PRECISION! The result value exceeds the number of significant digits.
For details on how to set the significant digits, refer to the following page.
Setting the Significant Digits of Numbers and the Rounding Method
#VALUE! This error message appears when the data type of the value to be calculated is invalid or the use of an operator is incorrect.
Example 1: Adding a string to a value
Example 2: Using the + operator for concatenating strings
For example, any number you entered in a drop-down is treated as a string. It cannot be used for calculation as a number.
Reference: Data Types of Values Referenced in Formulas
#ERROR! (Not for DATE_FORMAT function) The calculation cannot be performed. For example, when dividing a number by zero (0), exponent exceeds 100 or falls below -100, etc.
#ERROR! (For DATE_FORMAT function) This error message appears in the following cases:
  • When the field value or the calculation result is either of the following date and time in UTC (Coordinated Universal Time):
    - January 1, 1000 00:00 (UNIX time: -30610224000) or earlier
    - December 31, 9999 23:59 (UNIX time: 253402300799) or later
  • When the number of characters in the formula exceeds 64 characters.
  • When invalid time zone is specified as the third argument.
    Example: DATE_FORMAT (Date_and_time, "YYYY/MM/DD a hh:mm", "Tokyo")

If a Formula Set for a "Calculated" Field Returns a String Type Value (Cause and Solution for the #CONVERT! error)

If a formula that returns a string type value is set for a "Calculated" field, the error message #CONVERT! appears. Screenshot: Setting a formula for a "Calculated" field Screenshot: The #CONVERT! error

To display the calculation result, use a "Text" field for the formula. Select Calculate automatically for the "Text" field, and then enter the formula. Screenshot: Setting a formula for a "Text" field

This time, the calculation result "Clear" is successfully displayed. Screenshot: The calculation result