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
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.
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.
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:
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, https://forums.caspio.com/index.php/topic/3632-calculated-field-to-compare-dates-or-return-text/.