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
Sienna Luard 3Sienna Luard 3 

formula field based on Forecast Category

We need a formula field that does the following:

If the Forecast Category is Pipeline - Amount x 10%
If the Forecast Category is Best Case - Amount x 60%
If the Forecast Category is Committed - Amount x 90%

I have come up with the following formula:

IF( ISPICKVAL(  ForecastCategoryName ,"Pipeline" ) , Amount *0.10)+ 
IF( ISPICKVAL(  ForecastCategoryName ,"Pipeline" ) , Amount *0.60)+ 
IF( ISPICKVAL(  ForecastCategoryName ,"Pipeline" ) , Amount *0.90)

I get the following error.

Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 2
Best Answer chosen by Sienna Luard 3
Raj VakatiRaj Vakati
Use this
  
IF(ISPICKVAL(  ForecastCategoryName ,"Pipeline" ) ,Amount *0.10,
IF( ISPICKVAL(  ForecastCategoryName ,"Best Case" ),Amount *0.60,
IF( ISPICKVAL(  ForecastCategoryName ,"Pipeline" ),Amount *0.90,
0
)))

 

All Answers

Andrew GAndrew G
Your IF statement require format of IF test_expression, TRUE result , ELSE False result.  

Since it is nested, each ELSE statement will require another IF statement

And then a default result, in this case 0.

Try something like:

IF( ISPICKVAL(  ForecastCategoryName ,"Pipeline" ) , Amount *0.10),  
    IF( ISPICKVAL(  ForecastCategoryName ,"Best Case" ) , Amount *0.60),  
       IF( ISPICKVAL(  ForecastCategoryName ,"Pipeline" ) , Amount *0.90) ,
            0 
      )
   )
)

Regards
Andrew
Sienna Luard 3Sienna Luard 3
Thanks so much for your quick response Andrew.

I am getting the following error with your formula.

 Error: Syntax error. Extra ','

I do not see an extra , .

What am I missing?
Raj VakatiRaj Vakati
Use this
  
IF(ISPICKVAL(  ForecastCategoryName ,"Pipeline" ) ,Amount *0.10,
IF( ISPICKVAL(  ForecastCategoryName ,"Best Case" ),Amount *0.60,
IF( ISPICKVAL(  ForecastCategoryName ,"Pipeline" ),Amount *0.90,
0
)))

 
This was selected as the best answer
Sienna Luard 3Sienna Luard 3
You are the best Andrew!

That works perfectly.

Thansk again for your help. (-: