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
Denise MeinershagenDenise Meinershagen 

Need Help With Formula Field

I'm trying to calculate the current FY potential revenue by looking at the Product Close Date (date field) and the the Product Probablity % (text field). Then I have a formula that does the math. I have the formula that does the math working as needed. So what I need help with is the AND/IF statement (if that is even correct to use that statement). 

Here is the error message I receive:
 Error: Incorrect number of parameters for function 'IF()'. Expected 3, received 4

Here is the code:
IF(YEAR(TODAY()) = YEAR(Product_Close_Date__c),
AND(IF(    Product_Probability__c, "10%",
        (TotalPrice / 12) * 0.10 * (13 - (MONTH(Product_Close_Date__c))),
AND(IF(    Product_Probability__c, "15%",
        (TotalPrice / 12) * 0.15 * (13 - (MONTH(Product_Close_Date__c))),
AND(IF(    Product_Probability__c, "20%",
        (TotalPrice / 12) * 0.20 * (13 - (MONTH(Product_Close_Date__c))),
AND(IF(    Product_Probability__c, "40%",
        (TotalPrice / 12) * 0.40 * (13 - (MONTH(Product_Close_Date__c))),
AND(IF(    Product_Probability__c, "75%",
        (TotalPrice / 12) * 0.75 * (13 - (MONTH(Product_Close_Date__c))),
AND(IF(    Product_Probability__c, "100%",
        (TotalPrice / 12) * 0.100 * (13 - (MONTH(Product_Close_Date__c))),
0)))))))))))))
Best Answer chosen by Denise Meinershagen
CloudGeekCloudGeek
Hello Denise,

Try replacing all those AND/IFs with CASE like below :
  
CASE(Product_Probability__c,
10%, (TotalPrice / 12) * 0.10 * (13 - (MONTH(Product_Close_Date__c))),
15%, (TotalPrice / 12) * 0.15 * (13 - (MONTH(Product_Close_Date__c))),
20%, (TotalPrice / 12) * 0.20 * (13 - (MONTH(Product_Close_Date__c))),
40%, (TotalPrice / 12) * 0.40 * (13 - (MONTH(Product_Close_Date__c))),
75%, (TotalPrice / 12) * 0.75 * (13 - (MONTH(Product_Close_Date__c))),
100%,(TotalPrice / 12) * 0.100 * (13 - (MONTH(Product_Close_Date__c))),
"0")

And see if that works.


Note: Mark this as solution if it helps!

Cheers,
KVin

All Answers

CloudGeekCloudGeek
Hello Denise,

Try replacing all those AND/IFs with CASE like below :
  
CASE(Product_Probability__c,
10%, (TotalPrice / 12) * 0.10 * (13 - (MONTH(Product_Close_Date__c))),
15%, (TotalPrice / 12) * 0.15 * (13 - (MONTH(Product_Close_Date__c))),
20%, (TotalPrice / 12) * 0.20 * (13 - (MONTH(Product_Close_Date__c))),
40%, (TotalPrice / 12) * 0.40 * (13 - (MONTH(Product_Close_Date__c))),
75%, (TotalPrice / 12) * 0.75 * (13 - (MONTH(Product_Close_Date__c))),
100%,(TotalPrice / 12) * 0.100 * (13 - (MONTH(Product_Close_Date__c))),
"0")

And see if that works.


Note: Mark this as solution if it helps!

Cheers,
KVin
This was selected as the best answer
CloudGeekCloudGeek
Well, there could be syntax error in the suedo code I posted above.

Hope that helps you figure out what you looking for .

Good luck!​
Denise MeinershagenDenise Meinershagen
Yes, thank you for the quick response. You are correct - there is a syntax error in the suedo code. Still trying to get that worked out.