+ Start a Discussion
Soo Kim 16Soo Kim 16 

Formula help is needed

Hi,
I need your help with the point calculation below.  How do I modify the formula so that when StageName is
Discovery should be 1 Point,
Consensus 1.5,
Evluation 1.5,
Justification 1.5,
Negotiate 1.5 ,
Won 1.5.
Currently, it's defaulting points on above stages to 1. 

Below formula is working great but needed add additional stages to 1.5 points.

IF(ISPICKVAL([Opportunity].StageName , "7 Closed Lost") && ISPICKVAL([Opportunity].Lost_Reason__c , "Duplicate Opp"), 0, 
IF(ISPICKVAL([Opportunity].StageName , "7 Closed Lost" )&&
ISPICKVAL([Opportunity].Count__c ,"<200"), 1,

IF(ISPICKVAL([Opportunity].StageName , "7 Closed Lost") &&
ISPICKVAL([Opportunity].Count__c , "200 - 999"),1.5,

IF(ISPICKVAL([Opportunity].StageName , "7 Closed Lost" )&&
ISPICKVAL([Opportunity].Count__c ,"1000+") ,2,

IF(!ISPICKVAL([Opportunity].StageName , "7 Closed Lost" )&&
ISPICKVAL([Opportunity].Count__c ,"<200"), 1,

IF(!ISPICKVAL([Opportunity].StageName , "7 Closed Lost") &&
ISPICKVAL([Opportunity].Count__c , "200 - 999"),1.5,

IF(!ISPICKVAL([Opportunity].StageName , "7 Closed Lost" )&&
ISPICKVAL([Opportunity].Count__c ,"1000+") ,2,

IF(AND(!ISPICKVAL([Opportunity].StageName , "7 Closed Lost" ),
 !ISPICKVAL([Opportunity].Lost_Reason__c , "Duplicate Opp"),
ISPICKVAL([Opportunity].Count__c ,"")), 1,
0))))))))
Soo Kim 16Soo Kim 16
Stage:
Discovery 
Consensus  
Evluation  
Justification  
Negotiate  
Won  
Loss

If Loss is selected then following picklist is required:
Duplicate Opp
Other
karthikeyan perumalkarthikeyan perumal
Hello, 

Try below formula. if its not works let me know. 
IF(ISPICKVAL([Opportunity].StageName , "7 Closed Lost") && ISPICKVAL([Opportunity].Lost_Reason__c , "Duplicate Opp"), 0, 
IF(ISPICKVAL([Opportunity].StageName , "7 Closed Lost" )&&
ISPICKVAL([Opportunity].Count__c ,"<200"), 1,

IF(ISPICKVAL([Opportunity].StageName , "7 Closed Lost") &&
ISPICKVAL([Opportunity].Count__c , "200 - 999"),1.5,

IF(ISPICKVAL([Opportunity].StageName , "7 Closed Lost" )&&
ISPICKVAL([Opportunity].Count__c ,"1000+") ,2,

IF(!ISPICKVAL([Opportunity].StageName , "7 Closed Lost" )&&
ISPICKVAL([Opportunity].Count__c ,"<200"), 1,

IF(!ISPICKVAL([Opportunity].StageName , "7 Closed Lost") &&
ISPICKVAL([Opportunity].Count__c , "200 - 999"),1.5,

IF(!ISPICKVAL([Opportunity].StageName , "7 Closed Lost" )&&
ISPICKVAL([Opportunity].Count__c ,"1000+") ,2,

IF(ISPICKVAL([Opportunity].StageName , "Discovery"),1,

IF(ISPICKVAL([Opportunity].StageName , "Consensus"),1.5,

IF(ISPICKVAL([Opportunity].StageName , "Evluation"),1.5,

IF(ISPICKVAL([Opportunity].StageName , "Justification"),1.5,

IF(ISPICKVAL([Opportunity].StageName , "Negotiate"),1.5,

IF(ISPICKVAL([Opportunity].StageName , "Won"),1.5,

IF(AND(!ISPICKVAL([Opportunity].StageName , "7 Closed Lost" ),
 !ISPICKVAL([Opportunity].Lost_Reason__c , "Duplicate Opp"),
ISPICKVAL([Opportunity].Count__c ,"")), 1,
0))))))))))))))

