function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
alvatsealvatse 

Getting #Error! in a formula field

Hi, I created the following custom fields in Contact object to calculate the average survey socre:

- Sum of Scores (field name: Sum_of_Scores__c)

- # of surveys (field name: Surveys_filled_out__c)

- Ave Score: this is a formula: ROUND(Sum_of_Scores__c / Surveys_filled_out__c,0)

 

However, I'm getting'#Error!' in the Ave Score field when the value of the formula returns 0. I'd like it to show '0' instead.

 

Does anyone know how to work around that? Thanks.

 

 

Best Answer chosen by Admin (Salesforce Developers) 
Jeff TalbotJeff Talbot

One reason that #Error! can be produced in a formula result is when a formula tries to divide by 0. In your formula, if there are 0 surveys filled out, it will cause the divide by zero "#Error!".

 

So test for 0 in your formula. Try something like this:

 

IF(Surveys_filled_out__c = 0,

    0,

    ROUND(Sum_of_Scores__c / Surveys_filled_out__c,0)

   )

All Answers

Jeff TalbotJeff Talbot

One reason that #Error! can be produced in a formula result is when a formula tries to divide by 0. In your formula, if there are 0 surveys filled out, it will cause the divide by zero "#Error!".

 

So test for 0 in your formula. Try something like this:

 

IF(Surveys_filled_out__c = 0,

    0,

    ROUND(Sum_of_Scores__c / Surveys_filled_out__c,0)

   )

This was selected as the best answer
alvatsealvatse
Thanks a ton!! It worked perfectly!
dxfilo@mac.comdxfilo@mac.com

I am having a simlar issue.  Is there a way to return a zero (0) instead of the #Error! if it is divided by 0?
Kevin TullosKevin Tullos
I had the same issue and I used an if Statement.  First Identify the denominator that is giving you the error.  You can do this with some simple algebra.

In the case below, C cannot equal 0, else it will be undefined.  So I would write the formula as follows.

IF(C <> 0,
((A+B) / (C * 8.25)),
0
)
KCarmenKCarmen
I am trying to figure this out as well and it's kickin my behind. Should be simple.
With this formula, I'm getting an error: (( Total_Amount__c /(Contract_Term_mo__c)) / Seats__c)
So I changed it to this but it's not working:IF(Seats__c = 0,0 (( Total_Amount__c /(Contract_Term_mo__c)) / Seats__c))
GAH! Anyone?