+ Start a Discussion
ahasslerahassler 

Weighted Opportunity Amount Formula

I am trying to create a custom field that woud show the amount that an open opportunity adds to a weighted pipeline (ie amount*probability but only if its open otherwise it should be zero). Here's the formula I have:

 

IF(ISPICKVAL(StageName, "Prospecting")), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Qualification")), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Initial Needs Analysis")), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Proposal Response to RFP")), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Final Needs Analysis")), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Presentation Response to RFP")), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Proposal/Price Quote")), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Negotiation/Review")), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Closed Won")), ROUND(Amount * 0, 2), IF(ISPICKVAL(StageName, "Closed Lost")), ROUND(Amount * 0, 2)

 

However, it comes up with an error saying the "," before ROUND is exta. When I get rid of it, the error goes on saying each next item (round, (, Amount) is all extra. Any Thoughts?

Best Answer chosen by Admin (Salesforce Developers) 
ahasslerahassler

I was able to get a different formula to work so I haven't tried yours. But in case someone out there has a similar issue, here is the formula I got that worked:

 

IF(NOT(ISPICKVAL( StageName ,"Closed Won") || ISPICKVAL( StageName ,"Closed Lost")), Amount * Probability ,0)

All Answers

prakash_sfdcprakash_sfdc
Try this:
IF(ISPICKVAL(StageName, "Prospecting"), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Qualification"), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Initial Needs Analysis"), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Proposal Response to RFP"), ROUND(Amount * Probability, 2),
IF(ISPICKVAL(StageName, "Final Needs Analysis"), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Presentation Response to RFP"), ROUND(Amount * Probability, 2),
IF(ISPICKVAL(StageName, "Proposal/Price Quote"), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Negotiation/Review"), ROUND(Amount * Probability, 2), IF(ISPICKVAL(StageName, "Closed Won"), ROUND(Amount * 0, 2), IF(ISPICKVAL(StageName, "Closed Lost"), ROUND(Amount * 0, 2),ROUND(Amount * 0, 2)
)
)
)
)
)
)
)
)
)
)
prakash_sfdcprakash_sfdc
As a short version you can also use this:
IF( OR(ISPICKVAL(StageName, "Prospecting"), ISPICKVAL(StageName, "Qualification"),ISPICKVAL(StageName, "Initial Needs Analysis"),ISPICKVAL(StageName, "Proposal Response to RFP"), ISPICKVAL(StageName, "Final Needs Analysis"), ISPICKVAL(StageName, "Presentation Response to RFP"), ISPICKVAL(StageName, "Proposal/Price Quote"), ISPICKVAL(StageName, "Negotiation/Review")), ROUND(Amount * Probability, 2), ROUND(Amount * 0, 2))
ahasslerahassler

I was able to get a different formula to work so I haven't tried yours. But in case someone out there has a similar issue, here is the formula I got that worked:

 

IF(NOT(ISPICKVAL( StageName ,"Closed Won") || ISPICKVAL( StageName ,"Closed Lost")), Amount * Probability ,0)

This was selected as the best answer