You need to sign in to do that
Don't have an account?
Tahnee Puckett
Using If formula with 2 variables and a case statement - not returning correct results
I have a conditional formula with two variables. The first part is checking to see if the Product Category is = 'Equipment", if it is true, it checks to see if the UnitPrice is greater than the ListPrice, if so it does a calcualtion, if ELSE it does a case statement. Before the case statement, I need to check to make sure the Product Category is "Equipment". Can someone please show me how to check for that before th case statement.
It is calculating for all my other commission rates even if it is not Equipment right now.
Below is my syntax, thank you!!!
IF(
((Product_Category__c = "Equipment") && (UnitPrice > ListPrice)) ,
(0.075 * Discounted_Sales_Price__c) * Quantity ,
(
CASE(ROUND(((ListPrice - UnitPrice)/ListPrice),2),
0.00, 0.075,
0.005, 0.07232,
0.01, 0.06964,
0.0150, 0.06696,
0.020, 0.06429,
0.0250, 0.06161,
0.030, 0.05893,
0.0350, 0.05625,
0.040, 0.05357,
0.0450, 0.05089,
0.050, 0.04821,
0.0550, 0.04554,
0.060, 0.04286,
0.0650, 0.04018,
0.070, 0.03750,
0.0750, 0.03482,
0.080, 0.03214,
0.0850, 0.02946,
0.090, 0.02679,
0.0950, 0.02411,
0.10, 0.02143,
0.105, 0.01875,
0.11, 0.01607,
0.115, 0.01339,
0.12, 0.01071,
0.125, 0.00804,
0.13, 0.00536,
0.135, 0.00268,
0.14, 0.0000,
NULL)* Discounted_Sales_Price__c) * Quantity)
It is calculating for all my other commission rates even if it is not Equipment right now.
Below is my syntax, thank you!!!
IF(
((Product_Category__c = "Equipment") && (UnitPrice > ListPrice)) ,
(0.075 * Discounted_Sales_Price__c) * Quantity ,
(
CASE(ROUND(((ListPrice - UnitPrice)/ListPrice),2),
0.00, 0.075,
0.005, 0.07232,
0.01, 0.06964,
0.0150, 0.06696,
0.020, 0.06429,
0.0250, 0.06161,
0.030, 0.05893,
0.0350, 0.05625,
0.040, 0.05357,
0.0450, 0.05089,
0.050, 0.04821,
0.0550, 0.04554,
0.060, 0.04286,
0.0650, 0.04018,
0.070, 0.03750,
0.0750, 0.03482,
0.080, 0.03214,
0.0850, 0.02946,
0.090, 0.02679,
0.0950, 0.02411,
0.10, 0.02143,
0.105, 0.01875,
0.11, 0.01607,
0.115, 0.01339,
0.12, 0.01071,
0.125, 0.00804,
0.13, 0.00536,
0.135, 0.00268,
0.14, 0.0000,
NULL)* Discounted_Sales_Price__c) * Quantity)
I'm not sure about the context, I guess this is a formula field so I would try something like this:
IF(
Product_Category__c = "Equipment",
IF(
UnitPrice > ListPrice,
(0.075 * Discounted_Sales_Price__c) * Quantity,
CASE(your case statements here...)
),
Put here what you want to return if it is not Equipement, maybe 0
)
Let me know if this helps.
Thanks,
Parker