 ShowAll Questionssorted byDate Posted Philip Gulan

# Why formula is not recognizing blank currency feilds?

I have written a short formula to calculate the average value of multiple currency fields, so that if there is a blank value, it shouldn't be calculated.
Logically everything seems fine, but it is not recognizing the the blank fields

Any help is apperciated.

(ValueA__c +  ValueB__c + ValueC__c ) /
(
IF(ISBLANK(ValueA__c), 0, 1) +
IF(ISBLANK(ValueB__c), 0, 1) +
IF(ISBLANK(ValueC__c), 0, 1)
)

The problem is that wether the fileds are blank or filled the values are divided by 3 which it should be divided only based on the filled fields Best Answer chosen by Philip Gulan Bryan James
So I think because they are currency fields their default value is 0, even if it is not showing it.

When I recreated your formula I was returning the same results you were, in that it was always dividing by 3.
When I changed the formula to check if the values were greater than 0 rather than blank it worked as I had hoped.

(ValueA__c +  ValueB__c + ValueC__c ) /
(
IF(ValueA__c > 0, 1, 0) +
IF(ValueB__c > 0, 1, 0) +
IF(ValueC__c > 0, 1, 0)
)    Nagendra (Salesforce Developers) Hi Phillip,

One assumption Go to the formula field edit it and scroll down to the last and see if you set Treat blank fields as zeroes if you did set it to "Treat blank fields as zeroes"

Set it to : Treat blank fields as blanks

Note:
If your formula references any number, currency, or percent fields, specify what happens to the formula output when their values are blank. When you say blank as 0's then technically the field is never blank​

Hope this solves.

Kindly mark this post as solved if the information help's so that it gets removed from the unanswered queue and becomes a proper solution which results in helping others who are really in need of it.

Best Regards,
Nagendra.P Bryan James
So I think because they are currency fields their default value is 0, even if it is not showing it.

When I recreated your formula I was returning the same results you were, in that it was always dividing by 3.
When I changed the formula to check if the values were greater than 0 rather than blank it worked as I had hoped.

(ValueA__c +  ValueB__c + ValueC__c ) /
(
IF(ValueA__c > 0, 1, 0) +
IF(ValueB__c > 0, 1, 0) +
IF(ValueC__c > 0, 1, 0)
)  This was selected as the best answer Philip Gulan
Thank you