Complicated IF Formula

Simon Bamford

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.

Michael Culley

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

Michael Culley

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

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

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

Michael Culley

=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

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

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

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.