function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Philip GulanPhilip Gulan 

How to create a formula field to calcualte average currency with blank fields?

I need to write a formula to calculate the average currency of multiple fields which some can be with blank fields.
LakshmanLakshman
You can try this-
(Field1__c + Field2__c + Field 
3__c)/ (if(field1__c>0,1,0) + if(field2__c>0,1,0) + if( field3__c>0,1,0) )
Shrey_ShrmaShrey_Shrma
Hello Philip

Lakshman's answer is good but you should use the below one otherwise if there are no values in all the three fields you will get an error in the value of the field that will not be appropriate.

The below formula works for most of the worst cases like all the fields empty or currency in negative.
  
If((Field1__c + Field2__c + Field3__c) <= 0,0,((Field1__c + Field2__c + Field3__c)/( if(field1__c>0,1,0) + if(field2__c>0,1,0) + if( field3__c>0,1,0) )
)
Please feel free to ask if need any help in it.

Hope it helps. :)

Please mark the answer as Best Response to help others also find the right solution.

Regards
Shrey Sharma