function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MadChemistMadChemist 

Problem with CASE formula....

I am pretty new to using Sforce custom fields, but pretty experienced with computer jargon....Can someone tell me why this error (Error: Incorrect number of parameters for function CASE(). Expected 6, received 7) is happening in the following formula and how I can fix it.
 
Code:
CASE( Frequency__c ,  IF( ISPICKVAL( Frequency__c , "Low") , 1, 0) , 1, IF( ISPICKVAL( Frequency__c , "Moderate") , 2, 0), 2,IF( ISPICKVAL( Frequency__c , "High") , 3, 0),3)  *  CASE( Severity__c , IF( ISPICKVAL( Severity__c  , "Low") , 2, 0), 2, IF( ISPICKVAL( Severity__c  , "Moderate") , 3, 0) , 3,IF( ISPICKVAL( Severity__c  , "High") , 5, 0),5)

 
What I am trying to do is have a picklist value return a number so I can multiply two picklist values together to obtain a number.  The picklist values are Low, Moderate, High for both frequency and severity.  I want to multiply the values to obtain an Impact Score....thanks!
KaushikKaushik
Hi
Most probably the problem is that you are using 2 conditional statements if and ispickval.
 
In a CASE statement the ISPICKVAL is not required and you need to have a default value too.
Trying doing this
CASE( Frequency__c ,  "Low" , 1, "Moderate",2,"High",3,0)  *  CASE( Severity__c ,"Low",2, "Moderate", 3,"High",5,0)
 
The default is 0(if no condition satisfied)...So plz make sure ,the pick-lists are required values
 
 
Hope this helps you
 
Thanks
jtoy530jtoy530

I followed the suggested revised formula for a similar formula field and am getting a different error. How can I fix the error?

CASE (Pipeline_Size_Range__c, "$0 - $50k", $50k, "$50k - $250k",$50k,"$250k - $500k",$250k,"$500k - $1M",$500k,"$1M - $3M",$1M,0)

Error: Field $500k does not exist. Check spelling.

I have copied and pasted all field values from SFDC.

Wanted results:

If picklist value = $0k-$50k, then returned value = $50k

If picklist value = $50k-$250k, then returned value = $50k

If picklist value = $250k-$500k, then returned value = $250k

....and so on and so on...I want to calculate the lowest value of the range (with the exception of $0-$50k). Should I be using a different formula?

 
KaushikKaushik
Hi
Since the values are not numbers in this case...you have to include them in quotes
CASE (Pipeline_Size_Range__c, "$0 - $50k", "$50k", "$50k - $250k","$50k","$250k - $500k","$250k","$500k - $1M","$500k","$1M - $3M","$1M",0)


If double quotes " " don't work try single quotes ' '

CASE (Pipeline_Size_Range__c, "$0 - $50k", '$50k', "$50k - $250k",'$50k',"$250k - $500k",'$250k',"$500k - $1M",'$500k',"$1M - $3M",'$1M',0)


Kaushik
jtoy530jtoy530

I tried both options and still getting an error.

Error: Incorrect parameter for function CASE(). Expected , received NumberError: Incorrect parameter for function CASE(). Expected , received Number

I also tried the following formula:

CASE (Pipeline_Size_Range__c, "$0 - $50k", "50,000", "$50k - $250k","50,000","$250k - $500k","250,000","$500k - $1M","500,000","$1M - $3M","1,000,000",0)

I'm trying to return a currency value, so I thought I'd try taking out the "$" and "k". no luck. Getting the same error even when I try to return a text or number value.

Any other suggestions? Thanks!
KaushikKaushik
    Hi,
The problem is ,when you use the currency return type you shouldn't  use " ," in the numbers.Just use a regular number.
Make sure you create a formula field with Currency as the return type and use the formula that is below

CASE ( Pipeline_Size_Range__c , "$0 - $50k", 50000, "$50k - $250k",50000,"$250k - $500k",250000,"$500k - $1M",500000,"$1M - $3M",1000000,0)



Kaushik
jtoy530jtoy530

Found the solution! We were close. Thanks for your help.

 

Case(Pipeline_Size_Range__c,"$0 - $50k",50000,"$50k - $250k",50000,"$250k - $500k",250000,"$500k - $1M",500000,"$1M - $3M",1000000,0)