Complicated IF Formula
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.
Best 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"))
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"))

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.

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

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

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.

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

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
Categories
Check out the Formula Handbook template!