2

I have a requirement in which I need to create a Formula(Currency) field that will multiply a field of type Formula(Currency) with a picklist containing`percent value.

I am stating fields name & values it contains also I am stating the Formula that I created which is throwing error.

  1. Total Revenue - This field is a Formula(Currency)
  2. Win Probability - Picklist with Values 20%, 50%, 80%, 100%

Formula that I tried building was:

Total_Revenue__c * VALUE( TEXT(Win_Probability__c ))
1
  • I get #Error! as my result in the Formula Field
    – guest610
    Commented Feb 26, 2016 at 10:36

2 Answers 2

1

@Oles Malkov answer works perfectly fine

But requires editing the case statement in the Formula, whenever you add new values to the Picklist and might also look really big if the Picklist has so many values

This generic formula will solve that redundancy:

 Total_Revenue__c * (VALUE(SUBSTITUTE(TEXT(Win_Probability__c),'%',''))/100)
8
  • I tried both the formula as suggested by @Oles & karthikselva. I still get the same error.
    – guest610
    Commented Feb 26, 2016 at 11:06
  • Can you paste the exact value in Picklist ? you might have space between 20 and % like 20 % Commented Feb 26, 2016 at 11:09
  • These are mine picklist Values: 20%, 50%, 80%, 100%. With no spaces.
    – guest610
    Commented Feb 26, 2016 at 11:10
  • Can you try the updated code in my answer ? I added VALUE around Total_Revenue__c Commented Feb 26, 2016 at 11:12
  • Incorrect parameter type for function 'VALUE()'. Expected Text, received Number (Related field: Formula). I got this error when i prefixed Total Revenue with VALUE()
    – guest610
    Commented Feb 26, 2016 at 11:16
2

You can't convert 20% to a number in such way, because it contains non-numeric character - %. Can you try this one:

Total_Revenue__c *  CASE( Text(Win_Probability__c ) , '20%', 0.2, '50%', 0.5, '80%', 0.8, '100%', 1, 0)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .