A formula with a lot of conditions..

[missmyady]

Hi, I need a field (DepField) that depends on seven other fields (Let's say the fields are named 1-7). All fields (including the dependent field can be either "Red", "Yellow" or "Green"

There are different conditions for the dependent field (DepField):

If either field: 1, 3 or 4 = "Red", then DepField = "Red"

if two or more of fields 1-7 = "Red", then DepField = "Red"

If either field: 1, 3 or 4 = "Yellow", then DepField = "Yellow"

if two or more of fields 1-7 = "Yellow", then DepField = "Yellow"

Else DepField = "Green"

Thank you for taking your time.

Best Regards missmyady

 

[anglais428]

I have put your range of 'fields 1 to 7' in Range B1:B7, with the below formula in cell B8:
=IF(OR(B1="Red",B3="Red",B4="Red"),"Red",IF(COUNTIF($B$1:$B$7,"Red")>1,"Red",IF(OR(B1="Yellow",B3="Yellow",B4="Yellow"),"Yellow",IF(COUNTIF($B$1:$B$7,"Yellow")>1,"Yellow","Green"))))