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
    Feb 26 '16 at 10:36
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
    Feb 26 '16 at 11:06
  • Can you paste the exact value in Picklist ? you might have space between 20 and % like 20 % Feb 26 '16 at 11:09
  • These are mine picklist Values: 20%, 50%, 80%, 100%. With no spaces.
    – guest610
    Feb 26 '16 at 11:10
  • Can you try the updated code in my answer ? I added VALUE around Total_Revenue__c Feb 26 '16 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
    Feb 26 '16 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)

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.