You need to sign in to do that

Don't have an account?

alvatse

# 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.

Jeff 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 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)

)

alvatseThanks a ton!! It worked perfectly! dxfilo@mac.comI 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 TullosI 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

)

KCarmenI 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?