Field formula for customer health score

Question

I'm wondering why this formula isn't working and was hoping for some help, as I think I'm missing something small. I'm trying to translate a numeric health score into colors based on ranges in a formula field.

IF(
    RecordType.Name <> "Customer", 
    "",
    IF(
        ISNULL (Product_Usage_Health_Score__c ),
        IF(
            Product_Usage_Health_Score__c <= 0.5, 
            "Red", 
            "ND"
        ),
        IF(
            Product_Usage_Health_Score__c  > 0.5  || Product_Usage_Health_Score__c  < 0.7, 
            "Yellow", 
            "ND"
        ),
        IF(
            Product_Usage_Health_Score__c  >= 0.7, 
            "Green", 
            "ND"
        )
    )
)

Error: "Incorrect number of parameters for function 'IF()'; Expected 3, received 4

Thank you for the help!

Answer 1

The issues I see here are:

• It looks like you forgot to use NOT() with ISNULL (Product_Usage_Health_Score__c ). You only want to try to assign a color if that field is not null
• When you're checking for if a number is in a range, you need to use AND() or &&, not || (because 0.8 would be > 0.5, which would get you into the "Yellow" case if you use ||)
• You haven't composed the IF()s together correctly

The IF() function takes 3 parameters (only 3, and they're all required)
IF(<condition>, <expression if true>, <expression if false>)

The way you've structured the Product_Usage_Health_Score__c part of this formula looks like
IF(<condition>, <expression if true>, <expression if false>, <another expression>)

Instead, you need to continue nesting IF() functions inside either the <expression if true> or <expression if false> parts. Also, if you have an IF() that checks if Product_Usage_Health_Score__c >= 0.7 and that check returns false (getting you into the <expression if false> part), then you don't need to explicitly check for Product_Usage_Health_Score__c < 0.7. It is guaranteed to be < 0.7 by virtue of it not being >= 0.7

So all said and done, the inner part of your formula should look like

IF(
    NOT(ISBLANK(Product_Usage_Health_Score__c)),
    IF(
        Product_Usage_Health_Score__c >= 0.7,
        "Green",
        IF(
            /* If we get here, the score is guaranteed to be < 0.7 */
            /* There is no need to explicitly check for that
            Product_Usage_Health_Score__c >= 0.5,
            "Yellow",
            /* Likewise, if we get here, the score is guaranteed to be < 0.5 */
            "Red"
        )
    ),
    "ND"
)

Assuming that "ND" stands for "No Data"