0

Your input is appreciated, I'm creating a formula field called consumption is a result of dividing two numeric fields (Modified Usage, and Range) that will return a percentage of usage for example. If the Modified Usage is 80 and the Range is 100 then I want it to show 80%.

`IF( Modified_Usage__c / (Value(TEXT(Range__c))+0)> 1,1, (Modified_Usage__c /(Value(text (Range__c))+0)))`

Definitions: Modified Usage is a numeric field The range is a picklist hence the Value(Text... Consumption is the percentage of consumption based on the formula calculation.

I am not getting any Syntax errors before saving the formula field, but I get an #ERROR! once I add the field to the page layout for any and all entries. Suggestions are welcome. I've looked through the documentation and know I'm not dividing by zero for the error to occur.

  • Why are you adding 0 to the divider? Also, if range can be converted to a Number field on the custom field definition side, you could avoid using Value() and TEXT() (which I assume are causing the issues here) – Brian Miller Apr 3 '19 at 5:47
1

I realize you say you're not dividing by zero, but just to be sure, let's check for blank values first, then we'll optimize:

IF(
  ISPICKVAL(Range__c,""), null,
  MIN(1,Modified_Usage__c/VALUE(SUBSTITUTE(TEXT(Range__c),",",""))))

Here, we see if range is empty, and if so, return a blank value, otherwise we take the lesser of 1 or Modified Usage divided by Range as a value.

Please note that there's a formula option that says "Treat blanks as zero" or "Treat blanks as null." You must make sure you're using the "Treat blanks as zero" option to avoid errors if Modified Usage is null.

You can change this behavior if you like by returning 0 or another value when Range is empty by replacing null with whatever reasonable value you'd like.

  • your suggestion is helpful, but I'm still receiving the error message I've changed the field type for Modified Usage to text hoping that would eliminate the error but it did not. Still getting same error message #Error! – Xperez Apr 3 '19 at 7:32
  • @Xperez can the values in Range__c actually be converted to numbers? For example, "1-Hello" is not a valid number, it would need to be just "1". – sfdcfox Apr 3 '19 at 19:41
  • The value of Range__c can be anywhere from 250 to 50,000. – Xperez Apr 4 '19 at 7:39
  • 1
    @Xperez You can't have punctuation like commas when you use VALUE. They need to be removed. See my edit. – sfdcfox Apr 4 '19 at 12:40
  • That makes sense now. Your solution worked, and it met my requirements. Thank you! – Xperez Apr 4 '19 at 23:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.