Salesforce Formula Returning Incorrect Number

Question

I am working through a formula use case where I am trying to produce a number based on multiple IF statements that add numeric values depending on whether they are True or False. The first three IF statements are all working as expected, but the last two using DSO_eMASS_System__r.System_Type__c don't seem to impact the number. Here is my formula so far:

IF(
    AND(
        DSO_eMASS_System__r.Non_Compliant_Controls__c <= 0, 
        DSO_eMASS_System__r.Compliant_Controls__c  <= 0
    ), 
    +2,
    IF(
        AND(
            DSO_eMASS_System__r.Non_Compliant_Controls__c >= 1, 
            DSO_eMASS_System__r.Non_Compliant_Controls__c <= 25
        ), 
        +1,
        IF(
            AND(
                DSO_eMASS_System__r.Non_Compliant_Controls__c >= 26, 
                DSO_eMASS_System__r.Non_Compliant_Controls__c <= 125
            ), 
            +2,
            IF(
                DSO_eMASS_System__r.Non_Compliant_Controls__c >= 126,
                +3,
                IF(
                    ISPICKVAL(DSO_eMASS_System__r.System_Type__c, "IS_Enclave"),
                    +2,
                    IF(
                        ISPICKVAL(DSO_eMASS_System__r.System_Type__c, "Platform_IT_System"),
                        +2,
                        0
                    )
                )
            )
        )
    )
)

Answer 1

You used an if-else construct; this means if any earlier branch matches the specified value, the evaluation stops at that point. To make the last two work, you need to use math.

You can also eliminate the redundant AND statements. If you write:

IF(X < 0, 1, IF(X >= 0 && X <= 25, 2, 3))

The X >= 0 && is redundant, because you already checked that X < 0; if X is less than 0, you wouldn't reach the second IF statement (it's part of the ELSE branch), so X must be at least 0 if you reach that statement, so you can optimize it out:

IF(X < 0, 1, IF(X <= 25, 2, 3))

So, your final revised formula should be:

IF(
    AND(
        DSO_eMASS_System__r.Non_Compliant_Controls__c <= 0, 
        DSO_eMASS_System__r.Compliant_Controls__c  <= 0
    ), 
    +2,
    IF(
        DSO_eMASS_System__r.Non_Compliant_Controls__c <= 25
        +1,
        IF(
            DSO_eMASS_System__r.Non_Compliant_Controls__c <= 125
            +2,
            IF(
                DSO_eMASS_System__r.Non_Compliant_Controls__c >= 126,
                +3,
                0
            )
        )
    )
)+
IF(
    ISPICKVAL(DSO_eMASS_System__r.System_Type__c, "IS_Enclave"),
    +2,
    IF(
        ISPICKVAL(DSO_eMASS_System__r.System_Type__c, "Platform_IT_System"),
        +2,
        0
    )
)

Further optimizations are possible, such as using CASE:

)+
CASE(DSO_eMASS_System__r.System_Type__c, "IS_Enclave", +2, "Platform_IT_System", +2, 0)