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
Chris McKaughanChris McKaughan 

Help with nested Case formula

Hello,

I'm trying to write a formula to detremine a price by using 3 criteria -
1. Size (5 different sizes)
2. Tier (2 Pricing Tiers)
3. Days (Number of days - 2-6)

I'm getting the error "Incorrect number of parameters for function 'CASE()'. Expected 4, received 3"

Here's what I've got so far:

CASE(Size_Code__c,
"S",
CASE(Tier__c, 4,
CASE(
Days__c, 2, 985.6,
Days__c, 3, 1,285.16,
Days__c, 4, 1,539.68,
Days__c, 5, 1,779.28,
Days__c, 6, 2,004.00,
Days__c, 7, 2,213.72,
0)),
CASE(Tier__c, 5,
CASE(
Days__c, 2, 1,110.40,
Days__c, 3, 1,466.04,
Days__c, 4, 1,768.40,
Days__c, 5, 2,052.92,
Days__c, 6, 2,319.68,
Days__c, 7, 2,568.72,
0)),
"M",
CASE(Tier__c, 4,
CASE(
Days__c, 2, 985.6,
Days__c, 3, 1,285.16,
Days__c, 4, 1,539.68,
Days__c, 5, 1,779.28,
Days__c, 6, 2,004.00,
Days__c, 7, 2,213.72,
0)),
CASE(Tier__c, 5,
CASE(
Days__c, 2, 1,110.40,
Days__c, 3, 1,466.04,
Days__c, 4, 1,768.40,
Days__c, 5, 2,052.92,
Days__c, 6, 2,319.68,
Days__c, 7, 2,568.72,
0))
)

Any help would be appreciated
Best Answer chosen by Chris McKaughan
Shalom RubdiShalom Rubdi
Hey Chris - took a second look at this & couple of comments:
  • Dont put commas in your numerical values - Salesforce is interpreting those as separate parameters in the formula
  • You only have to specify the expression once; e.g. CASE(Days__c, 2, 1110.40, 3, 1466.04,...,0)
Try the following & please mark as best answer so your question can be flagged as solved:

CASE(Size_Code__c,
  "S", 
     CASE(Tier__c, 
      4, 
 
      CASE(Days__c, 2, 985.6,
       3, 1285.16,
       4, 1539.68,
       5, 1779.28,
       6, 2004.00,
       7, 2213.72,
       0), 
     
      5, 
 
      CASE(Days__c, 2, 1110.40,
       3, 1466.04,
       4, 1768.40,
       5, 2052.92,
       6, 2319.68,
       7, 2568.72,
       0),
     
     0), 
 
  "M", 
     CASE(Tier__c, 
      4, 
 
      CASE(Days__c, 2, 985.6,
       3, 1285.16,
       4, 1539.68,
       5, 1779.28,
       6, 2004.00,
       7, 2213.72,
       0), 
     
      5, 
 
      CASE(Days__c, 2, 1110.40,
       3, 1466.04,
       4, 1768.40,
       5, 2052.92,
       6, 2319.68,
       7, 2568.72,
       0),
     
     0),

   0)

All Answers

Shalom RubdiShalom Rubdi
Chris, try adding a '0' after your last size 'clause' - the formula is looking for something to return if the size is not any of the specified values.

Reference: https://help.salesforce.com/apex/HTViewHelpDoc?id=customize_functions_a_h.htm&language=en_US#CASE

CASE(Size_Code__c,
"S",
CASE(Tier__c, 4,
CASE(
Days__c, 2, 985.6,
Days__c, 3, 1,285.16,
Days__c, 4, 1,539.68,
Days__c, 5, 1,779.28,
Days__c, 6, 2,004.00,
Days__c, 7, 2,213.72,
0)),
CASE(Tier__c, 5,
CASE(
Days__c, 2, 1,110.40,
Days__c, 3, 1,466.04,
Days__c, 4, 1,768.40,
Days__c, 5, 2,052.92,
Days__c, 6, 2,319.68,
Days__c, 7, 2,568.72,
0)),
"M",
CASE(Tier__c, 4,
CASE(
Days__c, 2, 985.6,
Days__c, 3, 1,285.16,
Days__c, 4, 1,539.68,
Days__c, 5, 1,779.28,
Days__c, 6, 2,004.00,
Days__c, 7, 2,213.72,
0)),
CASE(Tier__c, 5,
CASE(
Days__c, 2, 1,110.40,
Days__c, 3, 1,466.04,
Days__c, 4, 1,768.40,
Days__c, 5, 2,052.92,
Days__c, 6, 2,319.68,
Days__c, 7, 2,568.72,
0)),
0
)
Chris McKaughanChris McKaughan
Thanks Shalom. I tried that, but got the same result. Any other ideas? Thanks!
Shalom RubdiShalom Rubdi
Hey Chris - took a second look at this & couple of comments:
  • Dont put commas in your numerical values - Salesforce is interpreting those as separate parameters in the formula
  • You only have to specify the expression once; e.g. CASE(Days__c, 2, 1110.40, 3, 1466.04,...,0)
Try the following & please mark as best answer so your question can be flagged as solved:

CASE(Size_Code__c,
  "S", 
     CASE(Tier__c, 
      4, 
 
      CASE(Days__c, 2, 985.6,
       3, 1285.16,
       4, 1539.68,
       5, 1779.28,
       6, 2004.00,
       7, 2213.72,
       0), 
     
      5, 
 
      CASE(Days__c, 2, 1110.40,
       3, 1466.04,
       4, 1768.40,
       5, 2052.92,
       6, 2319.68,
       7, 2568.72,
       0),
     
     0), 
 
  "M", 
     CASE(Tier__c, 
      4, 
 
      CASE(Days__c, 2, 985.6,
       3, 1285.16,
       4, 1539.68,
       5, 1779.28,
       6, 2004.00,
       7, 2213.72,
       0), 
     
      5, 
 
      CASE(Days__c, 2, 1110.40,
       3, 1466.04,
       4, 1768.40,
       5, 2052.92,
       6, 2319.68,
       7, 2568.72,
       0),
     
     0),

   0)
This was selected as the best answer
Chris McKaughanChris McKaughan
Nailed it! Thanks so much Shalom!