Combining 2 Formulas

chadedwardconway
chadedwardconway ✭✭✭
edited 12/09/19 in Formulas and Functions

Hi There! I have 2 formulas that I would like to combine together. They both work independently, but i can't seem to figure out how to combine them.

Formula 1:

=IF(OR([Phase Health]3 = "Red", [Phase Health]3 = "Yellow"), "Yellow")

Formula 2:

=IF((((COUNTIFS(CHILDREN(Status@row), OR(@cell = "Not Started", @cell = "In Progress"), CHILDREN([Key Milestones]@row), 1, CHILDREN([End Date]@row), TODAY() - @cell > 6))) > 0), "Red", IF((((COUNTIFS(CHILDREN(Status@row), OR(@cell = "Not Started", @cell = "In Progress"), CHILDREN([Key Milestones]@row), 1, CHILDREN([End Date]@row), TODAY() < @cell))) > 0), "Green", IF((((COUNTIFS(CHILDREN(Status@row), OR(@cell = "Not Started", @cell = "In Progress"), CHILDREN([Key Milestones]@row), 1, CHILDREN([End Date]@row), TODAY() - @cell <> 6))) > 0), "Yellow")))

 

I've tried a few iterations and it always results in the "#UNPARSEABLE" error message. 

 

Would someone be able to help guide me on what I need to do to combine these?

 

Thank you!

Tags:

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It is going to depend on where exactly you are trying to incorporate the first into the second.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Hi Paul--I'm trying to incorporate the first formula in the beginning of the 2nd.

     

    Thanks!

  • Lee Joramo
    Lee Joramo ✭✭✭✭✭✭

    As Paul noted it is almost impossible for us to know what you are trying to do here without more context.

    After reformatting your formulas for readability , I think that  Formula 2 is in cell [Phase Health]3. If this is the case, then a possible solution is to simply change Formula 2 so that "Red" is now "Yellow".

    Here are the formatted formulas, with the change made to Formula 2. Of course, you will need to remove the comment, and the formatting to use the new formula 2



    Formula 1:

    =IF(

        OR(

            [Phase Health]3 = "Red",

            [Phase Health]3 = "Yellow"

        ),

        "Yellow"

    )

    Formula 2:

    =IF(

        (

            (

                (COUNTIFS(

                    CHILDREN(Status@row),

                        OR(@cell = "Not Started", @cell = "In Progress"),

                    CHILDREN([Key Milestones]@row),

                        1,

                    CHILDREN([End Date]@row),

                        TODAY() - @cell > 6

                    )

                )

            ) > 0

        ),

        "Yellow",           
    //comment: changed "Red" to "Yellow"

        IF(

            (

                (

                    (COUNTIFS(

                        CHILDREN(Status@row),

                            OR(@cell = "Not Started", @cell = "In Progress"),

                        CHILDREN([Key Milestones]@row),

                            1,

                        CHILDREN([End Date]@row),

                            TODAY() < @cell

                        )

                    )

                ) > 0

            ),

            "Green",

            IF(

                (

                    (

                        (COUNTIFS(

                            CHILDREN(Status@row),

                                OR(@cell = "Not Started", @cell = "In Progress"),

                            CHILDREN([Key Milestones]@row),

                                1,

                            CHILDREN([End Date]@row),

                                TODAY() - @cell <> 6

                            )

                        )

                    ) > 0

                ),

                "Yellow"

            )

        )

    )

     

    Hope this helps

    --

    Lee Joramo

    Data Integrations Developer - Mesa County Valley School Dist. 51

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Easy enough. Let's first take a look at the syntax for an IF statement...

     

    =IF(logical statement, value if true, value if false)

    .

    You already have your first logical statement with the OR function and the value if true with "Yellow".

     

    =IF(OR(......., .......), "Yellow")

    .

    All we need to do is take your second formula and drop it into the value if false portion of the first formula.

     

    =IF(OR(......., .......), "Yellow", value if false)

     

    becomes

     

    =IF(OR(......., .......), "Yellow", second formula without the = at the beginning of it)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lee Joramo
    Lee Joramo ✭✭✭✭✭✭

    Really wish that Smartsheet provided better forum formatting tools. I have submitted a request.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    What kind of formatting tools did you have in mind?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Lee Joramo
    Lee Joramo ✭✭✭✭✭✭

    The big one would be to format text as "Code" like on Stack Overflow and many other forums, displayed as a monospace font, without putting additional space between lines.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!