if (and) question

[AmyTaylor]

Can someone help me construct an if(and) function to satisfy thi
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 xx

 

[Roger Govier]

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"),IF(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]

Thanks Roger, I dont want conditional formating, it was the if statemen
I was looking for.
When I ran your syntax it came up with false, so it is missing a
argument somewhere. I will have a look at it and post back if I can
work it out.
Thanks again for your help !!
Amy

 

[AmyTaylor]

Roger, ignore last post ! I had stupidly forgotten to change your "g"
"r" and "a" to green, red and amber !!
It works perfectly !!
Thank you Amy

 

[Roger Govier]

You're welcome. Glad you got it to work.
The fact that whenever AB52 is Red, the outcome is Red regardless of cell
AB4 content, allowed the first IF to take out a couple of the others you had
wanted to include.

Regards

Roger Govier

 

[Ron Moore]

Observe that in the first six cases (when AB4 is not "red"), the desired
result always equals AB52. This observation leads to the formula:

=IF(AB4="red",IF(AB52="green","amber","red"),AB52)