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
SkeeterSkeeter 

Formula to calculate average but not include null values

I'm trying to create a formula to calculate the average of 5 questions (1-5 values), but not include any null values.  I tried the following, but it doesn't seem to work.  If I null out all the answers and leave one with 4, it displays the average as 0.00.  

Any help is greatly appreciated. 
(IF(ISNULL(Answer2__c),0,Answer2__c)+IF(ISNULL(Answer3__c),0,Answer3__c)+IF(ISNULL(Answer4__c),0,Answer4__c)+IF(ISNULL(Answer6__c),0,Answer6__c)+IF(ISNULL(Answer7__c),0,Answer7__c))
/
(IF(ISNULL(Answer2__c),0,1)+IF(ISNULL(Answer3__c),0,1)+IF(ISNULL(Answer4__c),0,1)+IF(ISNULL(Answer6__c),0,1)+IF(ISNULL(Answer7__c),0,1))

 
William TranWilliam Tran
In general, it looks fine to me.  be sure you are testing with answer 2,3,4,6,7.  Also make sure these an numbers not strings.  IF you enter a value for 1,5,, or higher than 7 you will get 0.00 as described.  Also I add an addition if the bottom is 0, give it 1 so you don't have dividing by 0 issues.

Thx
  
(IF(ISNULL(Answer2__c),0,Answer2__c)+
IF(ISNULL(Answer3__c),0,Answer3__c)+
IF(ISNULL(Answer4__c),0,Answer4__c)+
IF(ISNULL(Answer6__c),0,Answer6__c)+
IF(ISNULL(Answer7__c),0,Answer7__c))
/
IF(
(IF(ISNULL(Answer2__c),0,1)+
IF(ISNULL(Answer3__c),0,1)+
IF(ISNULL(Answer4__c),0,1)+
IF(ISNULL(Answer6__c),0,1)+
IF(ISNULL(Answer7__c),0,1)),0,1)

 
SkeeterSkeeter
I figured out what it was.  My formula was correct, I need to check the 'Treat blank fields as blanks'.
William TranWilliam Tran
Lilranger,

Glad it all worked out.  

Also, as a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you. 

This will help keep the forum clean and help future users determine what answers are useful
and what answer was the best in resolving the user's issue. 

Thanks