Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Rick SF AdminRick SF Admin 

How to calculate the Average using a Formula Field

I have a formula field (Avg__c) that calculates the average of 4 custom fields (Base_Q1__c, Base_Q2__c, etc....) .

Formula: 
(IF(ISNULL(Base_Q1__c),Null,Base_Q1__c) + IF(ISNULL(Base_Q2__c),Null,Base_Q2__c)+IF(ISNULL(Base_Q3__c),Null,Base_Q3__c) +IF(ISNULL(Base_Q4__c),Null,Base_Q4__c))
/
(IF(ISNULL(Base_Q1__c),Null,1) + IF(ISNULL(Base_Q2__c),Null,1)+IF(ISNULL(Base_Q3__c),Null,1) + IF(ISNULL(Base_Q4__c),Null,1)
The problem is that I'm trying to establish a “running average” but it only calculates the average if all 4 custom fields (Base_Q1__c...) contain a value. If one of the 4 fields are blank, then the formula field will not calcuate anything.  I need it to calculate the average no matter how value are displayed or blank. 

WITH ALL 4 VALUES - AVG Calculates:
User-added image

WITHOUT ALL 4 VALUES - No AVG Calculation:
User-added image
 
Please help!!!
 
Best Answer chosen by Rick SF Admin
Raj VakatiRaj Vakati
Use this one pls
  
(IF(ISNULL(Base_Q1__c),0,Base_Q1__c) + IF(ISNULL(Base_Q2__c),0,Base_Q2__c)+IF(ISNULL(Base_Q3__c),0,Base_Q3__c) +IF(ISNULL(Base_Q4__c),0,Base_Q4__c))
/
(IF(ISNULL(Base_Q1__c),0,1) + IF(ISNULL(Base_Q2__c),0,1)+IF(ISNULL(Base_Q3__c),0,1) + IF(ISNULL(Base_Q4__c),0,1)

 

All Answers

Raj VakatiRaj Vakati
Use this one pls
  
(IF(ISNULL(Base_Q1__c),0,Base_Q1__c) + IF(ISNULL(Base_Q2__c),0,Base_Q2__c)+IF(ISNULL(Base_Q3__c),0,Base_Q3__c) +IF(ISNULL(Base_Q4__c),0,Base_Q4__c))
/
(IF(ISNULL(Base_Q1__c),0,1) + IF(ISNULL(Base_Q2__c),0,1)+IF(ISNULL(Base_Q3__c),0,1) + IF(ISNULL(Base_Q4__c),0,1)

 
This was selected as the best answer
Rick SF AdminRick SF Admin
Aw, replaced "NULL,1" with 0,1. 

Thank you!
Enda MullaneyEnda Mullaney
@raj...im trying to use this but if any of my fields are blank or null i dont want the N used in the average...by looking at the formula i would have hoped it was already doing this but in fact i have 5 fields with only 4 having numbers yet the formula is still dividing by 5..any ideas
Ryan Woolsey 2Ryan Woolsey 2
Hi @Enda Mullaney,

Not sure if you still have this issue, but when I followed the instructions I encountered the same problem. Under the "Blank Field Handling" section when you're editing the Formula Field, change "Treat blank fields as 0" to "Treat blank fields as blanks". This resolved that issue.
French LeoFrench Leo
Divide the total number of Opportunities by the total number of sales reps. The unique option handles this because it finds the total number of unique Opportunity Owners, so we don't have to hardcode this value into the report. See my web here: https://premiumconstructionpro.com/service/exterior-water-proofing-service/