0

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!

2
  • In general, if you're getting an error (and you should be getting an error here), you should include the entirety of the error message. Trying to paraphrase or otherwise shorten the error usually means that you just cut out important information.
    – Derek F
    Commented Aug 10, 2023 at 21:17
  • Thanks for the feedback Derek - updated!
    – sarah
    Commented Aug 10, 2023 at 21:26

1 Answer 1

0

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"

1
  • Thank you so much for your help!!!
    – sarah
    Commented Aug 11, 2023 at 0:17

You must log in to answer this question.

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