ShowAll Questionssorted byDate Posted
Soo 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 16
Stage:
Discovery
Consensus
Evluation
Justification
Negotiate
Won
Loss

If Loss is selected then following picklist is required:
Duplicate Opp
Other
karthikeyan 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 16
How do I fix this error message?
The formula expression is invalid: Syntax error. Missing '='

karthikeyan 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 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 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
))))))))))))))```