Apex Code Development You need to sign in to do that
Don't have an account?
Exclude null values from average
I'm using the below formula field to attempt to average numeric responses, but exclude null values from the average. The formula is still counting the null value in the division. Any ideas?
Wrong average: 5+5+4+NULL = 14/4 = 3.5
Right average: 5+5+4+NULL = 14/3 = 4.67
(IF(ISNULL(CS1_Q1_answer__c),0,CS1_Q1_answer__c)+
IF(ISNULL(CS1_Q2_answer__c),0,CS1_Q2_answer__c)+
IF(ISNULL(CS1_Q3_answer__c),0,CS1_Q3_answer__c)+
IF(ISNULL(CS1_Q4_answer__c),0,CS1_Q4_answer__c))
/
(IF(ISNULL(CS1_Q1_answer__c),0,1)+
IF(ISNULL(CS1_Q2_answer__c),0,1)+
IF(ISNULL(CS1_Q3_answer__c),0,1)+
IF(ISNULL(CS1_Q4_answer__c),0,1))
Wrong average: 5+5+4+NULL = 14/4 = 3.5
Right average: 5+5+4+NULL = 14/3 = 4.67
(IF(ISNULL(CS1_Q1_answer__c),0,CS1_Q1_answer__c)+
IF(ISNULL(CS1_Q2_answer__c),0,CS1_Q2_answer__c)+
IF(ISNULL(CS1_Q3_answer__c),0,CS1_Q3_answer__c)+
IF(ISNULL(CS1_Q4_answer__c),0,CS1_Q4_answer__c))
/
(IF(ISNULL(CS1_Q1_answer__c),0,1)+
IF(ISNULL(CS1_Q2_answer__c),0,1)+
IF(ISNULL(CS1_Q3_answer__c),0,1)+
IF(ISNULL(CS1_Q4_answer__c),0,1))