Hi Amy

If you are asking whether you can use Conditional Formatting to achieve as
display of the colours, then the answer is no. CF can only have 4 conditions.

If you are asking whether you can achieve a nested If statement to give a
value of Red, Green, or Amber dependent upon the other conditions, the
answer is yes.

I am lazy, and have used, "g", "r" and "a" rather than the colours, but the
following should help you.
=IF(AB54="r","r",IF(AB4="r",IF(AB54="g","a","r"),I F(AB54="g",IF(AB4="r","a","g"),IF(AB54="a",IF(AB4= "r","r","a")))))

This is at the limit of normal IF statements.

There are other ways to achieve your requirement, using Lookup tables. post
back if you need more information.

Regards

Roger Govier


AmyTaylor wrote:
Can someone help me construct an if(and) function to satisfy this
question?
I have tried but I cant get it to work after the 3rd if.. ! :(
Would appreciate anyones help...

Cell AB4 can contain: "green","amber" or "red"
Cell AB52 can contain: "green", "amber" or "red".

What I would really like is a formula that says:
if AB4 = "green" and AB52 = "green" then display "green"
if ab4 = "green" and ab52 = "amber" then display "amber"
if ab4 = "green" and ab52 = "red" then display "red"
if(ab4 = "amber" and ab52 = "green" then display "green"
if(ab4 = "amber" and ab52 = "amber" then display "amber"
if(ab4 = "amber" and ab52 = "red" then display "red"
if(ab4 = "red" and ab52 = "green" then display "amber"
if (ab4 = "red" and ab52 = "amber" then display "red"
if(ab4 = "red" and ab52 = "red" then display "red").

Is this possible, or would there be too many arguments...?
Thank you
Amy xxx