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
Nate BakerNate Baker 

Prevent division by 0

I have a formula field that averages survey responses and excludes null values. How can I prevent from dividing by 0 if all the responses are null? (If the second part of this formula = 0)

(IF(ISBLANK(CS1_Q1_answer__c),0,CS1_Q1_answer__c)+
IF(ISBLANK(CS1_Q2_answer__c),0,CS1_Q2_answer__c)+
IF(ISBLANK(CS1_Q3_answer__c),0,CS1_Q3_answer__c)+
IF(ISBLANK(CS1_Q4_answer__c),0,CS1_Q4_answer__c))

/

(IF(ISBLANK(CS1_Q1_answer__c),0,1)+
IF(ISBLANK(CS1_Q2_answer__c),0,1)+
IF(ISBLANK(CS1_Q3_answer__c),0,1)+
IF(ISBLANK(CS1_Q4_answer__c),0,1))
Best Answer chosen by Nate Baker
Ketankumar PatelKetankumar Patel
Try this..
  
(
IF(ISBLANK(CS1_Q1_answer__c),0,CS1_Q1_answer__c)+
IF(ISBLANK(CS1_Q2_answer__c),0,CS1_Q2_answer__c)+
IF(ISBLANK(CS1_Q3_answer__c),0,CS1_Q3_answer__c)+
IF(ISBLANK(CS1_Q4_answer__c),0,CS1_Q4_answer__c)
)/(
IF(
	(
	IF(ISBLANK(CS1_Q1_answer__c),0,1)+
	IF(ISBLANK(CS1_Q2_answer__c),0,1)+
	IF(ISBLANK(CS1_Q3_answer__c),0,1)+
	IF(ISBLANK(CS1_Q4_answer__c),0,1)
	) = 0,
	
	1,

	IF(ISBLANK(CS1_Q1_answer__c),0,1)+
	IF(ISBLANK(CS1_Q2_answer__c),0,1)+
	IF(ISBLANK(CS1_Q3_answer__c),0,1)+
	IF(ISBLANK(CS1_Q4_answer__c),0,1)
  )
)

 

All Answers

James LoghryJames Loghry
The easiest way (although there are probably others) is to wrap it in an IF block. 
IF(
    AND(
        ISBLANK(CS1_Q1_Answer__c)
        ,ISBLANK(CS1_Q2_Answer__c)
        ,ISBLANK(CS1_Q3_Answer__c)
        ,ISBLANK(CS1_Q4_Answer__c)
    )
    ,''
    ,(
        (IF(ISBLANK(CS1_Q1_answer__c),0,CS1_Q1_answer__c)+
        IF(ISBLANK(CS1_Q2_answer__c),0,CS1_Q2_answer__c)+ 
        IF(ISBLANK(CS1_Q3_answer__c),0,CS1_Q3_answer__c)+
        IF(ISBLANK(CS1_Q4_answer__c),0,CS1_Q4_answer__c))
        /
        (IF(ISBLANK(CS1_Q1_answer__c),0,1)+
        IF(ISBLANK(CS1_Q2_answer__c),0,1)+
        IF(ISBLANK(CS1_Q3_answer__c),0,1)+
        IF(ISBLANK(CS1_Q4_answer__c),0,1))
    )
)
Ketankumar PatelKetankumar Patel
Try this..
  
(
IF(ISBLANK(CS1_Q1_answer__c),0,CS1_Q1_answer__c)+
IF(ISBLANK(CS1_Q2_answer__c),0,CS1_Q2_answer__c)+
IF(ISBLANK(CS1_Q3_answer__c),0,CS1_Q3_answer__c)+
IF(ISBLANK(CS1_Q4_answer__c),0,CS1_Q4_answer__c)
)/(
IF(
	(
	IF(ISBLANK(CS1_Q1_answer__c),0,1)+
	IF(ISBLANK(CS1_Q2_answer__c),0,1)+
	IF(ISBLANK(CS1_Q3_answer__c),0,1)+
	IF(ISBLANK(CS1_Q4_answer__c),0,1)
	) = 0,
	
	1,

	IF(ISBLANK(CS1_Q1_answer__c),0,1)+
	IF(ISBLANK(CS1_Q2_answer__c),0,1)+
	IF(ISBLANK(CS1_Q3_answer__c),0,1)+
	IF(ISBLANK(CS1_Q4_answer__c),0,1)
  )
)

 
This was selected as the best answer
Nate BakerNate Baker
Those both work. Thank you.