I would like to set up a field that calculates the average of 6 other fields, but excludes any field that has a value of 0.
Thanks for your help.
This is the formula I have:
( G1_Soil_Tipping_Fee__c + G2_Soil_Tipping_Fee__c + G3_Soil_Tipping_Fee__c + G4_Soil_Tipping_Fee__c + G5_Soil_Tipping_Fee__c ) / (IF(ISBLANK( G1_Soil_Tipping_Fee__c),0,1) + IF(ISBLANK( G2_Soil_Tipping_Fee__c),0,1) + IF(ISBLANK( G3_Soil_Tipping_Fee__c ),0,1) + IF(ISBLANK( G4_Soil_Tipping_Fee__c ),0,1) + IF(ISBLANK( G5_Soil_Tipping_Fee__c ),0,1))
When I check "Treat Blanks as Zeros" it divides by 5, even if there is nothing in one of the fields. When i check "Treat Blanks as Blanks" it does not produce a value. I think "Treat Blanks as Blanks" needs to be checked, but not sure why it's not working.
For example if I have values in 3 of the fields, I want the total of those fields to be divided by 3 not 5.
