Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Complicated IF Formula

    ✭✭✭✭
    in Formulas and Functions

    Hi,

    I am looking to do a formula that will let me display the red, yellow, or green symbols depending on the amount of answers are green, yellow, or red.

    image.png

    The picture might let me explain better. The left column is Status and the right column is Constant.

    Only the top 3 Totals will count towards the result.

    Total NYIOP would be Red

    Total PIOP would be Yellow

    Total SI would be Green

    So if they're all majority are Total NYIOP show result as Red, if majority are PIOP then result will be Yellow.

    Now I would like it to be if majority Total SI it would be Green unless there are any Total NYIOP then it would show as Red.

    If that can't be done a simple majority system would be great as well.

    Thank you to anyone who looks at this. I have no idea where to begin apart from =IF.

    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭✭✭
      Answer ✓

      =if(and([constant column]9 > [constant column]8,[constant column]9 >[constant column]7,[constant column]7<1),"Green", if(and([constant column]8>[constant column]9,[constant column]8 >[constant column]7),"Yellow","Red"))

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭

      I would do something like:

      =if(and([constant column]3 > [constant column]2,[constant column]3 >[constant column]1,[constant column]1<1),"Green", if(and([constant column]2>[constant column]3,[constant column]2 >[constant column]1),"Yellow","Red"))

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      Basically it says if Total SI is greater than Total PIOP AND greater than NYIOP AND NYIOP is less than 1, Green.

      If Total PIOP is greater than Total SI AND greater than NYIOP, Yellow.

      Anything else, Red.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭

      Sorry I just noticed that you labeled the rows 7, 8, 9 so change my column numbers accordingly.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭
      Answer ✓

      =if(and([constant column]9 > [constant column]8,[constant column]9 >[constant column]7,[constant column]7<1),"Green", if(and([constant column]8>[constant column]9,[constant column]8 >[constant column]7),"Yellow","Red"))

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Hi Michael,

      Thank you so much for your time. The formula is great and is helping me to understand how to do something like that which is great.

      This works great.

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Hi Michael,

      Sorry, I actually asked my question wrong. It is mostly the same but I'll repost below.

      So if they're all majority are Total NYIOP show result as Red, if majority are PIOP then result will be Yellow.

      Now I would like it to be if majority Total SI it would be Green unless there are any Total NYIOP then it would show as Yellow.

      The yellow is the change from red. If you can help that would be great. Thank you

      · Share on FacebookShare on Twitter
    • ✭✭✭✭

      Had an epiphany after finally asking yourself for the help.

      =IF(AND(Constant9 > Constant8, Constant9 > Constant7, Constant7 < 1), "Green", IF(AND(Constant7 > Constant9, Constant7 > Constant8), "Red", "Yellow"))

      This was my answer and now works how I need it.

      Thank you.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions