You need to sign in to do that

Don't have an account?

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:

Please help!!!

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:**Please help!!!

Raj VakatiUse this one pls

## All Answers

Raj VakatiUse this one pls

Rick SF AdminAw, 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 2Hi @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 LeoDivide 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/