Hope this will helps you.

Thanks
karthik
 
Soo Kim 16Soo Kim 16
How do I fix this error message?
The formula expression is invalid: Syntax error. Missing '='
 
karthikeyan perumalkarthikeyan perumal
replace your code with updated code below
 
IF(ISPICKVAL(StageName , "7 Closed Lost") && ISPICKVAL(Lost_Reason__c , "Duplicate Opp"), 0, 
IF(ISPICKVAL(StageName , "7 Closed Lost" )&&
ISPICKVAL(Count__c ,"<200"), 1,

IF(ISPICKVAL(StageName , "7 Closed Lost") &&
ISPICKVAL(Count__c , "200 - 999"),1.5,

IF(ISPICKVAL(StageName , "7 Closed Lost" )&&
ISPICKVAL(Count__c ,"1000+") ,2,

IF(!ISPICKVAL(StageName , "7 Closed Lost" )&&
ISPICKVAL(Count__c ,"<200"), 1,

IF(!ISPICKVAL(StageName , "7 Closed Lost") &&
ISPICKVAL(Count__c , "200 - 999"),1.5,

IF(!ISPICKVAL(StageName , "7 Closed Lost" )&&
ISPICKVAL(Count__c ,"1000+") ,2,

IF(ISPICKVAL(StageName , "Discovery"),1,

IF(ISPICKVAL(StageName , "Consensus"),1.5,

IF(ISPICKVAL(StageName , "Evluation"),1.5,

IF(ISPICKVAL(StageName , "Justification"),1.5,

IF(ISPICKVAL(StageName , "Negotiate"),1.5,

IF(ISPICKVAL(StageName , "Won"),1.5,

IF(AND(!ISPICKVAL(StageName , "7 Closed Lost" ),
 !ISPICKVAL(Lost_Reason__c , "Duplicate Opp"),
ISPICKVAL(Count__c ,"")), 1,0
))))))))))))))

Hope this will work now. 

Thanks
karthik
 
Soo Kim 16Soo Kim 16
Hi, I noticed that when opportunity stage is closed lost and duplicate opportunity it should default to 0 but when there is VM count, it adds VM point. Can we fix this? Soo Kim
karthikeyan perumalkarthikeyan perumal
Hello, 

Updated this formula.. i made a changes in 34th line Checking not null of Count__c picklist. 
 
IF(ISPICKVAL(StageName , "7 Closed Lost") && ISPICKVAL(Lost_Reason__c , "Duplicate Opp"), 0, 
IF(ISPICKVAL(StageName , "7 Closed Lost" )&&
ISPICKVAL(Count__c ,"<200"), 1,

IF(ISPICKVAL(StageName , "7 Closed Lost") &&
ISPICKVAL(Count__c , "200 - 999"),1.5,

IF(ISPICKVAL(StageName , "7 Closed Lost" )&&
ISPICKVAL(Count__c ,"1000+") ,2,

IF(!ISPICKVAL(StageName , "7 Closed Lost" )&&
ISPICKVAL(Count__c ,"<200"), 1,

IF(!ISPICKVAL(StageName , "7 Closed Lost") &&
ISPICKVAL(Count__c , "200 - 999"),1.5,

IF(!ISPICKVAL(StageName , "7 Closed Lost" )&&
ISPICKVAL(Count__c ,"1000+") ,2,

IF(ISPICKVAL(StageName , "Discovery"),1,

IF(ISPICKVAL(StageName , "Consensus"),1.5,

IF(ISPICKVAL(StageName , "Evluation"),1.5,

IF(ISPICKVAL(StageName , "Justification"),1.5,

IF(ISPICKVAL(StageName , "Negotiate"),1.5,

IF(ISPICKVAL(StageName , "Won"),1.5,

IF(AND(!ISPICKVAL(StageName , "7 Closed Lost" ),
 !ISPICKVAL(Lost_Reason__c , "Duplicate Opp"),
NOT(ISBLANK(TEXT(Count__c)))), 1,0
))))))))))))))

Hope this will help you 

Thanks
karthik