Apex Code Development You need to sign in to do that
Don't have an account?
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
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
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)
)
All Answers
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
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)
)