Complicated IF Formula

Options

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.

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:

Best Answer

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Answer ✓
    Options

    =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"))

Answers

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

    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"))

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

    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.

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Options

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

  • Michael Culley
    Michael Culley ✭✭✭✭✭
    Answer ✓
    Options

    =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"))

  • Simon Bamford
    Simon Bamford ✭✭✭✭
    Options

    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.

  • Simon Bamford
    Simon Bamford ✭✭✭✭
    Options

    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

  • Simon Bamford
    Simon Bamford ✭✭✭✭
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!