ShowAll Questionssorted byDate Posted
Chris 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 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)

Shalom 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 McKaughan
Thanks Shalom. I tried that, but got the same result. Any other ideas? Thanks!
Shalom 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 McKaughan
Nailed it! Thanks so much Shalom!