if (and) question
 

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


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=471315

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

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


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=471315

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 X


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=471315

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


AmyTaylor wrote:
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 X

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)

"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


--
AmyTaylor
------------------------------------------------------------------------
AmyTaylor's Profile: http://www.excelforum.com/member.php...o&userid=20970
View this thread: http://www.excelforum.com/showthread...hreadid=471315