While performing calculations in a DataPage using Calculated Fields or Aggregate functions, there could be a number of arithmetic issues contributing to the message, Error in formula, but there are common situations when your formula is causing the error.

Division by zero:

Example: “A/B” works fine, except when B is zero and you get the error message.

To solve this situation you can use the following code which catches the zeros before they cause an error. Just make sure to change A and B with your own field names.

Case

When [@field:B]= '0'

Then null

Else

([@field:A]/[@field:B])

End

Blank values:

Example: A+B works fine, except when the field value is blank.

To solve this situation you can use the following code to handle blanks.

IsNull([@field:A],0)+IsNull([@field:B],0)

Non-numeric values:

Example: A+B works fine, except when the field value is text such as N/A.

To solve this situation you can use the following code to skip the text values in the calculation.

IsNumeric([@field:A])+IsNumeric([@field:B])

Table or Field name change:

If table or field name is changed in the table design, it won’t be updated in the formula which results in “error in formula” error message. You should update the table or field name reference manually in the formula.

Mismatch data type:

If fields in the formula do not have the same data type.
If result expressions in Case formula do not have the same data type. Example:

CASE input_expression
WHEN when_expression
THEN result_expression
ELSE result_expression
END

To solve this situation make sure the fields and result expressions have the same data type or use CAST function to convert the data type:

CAST (result_expression AS datatype)

Most common data types are char, varchar, binary, date, float, real, int.

See an example in our community forum, http://forums.caspio.com/index.php/topic/3632-calculated-field-to-compare-dates-or-return-text/.