I have a picklist called state and each state has its own percentage rate. I created a percentage formula field where if the user select the State X, this percentage field should display the rate for that state. I wrote the following formula:

IF(ISPICKVAL(Estado__c , "São Paulo"), "4",
IF(ISPICKVAL(Estado__c , "Rio de Janeiro"),"4",
IF(ISPICKVAL(Estado__c , "Minas Gerais"), "4",
IF(ISPICKVAL(Estado__c , "Goiás"), "0", "Others"))))

Error: Formula result is data type (Text), incompatible with expected data type (Percent).

What's wrong? Can you help me with the eight formula for it?

  • 4
    First thing to try is to drop the quotes around the numbers i.e. use 4 instead of "4" etc and change "Others" to 0. Using CASE would be a bit clearer too. – Keith C Nov 9 '15 at 14:46
  • Hi Keith! Thanks for your answer. I changed the "4" to 0.04 and "others" to 0.00 , and the formula worked. So, the formula is, just a small sample: IF(ISPICKVAL(Estado__c , "São Paulo"), 0.04, IF(ISPICKVAL(Estado__c , "Rio de Janeiro"), 0.04, IF(ISPICKVAL(Estado__c , "Minas Gerais"), 0.04, IF(ISPICKVAL(Estado__c , "Rio Grande do Sul"), 0.03, IF(ISPICKVAL(Estado__c , "Amazonas"), 0.03, IF(ISPICKVAL(Estado__c , "Goiás"), 0.00, 0.00)))))))))))))))))))))))))) – Barbara Moraes Nov 9 '15 at 15:29
  • 4
    Good. Still think you should be using CASE though! – Keith C Nov 9 '15 at 15:31
  • Case instaed of spickval? – Barbara Moraes Nov 9 '15 at 17:01
  • 2
    Yeah - see techbusinessman's answer. – Keith C Nov 9 '15 at 17:42

You could do something similar to:

  "São Paulo", 4,
  "Rio de Janeiro", 4,
  "Minas Gerais, 4
  "Goiás", 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

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.