Nested If Statement - Not getting a return of "0"

Question

I have the following nested IF Statement:

Data TypeFormula

Decimal Places0

IF(Closed_Won_Opportunity_s__c>4000, 1,

IF(AND(Closed_Won_Opportunity_s__c>=1500,Closed_Won_Opportunity_s__c<3999),2,

IF(Closed_Won_Opportunity_s__c <1499,3,

IF(Closed_Won_Opportunity_s__c<1,0,0))))

Accounts with the (Closed_Won_Opportunity_s__c are coming up as a "3" instead of "0". Any insights?

Deepak K Anand · Accepted Answer

Try this =

IF(
    Closed_Won_Opportunity_s__c < 1,
    0,
    IF(
        Closed_Won_Opportunity_s__c < 1499,
        3,
        IF(
            Closed_Won_Opportunity_s__c < 3999,
            2,
            IF(
                Closed_Won_Opportunity_s__c > 4000,
                1,
                NULL
            )
        )
    )
)

Doug Burkhart · Answer

Lynn, It's because on the third line you are indicating if it's less than 1499 then make it a 3. Technically, 0 is less than 1499 so you need make that line and AND statement like you did on line two and add the criteria for: Closed_Won_Opportunity_s__c > 0 You also don't actually need last line since it's output is 0 it can just be the else criteria. So, your whole formula would look like this: IF(Closed_Won_Opportunity_s__c>4000, 1, IF(AND(Closed_Won_Opportunity_s__c>=1500,Closed_Won_Opportunity_s__c<3999),2, IF(AND(Closed_Won_Opportunity_s__c<1499,Closed_Won_Opportunity_s__c>0),3, 0))) Doug

Nested If Statement - Not getting a return of "0"

Sales And Marketing

Related Learning

More Help