0

This is what I have:

IF(
    AND(
        IsWon,
        CASE(Type,
            "New Business", 1,
            "Existing Business", 1,
            "Existing Business- Add-on",1 
            ,0
        ) = 1
    ),

    IF(
        AND(
            Opp_ACV__c <= 1700,
            Owner.UserRole.Name = "VP Sales"
        ),
        Opp_ACV__c*0.01,

        IF(
            AND(
                Opp_ACV__c > 1700, 
                Owner.UserRole.Name = "VP Sales"
            ),
            Opp_ACV__c*0.02,
    
            IF(
                OR(
                    TEXT(Channel_Partner__c) = "Partner",
                    AND(
                        Opp_ACV__c <= 1700, 
                        Owner.UserRole.Name = "VP Sales"
                    )
                ),
                (Opp_ACV__c*0.40)*0.01,
    
                IF(
                    OR(
                        TEXT(Channel_Partner__c) = "Partner",
                        AND(
                            Opp_ACV__c > 1700, 
                            Owner.UserRole.Name = "VP Sales"
                        )
                    ),
                    (Opp_ACV__c*0.40)*0.02,
    
                    IF(
                        AND(
                            Opp_ACV__c <= 1000, 
                            Owner.UserRole.Name = "Sales Director"
                        ),
                        Opp_ACV__c*0.03,
    
                        IF(
                            AND(
                                Opp_ACV__c > 1000, 
                                Owner.UserRole.Name = "Sales Director"
                            ),
                            Opp_ACV__c*0.04,
    
                            IF(
                                OR(
                                    TEXT(Channel_Partner__c) = "Partner",
                                    AND(
                                        Opp_ACV__c <= 1000,
                                        Owner.UserRole.Name = "Sales Director"
                                    )
                                ),
                                (Opp_ACV__c*0.40)*0.03,
    
                                IF(
                                    OR(
                                        TEXT(Channel_Partner__c) = "Partner",
                                        AND(
                                            Opp_ACV__c > 1000,
                                            Owner.UserRole.Name = "Sales Director"
                                        )
                                    ),
                                    (Opp_ACV__c*0.40)*0.04),
    
                                    0
                                )
                            )
                        )
                    )
                )
            )
        )
    )
),

IF(
    ISPICKVAL(Type,"Renewal"), 
    Renewal_Increase__c* 0.05,
    0
),

0
)
2
  • doesn't the error usually point towards a column, row ? (line) something to pinpoint exactly where the "extra char" is located?
    – glls
    May 11, 2021 at 14:51
  • Being rigorous with indentation often helps with spotting issues like these (especially when you have such a large formula). I've taken the liberty of doing that for you.
    – Derek F
    May 11, 2021 at 15:00

1 Answer 1

2

It's often easier if you format your formula with structural indentation. Here's where I got to when I spotted the extra paren:

IF(
    AND(IsWon, CASE(Type,"New Business",1,"Existing Business",1, "Existing Business- Add-on",1,0)=1),
    IF(
        AND(Opp_ACV__c <=1700, Owner.UserRole.Name="VP Sales"),
        Opp_ACV__c*0.01,
        IF(
            AND(Opp_ACV__c >1700, Owner.UserRole.Name="VP Sales"),
            Opp_ACV__c*0.02,
            IF(
                OR(TEXT(Channel_Partner__c)="Partner",AND(Opp_ACV__c <=1700, Owner.UserRole.Name="VP Sales")),
                (Opp_ACV__c*0.40)*0.01,
                IF(
                    OR(TEXT(Channel_Partner__c)="Partner",AND(Opp_ACV__c >1700, Owner.UserRole.Name="VP Sales")),
                    (Opp_ACV__c*0.40)*0.02,
                    IF(
                        AND(Opp_ACV__c <=1000, Owner.UserRole.Name="Sales Director"),
                        Opp_ACV__c*0.03,
                        IF(
                            AND(Opp_ACV__c >1000, Owner.UserRole.Name="Sales Director"),
                            Opp_ACV__c*0.04,
                            IF(
                                OR(TEXT(Channel_Partner__c)="Partner",AND(Opp_ACV__c <=1000, Owner.UserRole.Name="Sales Director")),
                                (Opp_ACV__c*0.40)*0.03,
                                IF(
                                    OR(TEXT(Channel_Partner__c)="Partner",AND(Opp_ACV__c >1000, Owner.UserRole.Name="Sales Director")),
                                    (Opp_ACV__c*0.40)*0.04), /* <- that one! */
                                    0))))))))),

IF(ISPICKVAL(Type,"Renewal"), Renewal_Increase__c* 0.05,0),
0)

Right above the 0))))))))) is an extra parenthesis that closes an IF() early. However, it appears to me there's a second extra parenthesis and an extra zero at the end; removing them yields

IF(
    AND(IsWon, CASE(Type,"New Business",1,"Existing Business",1, "Existing Business- Add-on",1,0)=1),
    IF(
        AND(Opp_ACV__c <=1700, Owner.UserRole.Name="VP Sales"),
        Opp_ACV__c*0.01,
        IF(
            AND(Opp_ACV__c >1700, Owner.UserRole.Name="VP Sales"),
            Opp_ACV__c*0.02,
            IF(
                OR(TEXT(Channel_Partner__c)="Partner",AND(Opp_ACV__c <=1700, Owner.UserRole.Name="VP Sales")),
                (Opp_ACV__c*0.40)*0.01,
                IF(
                    OR(TEXT(Channel_Partner__c)="Partner",AND(Opp_ACV__c >1700, Owner.UserRole.Name="VP Sales")),
                    (Opp_ACV__c*0.40)*0.02,
                    IF(
                        AND(Opp_ACV__c <=1000, Owner.UserRole.Name="Sales Director"),
                        Opp_ACV__c*0.03,
                        IF(
                            AND(Opp_ACV__c >1000, Owner.UserRole.Name="Sales Director"),
                            Opp_ACV__c*0.04,
                            IF(
                                OR(TEXT(Channel_Partner__c)="Partner",AND(Opp_ACV__c <=1000, Owner.UserRole.Name="Sales Director")),
                                (Opp_ACV__c*0.40)*0.03,
                                IF(
                                    OR(TEXT(Channel_Partner__c)="Partner",AND(Opp_ACV__c >1000, Owner.UserRole.Name="Sales Director")),
                                    (Opp_ACV__c*0.40)*0.04,
                                    0
                                )
                            )
                        )
                    )
                )
            )
        )
    ),
    IF(
        ISPICKVAL(Type,"Renewal"),
        Renewal_Increase__c* 0.05,
        0
    )
)

which appears to be balanced for parameters and parentheses.

2
  • The syntax is correct, so thank you! However, the math is not working correctly. Depending on the user role (VP or Sales Director) and ACV threshold, they either get the full multiplier OR if the picklist "Channel Partner" = "Partner", they get 40% of that. See sample line: OR(TEXT(Channel_Partner__c)="Partner",AND(Opp_ACV__c >1000, Owner.UserRole.Name="Sales Director")), (Opp_ACV__c*0.40)*0.04, Does it have anything to do with the IFs or ORs? May 12, 2021 at 19:35
  • Sorry, Megan, I don't know that I understand the question; that might merit a separate post with very specific details.
    – David Reed
    May 13, 2021 at 22:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .