Review common errors that can occur with formulas and how to fix them.
Available in: both Salesforce Classic and Lightning Experience
Available in: All Editions
“#Error!” displays for a formula field whenever an error occurs while calculating the value of a formula. To resolve the error, check your formula.
Is the formula dividing by zero? If so, check if the denominator of your expression is zero and provide an alternative value. For example, the following campaign formula field is blank if the number of opportunities is zero:
Is the formula calculating a value larger than the maximum value of the current type? If so, you can append L to numeric values to make them Long so the intermediate products will be Long and no overflow occurs. For example, the following example shows how to correctly compute the amount of milliseconds in a year by multiplying Long numeric values.
Long MillsPerYear = 365L * 24L * 60L * 60L * 1000L;
Long ExpectedValue = 31536000000L;
Is the formula calculating the square root of a negative number? If so, use an IF function similar to the one above to check if the value is a positive number.
Is the formula calculating the LOG of a negative number? If so, use an IF function similar to the one above to make sure that the number is positive.
Is the formula using the VALUE function with text that contains special characters? For examples of special characters, see Formula Operators and Functions.
Make sure the formula does not contain a HYPERLINK function within a text function, such as LEFT( HYPERLINK("http://MYCOMPANY.ORG ", "MYCOMPANY ") , 5).
Is the formula disabled or referencing a disabled formula field? Salesforce disables formula fields when they are deleted and they remain disabled after they are restored. To enable disabled formula fields, edit and save the field. For more information on deleted custom fields and restoring them, see Manage Deleted Custom Fields.
“#Too Big!” displays if your formula output is over 18 digits. When this happens, check your formula for calculations that could result in more than 18 digits. Avoid multiplying large numbers, raising a large number to a power, or dividing by a very small number.
CASE functions return an error whenever any of the expressions return an error, regardless of which one should be returned. For example, CASE(Field__c,"Partner", "P", "Customer", "C", LEFT(Field__c, -5)) returns an error even if the value of the field is “Partner” or “Customer” because the last statement is illogical.
Prevent division by zero errors by including an IF function that determines if the value of a field is zero. For example, IF(Field__c =0,0, 25/Field__c).