 ShowAll Questionssorted byDate Posted Rick 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: WITHOUT ALL 4 VALUES - No AVG Calculation:  Best Answer chosen by Rick SF Admin Raj 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)``` Raj 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 Admin
Aw, replaced "NULL,1" with 0,1.

Thank you! Enda 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 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.