Skip to main content Stream TDX Bengaluru on Salesforce+. Start learning the critical skills you need to build and deploy trusted autonomous agents with Agentforce. Register for free.
Topics#FormulasPergunta de RR H
RR H (PST) perguntou em #Formulas
3 de ago. de 2018, 21:25

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:

How to calculate the Average using a Formula Field

WITHOUT ALL 4 VALUES - No AVG Calculation:

User-added image

 

Please help!!!

 
8 respostas
  1. Rajamohan Vakati (Amazon)
    3 de ago. de 2018, 21:40
    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)

     
Carregando
Escrever uma resposta...
0/9000