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
Tahnee PuckettTahnee 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)
JJE_OLDJJE_OLD
Hi,
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
)
 
Parker EdelmannParker Edelmann
You could try a slight restructuring as follows: 
IF(Product_Category__c = "Equipment",
   IF(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),
    NULL)

Let me know if this helps.

Thanks,
Parker