+ 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.
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) )
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.

Shrey Sharma