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
Matthew LohMatthew Loh 

Multiple IF statements in a formula field to calculate expected revenue

Hi all

My org does basically all of our sales from Partners, and each partner has a different pricing strategy.

I'd like to be able to generate forecasts/dashboard based on expected revenue of each opportunity.

This expected revenue will be a formula field returning numbers/currency. I cannot use the standard Opportunity Amount field because it is based on Product price * quantity, and we do not have products. We have pricing agreements with all these different clients based on the Partner that they originated from.

As such, each partner has a different pricing strategy. For each lead, we would have their total turnover, but the actual revenue to my organisation is always a percentage of that turnover plus a fee multiplied by the number of transactions they carry out

I was thinking of a formula field where it look something like this, but I'm not sure what syntax to use. 

**Note: there is a custom field for Parent Account mapped over from Lead stage, so every opportunity will have a value in this field from a restricted picklist**
 

IF Parent Account = Partner A, then (1% * annual_turnover__c + $0.1 * annual_transactions__c)
IF Parent Account = Partner B, then (2% * annual_turnover__c + $0.2 * annual_transactions__c)
IF Parent Account = Partner C, then (3% * annual_turnover__c + $0.3 * annual_transactions__c)
 

You get the idea.

Any idea on how I can implement this?  I found some discussion on this on the forums, but they required the field to return a simple text value upon several IF conditions. I wasn't sure if that was suitable, because I need it to perform a different calculation upon each condition.

Thanks!

Best Answer chosen by Matthew Loh
Matthew LohMatthew Loh
Forgot to mention, I'll probably be making at least 8 to 10 IF conditions for all our different pricing strategies.

We also have 2 types of agreements within each partner itself because we offer two services, and each would have different pricing.

So apart from the Lead Parent field there would be another restricted picklist field called Service.

So to correct the initial proposed formula above:
  
IF Parent_Account__c = Partner AND Service__c = Service 1, then (1% * annual_turnover__c + $0.1 * annual_transactions__c)
IF Parent Account = Partner B AND Service__c = Service 2, then (2% * annual_turnover__c + $0.2 * annual_transactions__c)

 

All Answers

Matthew LohMatthew Loh
Forgot to mention, I'll probably be making at least 8 to 10 IF conditions for all our different pricing strategies.

We also have 2 types of agreements within each partner itself because we offer two services, and each would have different pricing.

So apart from the Lead Parent field there would be another restricted picklist field called Service.

So to correct the initial proposed formula above:
  
IF Parent_Account__c = Partner AND Service__c = Service 1, then (1% * annual_turnover__c + $0.1 * annual_transactions__c)
IF Parent Account = Partner B AND Service__c = Service 2, then (2% * annual_turnover__c + $0.2 * annual_transactions__c)

 
This was selected as the best answer
Manohar kumarManohar kumar

Hi Matthew,

I am not sure but some like this will work for your requirement.

 

​IF( Parent_Account__c == 'Partner A' , IF(Service__c == 'Service 1' ,0.01 * annual_turnover__c + $0.1 * annual_transactions__c,  
            IF(Service__c == 'Service 2', 0.02 * annual_turnover__c + $0.2 * annual_transactions__c, ' ') ), 
     IF(Parent_Account__c == 'Partner b', DO SAME , DO SAME) )
 

please let know if this hepls.  

Thanks,

Manohar 

 
Manohar kumarManohar kumar

you can put every Parent_Account__c in else part if the condition.

It will be a bit hectic to write that many conditions, but some thing like that should work.

Thanks,

Manohar
Matthew LohMatthew Loh

Hi @Manohar

Thanks for your input. I'm slightly confused by the first part of the command you wrote:

Shouldn't it be

​IF Parent_Account__C = 'Partner A' AND IF Service__C = 'Service A', 0.01 * annual_turnover__c + $0.1 * annual_transactions__c,  
Then repeated IF functions below that?

I'm a little confused, sorry!

One of the suggestions here https://success.salesforce.com/answers?id=90630000000gyFmAAI was to try something like  
IF(Product.name = "X",A*B,
IF(Product.name ="Y",A*B*C,
IF(Product.name= "Z",A*B*C*D,
A
)))

Where The last "A" represents what your default value will be if Product name doesn't equal either X,Y or Z..

Do you think this would be more suitable?
Manohar kumarManohar kumar

Hi Metthew, both are same i just checked Service__c in the true part ( if(), true, false ). You can go with your approach it looks more clean.

In your method you are putting Service__c in AND and i was doing in true part of the condition. 
Matthew LohMatthew Loh
Hi @Manohar

I just tried putting this in 
IF(ISPICKVAL(Parent_Account__c, "Partner A"),Annual_transactions__c*0.005,
A
)


 and it gives me the error "Field Parent_Account__c may not be used in this type of formula" 

Any ideas what I'm doing wrong? 

The Parent_Account__c is a restricted picklist, whereas annual_transactions__c is a currency field

 
Manohar kumarManohar kumar

Hi Matthew,

Cannot see anything right now, if formula return type is currency then A will not work. Have to put any digit.

Cannot see anything wrong with Parent_Account__c.

Thanks,

Manohar
Matthew LohMatthew Loh
Hi @Manohar

I kept getting errors if I used the actual field itself, so I set up a separate custom field as a formula field itself returning currency.

I initially entered 
  
IF(ISPICKVAL(Parent_Account__c, "Partner A"),
    ROUND(Annual_transactions__c*0.005, 0), 0)
and there were no syntax errors. I got the example of using ROUND from the Salesforce formula guide for ISPICKVAL. 

But when I tried to add the AND conditions and multiple expressions to calculate, it completely broke down :( 
IF(AND(ISPICKVAL(Parent_Account__c, "Partner A"), ISPICKVAL(Service__c, "Service 1")
    ROUND( Annual_card_turnover__c  * 0.005 + Annual_transactions__c  * 0.1, 0, 0))

I keep getting Error:Syntax Error

I feel like I am on the right track but I cannot figure this out.