Calculated Company Property Field for Dropdown output

[ANadler3]

Hi, 

I am trying to create a calculated field for Health Score Trend and while all of these lines work individually, when I put them together like this I get the message "1. There is a problem with your formula". The issue warning gives me no further information.

Note, The Output type is "String" and the fields I am referencing (Health Score and Previous Health Score) all have these as available options in their field drop downs (Good, Moderate, Poor).

if(([properties.health_score] = [properties.previous_health_score]), "No change")
if(([properties.health_score] = "Good" and [properties.previous_health_score]= "Moderate"), "Up")
if(([properties.health_score] = "Good" and [properties.previous_health_score]= "Poor"), "Up")
if(([properties.health_score] = "Moderate" and [properties.previous_health_score]= "Good"), "Down")
if(([properties.health_score] = "Moderate" and [properties.previous_health_score]= "Down"), "Up")
if(([properties.health_score] = "Poor" and [properties.previous_health_score]= "Good"), "Down")
if(([properties.health_score] = "Poor" and [properties.previous_health_score]= "Moderate"), "Down")

[RSchweighart · Accepted solution]

Hi @ANadler3,

It looks like you'll need to open the if statements if the criteria is not met, add more commas, and closed parenthesis. I haven't fully tested this by mimicking your properties, but try this to see if it works as intended.

IF(
    [properties.health_score] = [properties.previous_health_score], 
    "No change",
    IF(
        [properties.health_score] = "Good" AND [properties.previous_health_score] = "Moderate", 
        "Up", 
        IF(
            [properties.health_score] = "Good" AND [properties.previous_health_score] = "Poor", 
            "Up", 
            IF(
                [properties.health_score] = "Moderate" AND [properties.previous_health_score] = "Good", 
                "Down", 
                IF(
                    [properties.health_score] = "Moderate" AND [properties.previous_health_score] = "Poor", 
                    "Up", 
                    IF(
                        [properties.health_score] = "Poor" AND [properties.previous_health_score] = "Good", 
                        "Down", 
                        IF(
                            [properties.health_score] = "Poor" AND [properties.previous_health_score] = "Moderate", 
                            "Down", 
                            "No change" 
                        )
                    )
                )
            )
        )
    )
)

I look forward to your response!

All the Best,

Ryan Schweighart

Whole Hart Impact, LLC

whimpact.co

I help businesses with HubSpot and Zapier. 

[BérangèreL]

Hi @ANadler3 and welcome to the Community!
We are delighted to have you here!

Thanks for asking the Community!

Just for information, here are some examples of custom equation syntax.

I'd love to put you in touch with some of our Top Experts and some of our Community Members who also use calculation properties: Hi @mattjohnson068, @LouiseHare, @Snapdragon23, @RSchweighart, @SBencheikh, and @Jnix284 do you have tips to share with @ANadler3, please?

Thanks so much and have a great day!

Best,
Bérangère