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/
bella estellabella estella

To calculate the average using a Formula Field, you can follow these steps. First, identify the values you want to average. Let's assume you have a set of values: 10, 15, 20, 25, and 30. In your Formula Field, use the formula: (Value1 + Value2 + Value3 + Value4 + Value5) / TotalValues. Replace Value1, Value2, and so on, with the actual values you want to average. TotalValues represents the total number of values you are averaging, which in this case is 5. Thus, the formula would be (10 + 15 + 20 + 25 + 30) / 5. Calculating this equation gives you the average value of 20.
Now, let's integrate the VAT calculator (https://calculatorarea.com/vat-calculator/) example. Imagine you have a product priced at $100 and you want to calculate the VAT amount. Suppose the VAT rate is 10%. To find the VAT amount, you can multiply the product price by the VAT rate: $100 * 0.1 = $10. This means the VAT amount would be $10. To calculate the total cost including VAT, add the VAT amount to the original price: $100 + $10 = $110. Hence, the total cost with VAT would be $110.
kihoyop sonaerkihoyop sonaer
5 Of The Biggest Home Improvement Stores In The US And Where They Were Born  Lowe's Names Quonta (https://www.reddit.com/user/oyhjgbh567/comments/14e310z/lowes_names_quonta_que_vance_executive_vice/" target="_blank)
salvex zatminsalvex zatmin
To calculate the average using a Formula Field, you can use the SUM function to add up the values of the desired field, and then divide that sum by the total number of entries. For example, in the context of R2Parking, let's say we want to calculate the average parking duration for a set of parking sessions. We have a "Duration" field for each session, and we want to find the average duration. The Formula Field could be written as follows:
"Average Duration" = SUM(Duration) / Total_Parking (https://theparkingarea.com/)_Sessions
This Formula Field calculates the sum of all the parking durations and divides it by the total number of parking sessions, providing us with the average duration for R2Parking users. This valuable metric helps optimize parking space turnover and enhance the user experience by ensuring that parking durations are efficiently managed, aligning perfectly with the innovative spirit of R2Parking.