[SalesForce] How to write formula field that returns a percentage to show amount at or below 100%

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.

Best Answer

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.

Related Solutions

[SalesForce] Percentage Formula Field based on Picklist values

You could do something similar to:

Case(TEXT(Estado__c),
  "São Paulo", 4,
  "Rio de Janeiro", 4,
  "Minas Gerais, 4
  "Goiás", 0,
  0
)

In my opinion this becomes more readable than multiple IF statements. You can then imbed this into the current formula you are using.

Here is the link to documentation on the case field: Salesforce Help: Formula - Case

[SalesForce] Create a formula text field to display prior value or text field

No, there is no formula for this; formula functions like ISCHANGED and PRIORVALUE only work in the context when a field value is transitioning from one value to another (i.e. during a DML update). Old values are not stored in the database (other than field history, which can't be used in a formula anyways).

The best you could do is create a text field called something like "Field Previous Value," then set a Process Builder/Workflow Rule that copies the prior value of the field to this second text field when the field changes. As a workflow example: "Evaluate when the following formula is true: ISCHANGED(FieldName)" with a workflow field update "Previous Field Value set to formula PRIORVALUE(FieldName).

Best Answer

Related Solutions

[SalesForce] Percentage Formula Field based on Picklist values

[SalesForce] Create a formula text field to display prior value or text field

Related Topic